Jump to content

Joins VS 2 while loops


V

Recommended Posts

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

 

:birthday:

 

 

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

 

:facepalm:

 

 

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.