Completing a Join Statement

[datoshway]

I need help!  Banging my head against the wall on this all day.  I really need some assistance on finishing this statement heres the summery.  I'm using mysql version 5.2.6

 

I have a table set up with 3 fields in it

 

intID

intItemID

intIconID

 

This database is correlating to the intID of another database. So my statement needs to detect what Id we are on and show only those rows with that unique ID.  Here is what I have so far.

queryGetIcons	= db_query($strQuery);

		if (db_num_rows($queryGetIcons) > 0) {
			$objRow			= db_fetch_object($queryGetIcons);
			$strHeadline	= stripslashes($objRow->strIconName);
			db_free_result($queryGetIcons);

			$strPostURL		= "Icon/" . $intIconID;

			$strQuery		= sprintf(	"SELECT i.intID, i.strName FROM tblItems i LEFT JOIN tblItemToIcon c ON c.intItemID = i.intID WHERE c.intIconID = %d ORDER BY i.strName ASC", 
										intval($intIconID));
		} else {
			header("Location: " . STR_RELATIVE_PATH);
		}

 

[datoshway]

Sorry I had pasted in a older version of my code.  This is the most recent that I can't seem to get working. 

 

$queryGetIcons	= db_query($strQuery);

		if (db_num_rows($queryGetIcons) > 0) {
			$strFilename	= stripslashes($objRow->strFilename);
			$txtIcon	= stripslashes($objRow->txtIcon);

			db_free_result($queryGetIcons);

			$strQuery		= sprintf(	"SELECT i.intID, i.strName FROM tblItems i LEFT JOIN tblItemToIcon c ON c.intItemID = i.intID WHERE c.intIconID = %d ORDER BY i.strName ASC", 
										intval($intIconID));
		} else {
			header("Location: " . STR_RELATIVE_PATH);
		}

[kickstart]

Hi

 

Which table is what?

 

I am unsure what you are trying to retrieve.

 

All the best

 

Keith

[datoshway]

Three tables are at hand here

 

tblIcons

tblItems

tblItemToIcon

 

The tbl Items is the table holding all the product data.  tblIcons is the table holding all the Icon data.  And tblItemToIcon is the table holding the associations to each product.  That table looks like this

 

intID intItemID i intIconID

305  27            4

 

The intIconID is the ID for the Icons in tblIcons, and the intItemID is ID for the product in tblItems.

 

What we need to happen here is when the user is on ItemID it should check those rows to see if the IconID exists for the ItemID

 

 

[kickstart]

Hi

 

I think you code should do what you want it to do. However you need to bring back a field from the tblItemToIcon table. Best to return the intIconID, as if this is NULL then you know that a matching icon record hasn't been found.

 

All the best

 

Keith

[datoshway]

hmm I tried that still not returning results.. I'm lost.  Maybe I did it wrong.  Any chance you can display a example?

[kickstart]

Hi

 

It should be fine.

 

Try the following line in your code

 

$strQuery = sprintf("SELECT i.intID, i.strName, c.intIconID FROM tblItems i LEFT OUTER JOIN tblItemToIcon c ON c.intItemID = i.intID WHERE c.intIconID = %d ORDER BY i.strName ASC",intval($intIconID));

 

It is doing a LEFT OUTER JOIN, so wil bring back the records from the tblItems and if they exist those from tbItemToIcon.

 

If there isn't a matching icon then intIconID will come back as null

 

All the best

 

Keith