Still Trying to Wrap My Head Around Joins

[tonyawelch]

I know if I see enough of these done correctly, I'll finally get it....

 

Anyway, required information:

 

  * your MySQL server version -5.0.75

 

  * the raw MySQL statement in question [in a CODE block, and without any PHP variables]

 

If I did it all the LONG way (as I did before I started trying to learn JOINS), I would need to do these 4 queries:

//Get all ads using a query string that can have up to 3 category variables
"SELECT * FROM ads WHERE maincat_id = $_GET[variable] and subcat1_id=$_GET[variable] AND subcat2_id=$_GET[variable] AND approved='Y' ORDER BY submitted DESC";

//Now, so I can make a legend, (like so: maincategory > subcategory1 > subcategory2 > ad) get the name of each category using $_GET variables
"SELECT maincategoryname FROM maincategorytable WHERE maincategoryID = maincategoryID;
"SELECT sub1categoryname FROM sub1categorytable WHERE sub1categoryID = sub1categoryID;
"SELECT sub2categoryname FROM sub2categorytable WHERE sub2categoryID = sub2categoryID;

 

 

    * any errors that MySQL returns to the client [from mysql_error()]

 

Well, I've tried several JOIN statements, and have either gotten an empty array or an array of category names for each ad.

 

 

 

    * the table structure & column indexes of the relevant tables [via SHOW CREATE TABLE is preferred]

 

