Joins VS 2 while loops

[V]

I'm having some trouble echoing a join query. I'm querying two tables: categories and subcats. First I used 2 loops to query all the categories and their respective subcategories (subcats). It works fine, look..

 

$sql = "SELECT * FROM categories ORDER BY cat_name"; //categories
$cats_result = $connection->query($sql) or die(mysqli_error($connection)); 

    while ($row = $cats_result->fetch_assoc()) { 
$cat_id = $row['cat_id'];
$cat_name = $row['cat_name'];

	echo "<strong>".$cat_name."</strong><br />"; 

		$sql = "SELECT * FROM subcats WHERE cat_id = '$cat_id'"; //subcategories
		$subcat_result = $connection->query($sql) or die(mysqli_error($connection)); 

			while ($row = $subcat_result->fetch_assoc()) { 
			$subcat_name = $row['subcat_name'];

				echo $subcat_name; 
				echo "<br />";

			}//end subcats while loop

    }//end category while loop

 

The output is..

 

Technology

Computers

Gadgets

Robots

 

Health

Fitness

Diet

 

 

 

Now I'm trying to make everything more compact by joining the two tables in one query.

 

$sql = "SELECT categories.*, subcats.* FROM categories, subcats WHERE categories.cat_id = subcats.cat_id ORDER BY cat_name";
$cats_result = $connection->query($sql) or die(mysqli_error($connection)); 

    while ($row = $cats_result->fetch_assoc()) { 
$cat_id = $row['cat_id'];
$cat_name = $row['cat_name'];
$subcat_name = $row['subcat_name'];

       //should output all categories once and nest their subcategories
echo "<strong>".$cat_name."</strong>"; 
echo "<br />";
echo $subcat_name; 
echo "<br />";

}

 

The output I get is..

 

Technology

Computers

 

Technology

Gadgets

 

Technology

Robots

 

Health

Fitness

 

Health

Diet

 

 

 

Will I always get this with joins or am I doing something terribly wrong?

[Alex]

That's not a JOIN at all..

 

Using a JOIN your query might look something like this:

 

SELECT categories.*, subcats.* FROM categories JOIN subcats on (categories.cat_id = subcats.cat_id) ORDER BY cat_name

[V]

Thanks Alex! I was under teh impression that join means when you query 2 tables at once. I used the query you provided but I get the exact output like my query without join.

[Alex]

The data returned is correct. You just need to loop over it with PHP in the correct manner, but that's more of a question suited for the PHP coding help forum.

[V]

Ok, that makes sense! I'll re-post in the correct section. Thanks!