[PHP & MySQL] Join

Hi, I'm pretty new to the forums, lurked around, but I fell on a problem on my website, using PHP and MySQL, most of the things works, but I don't quite understand the Join method correctly in MySQL.

What I'm trying to do is match an 'id' with its respectful category name.

First I have my 'categories' table that uses an 'id' and a 'name' row.
Then I have my 'movies' table that has number of plays, etc, but the most important, a row called 'category' with the actual category id assigned to it.

To print the category id, I have no problems - $row['category']

But I need to print the category name, and I guess the easiest way is with the Join method.

Anyone can help me out? Since I don't understand it at all.

Thanks a bunch!

http://www.tizag.com/mysqlTutorial/mysqljoins.php
http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

found that to be a decent explanation.

Quote:

---

Originally Posted by bone-you

http://www.tizag.com/mysqlTutorial/mysqljoins.php
http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

found that to be a decent explanation.

---

Yea I've already read that, but imo it's abit unclear, I mea I understand how it works, but my problem is using that example of code for my use.

SELECT table1.*,table2.name FROM table1,table2 WHERE table1.category=table2.id

table1 being your table of movies, table1.category being the id of the category, and table2 being your list of categories. That is assuming all movies have a category.

To also display ones without, you'd do left join

SELECT table1.*,table2.name FROM table1 LEFT JOIN table2 ON table1.category=table2.id

Yea, all the movies have a category ID.

Ill try this out and let you know, thanks alot for your time!

---------- Post added at 09:28 PM ---------- Previous post was at 09:17 PM ----------

Ok I'm getting some issues right now.

To print it, what would I print?

$row['category'] or what?

Quote:

---

Originally Posted by CaneOne

Yea, all the movies have a category ID.

Ill try this out and let you know, thanks alot for your time!

---------- Post added at 09:28 PM ---------- Previous post was at 09:17 PM ----------

Ok I'm getting some issues right now.

To print it, what would I print?

$row['category'] or what?

---

Question is, what are you trying to print, and how are you fetching it?

If you're trying to print the category name, than assuming table1 does not have a field named `name` and table2 has a field named `name` (category name) then you'd just do $row['name'] if you get the result via mysql_fetch_assoc().

Ok here is what I'm using:

Code:

---

<?php
                              $query = mysql_query("SELECT games.*,categories.name FROM games,categories WHERE games.category=categories.id");
                                while($row = mysql_fetch_array($query)) {
   
  echo'<div class="game-top"><h1>' . $row['name'] . ' Games</h1></div>';
 
                                }
                                ?>

---

(I changed movies to games btw)

Ok so when I use this, it simply listed allllllll the different games category, and when I had limit 1, it always displayes the same.

Quote:

---

Originally Posted by CaneOne

Ok here is what I'm using:

Code:

---

<?php
                              $query = mysql_query("SELECT games.*,categories.name FROM games,categories WHERE games.category=categories.id");
                                while($row = mysql_fetch_array($query)) {
   
  echo'<div class="game-top"><h1>' . $row['name'] . ' Games</h1></div>';
 
                                }
                                ?>

---

(I changed movies to games btw)

Ok so when I use this, it simply listed allllllll the different games category, and when I had limit 1, it always displayes the same.

---

psst, fetch_assoc, not array. Array means you have to access it like [0], [1], etc. Assoc = associative which is by name.

mysql_fetch_row fetches numeric array. {ex: $row[0]}
mysql_fetch_assoc fetches associative array. {ex: $row['name']}
mysql_fetch_array fetches both (associative & numeric). {ex: $row['name']=$row[0]}

In that order, none significantly slower then the one before.


As to your query, You don't really need the JOIN statement.. yet.. with this system.

PHP Code:

---

$query = mysql_query('SELECT `categories`.`name` FROM `categories`,`games` WHERE `games`.`category` = `categories`.`id` ') or die(mysql_error()); 


---

For future problems that may come up, never use a select statement inside a loop of another select statement.

Ex:

PHP Code:

---

$select = mysql_query('Select Some Rows..');
while($row = mysql_fetch_assoc($select))
{
    //Never Do this because it will greatly effect negatively on load-time.
    $select2 = mysql_query('Select Some Other Rows..');
    while($row2 = mysql_fetch_assoc($select2))
    { .... }
    //If you ever run into this issue, it's time to use the JOIN statement in your query.
} 


---

So the above code should give you a clue as to when/why to use the Join statement, in popular cases.

Good Luck! ;)

- Spence

Here is one site where you can find a lot of information about php tutorials.