Inner join issue

[fife]

Hi.  I have a query which I wrote wrongly and then kenrbnsn came along and fixed it for me.  I works great but I have realized that it needs an inner join put into it.  If the query was written differently I could probably do it but its confusing me on how.  I basically need an inner join on cat to my categorys table as at the moment the 'cat' is just coming up as a number and I want the word.  Can someone please explain if this is possible and how to do it as I've spent about 5 hours going over and over it and its driving me crazy!!!!!!!

 

$Adds =  sprintf("SELECT `name`, `cat`, `area`, `county`, `thumbsup`, `thumbsdown`, `logo`, `cat`, `members` FROM `clubs` 
					WHERE `cat` IN('%s', '%s', '%s', '%s', '%s') 
					AND `area` = '%s' 
					OR `county` = '%s' 
					ORDER BY rand()
					LIMIT 6",
					$User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
					$User['area'],
					$User['county']);
					$adverts = mysql_query($Adds) or die(mysql_error());
while($club_adds = mysql_fetch_assoc($adverts)){
echo {$club_adds['name']}  {$club_adds['cat']}
} ?>

[Psycho]

That's a very simple query - don't let the sprintf() scare you. All it does is populate the values (i.e. the $User variables) into the first parameter (i.e. the query). Just add your JOIN right after the FROM as you normally would - along with the appropriate field name to the SELECT clause.

 

By the way you have `cat` listed twice in your select clause.

 

I guessed on the table/field names.

 

$Adds =  sprintf("SELECT `name`, `cat`, `area`, `county`, `thumbsup`, `thumbsdown`, `logo`, `members`
                         `cat_name`
                  FROM `clubs`
                  JOIN `categories` ON `clubs.cat` = `categories.cat_id`
                  WHERE `cat` IN('%s', '%s', '%s', '%s', '%s') 
                    AND `area` = '%s' 
                     OR `county` = '%s' 
                  ORDER BY rand()
                  LIMIT 6",
                  $User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
                  $User['area'],
                  $User['county']);

[fife]

Ok I tried what was posted but I still recieve an error.  Ill give some more info now.  Here is the error

 

                            "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`club_category` ON `clubs.cat` = `club_category.catID` WHERE `cat` IN('0',' at line 1"

 

here is the query I wrote....

 

<?php $Adds =  sprintf("SELECT `clubID`, `name`, `cat`, `area`, `county`, `thumbsup`, `thumbsdown`, `logo`, `members` FROM `clubs` INNERJOIN `club_category` ON `clubs.cat` = `club_category.catID`
					WHERE `cat` IN('%s', '%s', '%s', '%s', '%s') 
					AND `area` = '%s' 
					OR `county` = '%s' 
					ORDER BY rand()
					LIMIT 6",
					$User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
					$User['area'],
					$User['county']);
					$adverts = mysql_query($Adds) or die(mysql_error());
		while($club_adds = mysql_fetch_assoc($adverts)){ 

echo {$club_adds['name']}{$club_adds['category']}
}
?>

 

the table structures

 

 

 

CLUBS                                                        CLUB_CATEGORY

 

name,                                                          catID

cat,                                                              categorys

 

 

 

in the clubs table the 'cat' is = to catID in the category table.  I don't understand what the error saying but i think it is because I'm not saying the I need to select the two fields from the club_category table..... ie

 

 

$Adds =  sprintf("SELECT `clubID`, `name`, `cat`, `area`, `county`, `thumbsup`, `thumbsdown`, `logo`, `members`, `club_category.catID`, `club_category.categorys` FROM `clubs` INNERJOIN `club_category` ON `clubs.cat` = `club_category.catID`
					WHERE `cat` IN('%s', '%s', '%s', '%s', '%s') 
                                                AND `area` = '%s' 
					OR `county` = '%s' 
					ORDER BY rand()
					LIMIT 6",
					$User['fav_1'],$User['fav_2'],$User['fav_3'],$User['fav_4'],$User['fav_5'],
					$User['area'],
					$User['county']);
					$adverts = mysql_query($Adds) or die(mysql_error());
		while($club_adds = mysql_fetch_assoc($adverts)){ 

echo {$club_adds['name']}{$club_adds['category']}
}
?>

 

Obviously I cant put these two fields in this bit because its an sprintf statement and I'm not dynamically creating these two fields I'm just pulling them from the database. Am I right or completely way of the mark?  If I am is there a way around this?

[Psycho]

I really have no idea what you are talking about with regard to the two fields. When you have a problem with a query the FIRST thing you should do is look at the entire query. So, just echo $Adds to the page to see what you have. Also, when you are needing to create a query - especially a complex one- don't do it by creating/executing it in a PHP page. Instead start by running the query in the database. In the case of MySQL this is usually done through phpmyadmin (yes, I know that is a PHP page, but not the same as building your own). Once you have validated that the query is generating the results you expect - THEN you move on to creating the query in PHP and making it dynamic.

 

Having said all that, the error was on my part! In the join statement where you specify the "table.field" values, each should be enclosed in backticks not the entire value.

 

WRONG

JOIN `categories` ON `clubs.cat` = `categories.cat_id`

 

RIGHT

JOIN `categories` ON `clubs`.`cat` = `categories`.`cat_id`