Jump to content

Help with INNER JOIN - setting up like a forum


Anders Moen

Recommended Posts

Hey guys and maybe girls!I'm working on a forum now and then, but I want to know how I can form the categories with the subcategories under...I mean, I managed to make it like this

Category 1 - One subcategoryCategory 2 - One subcategory
but I coudn't make it work with several subcategories under =/How do I do that? The code I used to form it the first way was with this:
<?php// Make a MySQL Connection// Construct our join query$query = "SELECT categories.id, subcategories.id ". "FROM family LEFT JOIN food ".	"ON family.id = food.id"; 	 $result = mysql_query($query) or die(mysql_error());// Print out the contents of each row into a table while($row = mysql_fetch_array($result)){	echo $row['Position']. " - ". $row['Meal'];	echo "<br />";}?>

Actually I don't remember this script how I made it work almost like I wanted it too, but that's the script I worked out. From here for those of you who want to know about some more tutorialsEdit: I wrote INNER JOIN in the topic title, but I meant LEFT JOIN :)

Link to comment
Share on other sites

Hm, okay...I still don't know how to do it.Yeah, I know they don't cause I deleted the script I used before from the computer (don't know why) and then I had to find the code back and then I started to edit it but then I couldn't be bothered to do it when I was in the middle of it...lol

Link to comment
Share on other sites

A left join preserves all rows from the left-hand table, even if there are no matching rows in the right-hand table. An inner join will only return rows that match between tables. If you are left-joining the family table with the food table, then every qualifying row will be retrieved from family, and only those rows that match in the food table will be returned. With an inner join, it would only return rows where there is a match between tables. If there were a row in family that would pass the requirements in the WHERE clause but does not have a joined row, it would be left out.A left join is probably what you want here, because you would want all categories, even if the category had no subcategories. One thing to point out is that you are returning two columns called ID, that will be an error. You really need to be bothered to only post code here that you are using. It doesn't do anyone any good if you copy and paste code that you know has errors in it. If you want me to bother to help you, then you need to do what you need to do to make your post as non-confusing as possible. Posting code that you later say you changed or you are going to change is not the right thing to do, and it wastes people's time trying to help you.If you need further help with this, post the schema for the two tables that you are trying to join and explain how they relate if it's not obvious, and post the code you are running. I'm not interested in seeing code that you are going to change anyway.

Link to comment
Share on other sites

Hm, okay...Table: categories

`id` auto_increment primary key`category` text

Table: subcategories

`id` auto_increment primary key`cat_name` text // this is the category name (maybe id?) it belongs to`sub_cat_name` text // this is the sub category name

This helps you?

Link to comment
Share on other sites

That's a start. I need to know if cat_name in subcategories is an int or varchar/text, it would make sense to be an int but you have it labelled as text. Also post the SQL statement and the rest of the PHP code, your original question was about the SQL statement but you posted a statement that already had problems with it.

Link to comment
Share on other sites

Huh?! Now you confused me ... that "code" I sent was just to simply see how my tables look like...not the actual code :)And it's text by the way, not varchar..I just use text all the time lol

Link to comment
Share on other sites

I wasn't referring to what you posted there, I was referring to your original post. This is the problem when you post things that you couldn't be bothered to finish, we run into confusion about what's right and what's wrong. It ends up wasting time, this is probably a fairly easy problem but you post a question about code and then post code that you know already has problems with it (i.e. the table names are wrong). Please, in the future if you have a question to ask, make sure that the code you are posting is good as far as you know, it will save everyone time trying to guess what is what.Now, about the tables. It's not a good idea to have a foreign key point to anything other then a primary key. You have a field called cat_name that is apparently supposed to relate a subcategory to its parent category by storing the title of the parent category. This field should be an integer and it should point to the primary key of the parent category (the ID), not the name. If you change the name of the parent, or if you have multiple categories or subcategories with the same name, you are going to run into problems. When setting up a foreign key that relates one table to another table, point to a primary key, that's the general rule.So, assuming that the subcategories table has a field called parent_id that points to the id of an entry in the categories table, you will want to use an inner join to relate them. If you use a left join, you are only going to get one row for each category, which is what you were seeing. An inner join will produce all combinations of rows that match the conditions. Keep in mind that if you use this method, you will not be able to retrieve any categories that do not have subcategories, you will need to use a separate query to do that because it will not match the inner join conditions.

SELECT c.id AS par_id, c.category AS par_name, s.id AS sub_id, s.sub_cat_name AS sub_name FROM categories AS c INNER JOIN subcategories AS s ON s.parent_id = c.id ORDER BY par_name, sub_name

Link to comment
Share on other sites

Okay, sorry then...probably tired.But this code will work with how I've set up my forum tables?

SELECT c.id AS par_id, c.category AS par_name, s.id AS sub_id, s.sub_cat_name AS sub_name FROM categories AS c INNER JOIN subcategories AS s ON s.parent_id = c.id ORDER BY par_name, sub_name

Link to comment
Share on other sites

That will work with how I described I thought the tables should be setup, with matching IDs instead of names. If you want to leave your foreign key as a text field, which is going to take longer to run this query because the text field is not indexed, then you will need to change the fields in the "ON" clause to use the names instead of the IDs.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...