CREATE TABLE `ads` (
`ad_id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL,
`maincat_id` int(10) unsigned NOT NULL,
`subcat1_id` int(10) unsigned default NULL,
`subcat2_id` int(10) unsigned default NULL,
`ad_title` varchar(120) NOT NULL,
`ad_text` mediumtext NOT NULL,
`price` varchar(40) default NULL,
`image` varchar(80) default NULL,
`tn_image` varchar(80) default 'noimage.gif',
`submitted` timestamp NOT NULL default CURRENT_TIMESTAMP,
`approved` char(1) NOT NULL default 'Y',
PRIMARY KEY  (`ad_id`)
) ENGINE=MyISAM AUTO_INCREMENT=63 DEFAULT CHARSET=latin1

CREATE TABLE `ad_maincat` (
`maincat_id` int(10) unsigned NOT NULL auto_increment,
`maincat_name` varchar(50) NOT NULL,
`img_option` char(1) NOT NULL,
`price_option` char(1) NOT NULL,
PRIMARY KEY  (`maincat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

CREATE TABLE `ad_subcat1` (
`subcat1_id` int(10) unsigned NOT NULL auto_increment,
`maincat_id` int(10) unsigned NOT NULL,
`subcat1_name` varchar(50) NOT NULL,
PRIMARY KEY  (`subcat1_id`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1

CREATE TABLE `ad_subcat2` (
`subcat2_id` int(10) unsigned NOT NULL auto_increment,
`subcat1_id` int(10) unsigned NOT NULL,
`maincat_id` int(10) unsigned NOT NULL,
`subcat2_name` varchar(50) NOT NULL,
PRIMARY KEY  (`subcat2_id`)
) ENGINE=MyISAM AUTO_INCREMENT=71 DEFAULT CHARSET=latin1

 

 

 

    * a clear and concise description of what you want this statement to achieve

 

I can still do this the long way, but since I am determined to learn how to be more efficient with JOIN statements, I'd like to know how I can get all of the data I need in as few queries as possible

 

 

    * a description of what it's currently doing that's not to your liking

 

Not returning the data I need, or is returning the data I want for all ads rather than just the 3 possible variables passed in the URL.

 

    * a brief listing of the types of things you've attempted so far

 

// TRY 1: Returned an array of category names for each ad, not from the variables in the URL.  I understand why this happened...

"SELECT ads.*, ad_maincat.maincat_name, ad_subcat1.subcat1_name, ad_subcat2.subcat2_name
FROM ads
    LEFT JOIN ad_maincat USING maincat_id
    LEFT JOIN ad_subcat1 USING subcat1_id
    LEFT JOIN ad_subcat2 USING subcat2_id
WHERE maincat_id = $_GET[variable] and subcat1_id=$_GET[variable] AND subcat2_id=$_GET[variable] AND approved='Y' ORDER BY submitted DESC";

//TRY 2:  Used a simple query to get the ads, then this second query using the get variables just for the names.  Tried the less efficient syntax, (thought I might understand it better...)Returned an empty array

"SELECT ad_maincat.maincat_name, ad_subcat1.subcat1_name, ad_subcat2.subcat2_name 
FROM ad_maincat, ad_subcat1, ad_subcat2
WHERE ad_maincat.maincat_id = $_GET[variable] AND ad_subcat1.subcat1_id = $_GET[variable] AND ad_subcat2.subcat2_id = $_GET['variable']"

 

I tried a few other, even less sensible queries thinking I might figure it out by just playing with it.  I always managed to get the syntax correct enough for the query to not throw an error - it just never returned the data I'm looking for.  One even went into an never-ending loop

 

So, questions: 

 

Is it possible to achieve both of these goals in one query only? (as in, get all the ads so I can while/loop them out, and also get the category names based on the GET variables passed in the URL)  If so, can someone help me build it?

 

Is there a more efficient way to do this that is right in front of me, but I'm too blind to see it?

 

 

 

 

Thanks for all of your help!!  So far this forum has been the quickest and most reliable source for support.  You are all a blessing!

 

Tonya

[GeoffOs]

What about

"SELECT 
ads.*, 
ad_maincat.maincat_name, 
ad_subcat1.subcat1_name, 
ad_subcat2.subcat2_name
FROM ads
INNER JOIN ad_maincat ON ads.maincat_id = ad_maincat.maincat_id
INNER JOIN ad_subcat1 ON ads.subcat1_id = ad_subcat1.subcat1_id
INNER JOIN ad_subcat2 ON ads.subcat2_id = ad_subcat2.subcat2_id
WHERE ad_id = $_GET[ad_id]
AND approved='Y' ORDER BY submitted DESC";

 

Though this expects to always have a main_cat, subcat1 and subcat2. If these are not always present then switch the join to be a LEFT OUTER JOIN so that the ads record is always returned irrespective of the existing of if the categories have been assigned.

 

Though you should always check the $_GET variable is what you expect before putting it into a query..

 

Also have you read http://www.phpfreaks.com/tutorial/data-joins-unions

[kickstart]

Hi

 

First thing, not sure on you table designs. Why does ad_subcat2 have maincat_id in it, when that can be derived from ad_subcat1 unless there may not be a record on ad_subcat1. Same applies to maincar_id and subcat1_id on the ads table.Appers to be just duplication.

 

I can't see why this shouldn't work:-

 

"SELECT ads.*, ad_maincat.maincat_name, ad_subcat1.subcat1_name, ad_subcat2.subcat2_name
FROM ads
    LEFT JOIN ad_maincat USING maincat_id
    LEFT JOIN ad_subcat1 USING subcat1_id
    LEFT JOIN ad_subcat2 USING subcat2_id
WHERE maincat_id = $_GET[variable] and subcat1_id=$_GET[variable] AND subcat2_id=$_GET[variable] AND approved='Y' ORDER BY submitted DESC";

 

Personally I would take out the duplicate fields from the table and then try.

 

"SELECT a.*, b.maincat_name, c.subcat1_name, b.subcat2_name
FROM ads a
JOIN ad_subcat2 b ON a.subcat2_id = b.subcat2_id
JOIN ad_subcat1 c ON b.subcat1_id = c.subcat1_id
JOIN ad_maincat d ON c.maincat_id = d.maincat_id
WHERE maincat_id = $_GET[variable] 
AND subcat1_id=$_GET[variable] 
AND subcat2_id=$_GET[variable] 
AND approved='Y' 
ORDER BY submitted DESC";

 

All the best

 

Keith

[tonyawelch]

I set up the tables this way because for a couple of reasons:

 

1.  Every ad has at least a main category.  It may or may not have a subcat1 category.  If it DOES have a subcat1 category, it may or may not have a subcat2 category.  The categories are all nested.  The subcat2 category can only apply if the subcat1 category is set, and the subcat1 category can only apply if the maincat is set.

 

2.  I want the flexibility to easily add new categories on the fly to any category level (maincat, subcat1 or subcat2) via an administrative panel.

 

Maybe I'm not seeing what you mean?

 

Anyway Geoff, I looked at your example and could tell it wouldn't work because at this point I'm not using the ad_id variable yet.  The page in question has an url like this: 

 

http://www.mysite.com/ads.php?maincat=X&subcat1=X&subcat2=X

 

On this page if only a maincat is defined, the script grabs all ads posted under that broad category category.  For example, any ads posted that are regarding stuff for sale can be seen under the URL

 

http://www.mysite.com/ads.php?maincat=forsale_catID 

 

If the user wants to get more specific and get only electronics that are for sale, he/she will do so via the URL

 

http://www.mysite.com/ads.php?maincat=forsale_catID&subcat1=electronics_catID 

 

Then finally, if he/she wants only TVs for sale, that list will be accessed by the

 

http://www.mysite.com/ads.php?maincat=forsale_catID&subcat1_electronicscatID&TVs_catID

 

I hope this all makes sense.  Once directed to whatever level of categories the users wants, he/she will get a list of the ads under that category that are hyperlinked to the ad using the ad_id. 

 

I have all of this working pretty well - just need to pull out the category names to make a legend for navigation.  So for TVs, the navigation would look like this (with links):

 

Ads > For Sale > Electronics > TVs

 

Kickstart, I tried your query and got this error:

 

"Unknown column 'b.maincat_name' in 'field list'"

 

Which makes sense because there is no "maincat_name" in the ad_subcat2 table.

 

Thanks for your patience.  I have indeed read the tutorial on this site for JOINS, etc. - it is the third or fourth that I've read.  For some reason I just can't seem to grasp the logic of these queries.  But I'm trying!

[kickstart]

I set up the tables this way because for a couple of reasons:

 

1.  Every ad has at least a main category.  It may or may not have a subcat1 category.  If it DOES have a subcat1 category, it may or may not have a subcat2 category.  The categories are all nested.  The subcat2 category can only apply if the subcat1 category is set, and the subcat1 category can only apply if the maincat is set.

 

2.  I want the flexibility to easily add new categories on the fly to any category level (maincat, subcat1 or subcat2) via an administrative panel.

 

Fair enough, although then possibly you want to have a table of subcategories, each with a parent id, allowing a virtually unlimited number of levels (or do it properly, google "Nested Set Model" which is far more efficient for retrieving data but a bit harder to follow and less efficient for inserting data)

 

Kickstart, I tried your query and got this error:

 

"Unknown column 'b.maincat_name' in 'field list'"

 

Which makes sense because there is no "maincat_name" in the ad_subcat2 table.

 

Typo. Should be d.maincat_name

 

All the best

 

Keith

[tonyawelch]

OK, we are almost there!  I think...

 

I'm starting to feel like a pain.  Thanks again for the patience.

 

Like my first JOIN query, this query grabs all of the ads, not a single row that gives me just the category names of only those listed in the URL.

 

To better explain...

 

I tested on this URL:  http://www.mysite.com/ads.php?maincat=1

 

This is the broad category of 'For Sale'.  Currently there are 5 test records under this broad category.

 

Under this main category, I have the following 5 posts (DESC order).

 

5.  Xbox that is under 'For Sale/Computer and Game Systems/Game Console Systems

4.  Xbox that is under 'For Sale/Computer and Game Systems/Game Console Systems

3.  Sat-Go that is under 'For Sale/Sporting Equipment/Camping

2.  Stuff that is under 'For Sale/Sporting Equipment/Fitness

1.  Stuff that is under 'For Sale/Pets and Animals/Lost and Found

 

The query returned the category names for the record listed above as #5.

 

So, under the URL http://www.mysite.com/ads.php?maincat=1, the query should do nothing more than this:

 

- Check to see if there is a maincat_id in the URL, and if so, get the name of that category from the ad_maincat table (though, there will ALWAYS be at least a maincat_id)

 

- Check to see if there is a subcat1_id in the URL, and if so, get the name of that category from the ad_subcat1 table.  If there is no subcat1_id in the URL, return a NULL or 0 value.

 

- Check to see if there is a subcat2_id in the URL, and if so, get the name of that category from the ad_subcat2 table.  If there is no subcat2_id in the URL, return a NULL or 0 value.

 

Instead of checking these values against the URL, the query seems to be getting the maincat_id, subcat1_id and subcat2_id from the first record returned in an array of several records.

 

Maybe it's not possible to do this kind of JOIN?

 

BTW, I do restrict the WHERE clause in the query with an if statement, so if the query were working right, it wouldn't even check for subcat1 or subcat2 unless those values were in the URL.  Here's the code:

 

	$string = NULL;
	//Create String for MYSQL query
	$string = "a.maincat_id = $_GET[maincat]";
		if ($_GET[subcat1] !="")
		{
		$string .= "AND a.subcat1_id = $_GET[subcat1]";
			if ($_GET[subcat2] !="")
			{
			$string .= "AND a.subcat2_id = $_GET[subcat2]";
			}
		}

	echo $string;  //prints correctly

	$get_ads_query = 

	"SELECT a.*, d.maincat_name, c.subcat1_name, b.subcat2_name
	FROM ads a
	JOIN ad_subcat2 b ON a.subcat2_id = b.subcat2_id
	JOIN ad_subcat1 c ON b.subcat1_id = c.subcat1_id
	JOIN ad_maincat d ON c.maincat_id = d.maincat_id
	WHERE $string
	AND approved='Y'
	ORDER BY submitted DESC";

	$get_ads = mysql_query($get_ads_query) or die(mysql_error());

 

Thanks so much again!  I'm actually pretty proud that the first JOIN I tried was basically the same as yours (though decidedly less organized) - gives me hope that I'm starting to get this stuff.

 

[kickstart]

Hi

 

A conventional JOIN (INNER JOIN) will only return a row if there is a match on both tables (or in your case all tables).

 

If you want a row returned where there may not be a matching record on some tables (and in which case return nulls for the fields from those tables) then you should use an OUTER JOIN.

 

Something like this:-

 

	"SELECT a.*, d.maincat_name, c.subcat1_name, b.subcat2_name
	FROM ads a
	LEFT OUTER JOIN ad_subcat2 b ON a.subcat2_id = b.subcat2_id
	LEFT OUTER JOIN ad_subcat1 c ON b.subcat1_id = c.subcat1_id
	LEFT OUTER JOIN ad_maincat d ON c.maincat_id = d.maincat_id
	WHERE $string
	AND approved='Y'
	ORDER BY submitted DESC";

 

I cannot see why your query seems to be getting the maincat_id, subcat1_id and subcat2_id from the first record returned in an array of several records.

 

All the best

 

Keith