Join Issues

[fife]

Ok I have a join issue I can not solve.  First I will give the two table stucture

 

 

 

members                                                      categorys

memberID                                                     

fav_1                                                                            catID

fav_2                                                                            category

fav_3

fav_4

fav_5

 

 

Now when the member select their top five favourites it saves the catID an not the name.  When I come to echo the members favourite obviously it show the number.  I've been trying to write an inner join I believe to make it echo the name instead but I keeps failing.  can somebody please tell me where i have gone wrong in the code? 

 

<?php 
						  
						$query = mysql_query("SELECT club_category.*, members.* FROM club_category INNER JOIN members ON club_category.catID = members.fav_1 WHERE memberID= ".$User['memberID']." ");
$result = mysql_fetch_array($query); 
						  
						  if ($User['fav_1']!="") { if($result['fav_1']) {echo " <li>{$result['categorys']}</li> ";}}
						  		if ($User['fav_2']!="") {if ($User['fav_2']){echo " <li>{$result['categorys']}</li> ";}}

						    ?>

 

It just seems to be showing the result of fav_1 two times on the screen.

[kickstart]

Hi

 

You effectively have duplicate data in different columns. You would be best pulling off the favourites into another table with one row per favourite per person. Would also make it FAR easier should you chose to increase or reduce the number of favourites in the future.

 

However if you must have the columns:-

 

<?php

$query = mysql_query("SELECT members.memberID, club_category1.category AS Cat1, club_category2.category AS Cat2, club_category3.category AS Cat3, club_category4.category AS Cat4, club_category5.category AS Cat5
FROM members
LEFT OUTER JOIN club_category club_category1 ON members.fav_1 = club_category1.catID
LEFT OUTER JOIN club_category club_category2 ON members.fav_2 = club_category2.catID
LEFT OUTER JOIN club_category club_category3 ON members.fav_3 = club_category3.catID
LEFT OUTER JOIN club_category club_category4 ON members.fav_4 = club_category4.catID
LEFT OUTER JOIN club_category club_category5 ON members.fav_5 = club_category5.catID
WHERE memberID= ".$User['memberID']." ");


while($row = mysql_fetch_array($query))
{
if ($row['Cat1']) echo " <li>{$row['Cat1']}</li> ";
if ($row['Cat2']) echo " <li>{$row['Cat2']}</li> ";
if ($row['Cat3']) echo " <li>{$row['Cat3']}</li> ";
if ($row['Cat4']) echo " <li>{$row['Cat4']}</li> ";
if ($row['Cat5']) echo " <li>{$row['Cat5']}</li> ";
}

?>

 

All the best

 

Keith

[fife]

Ok I made the changes to my database as suggested.  It now has favourites table with the memberID, the "favourite" number (which is the same as the catID in the category table) and the order in which they chose that favourite......

 

 

FAVOURITES                                CATEGORY                                                       

FavID,                                            catID

memberID,                                      category

favourite,

order

 

I created a new join but again it does not work

 

$new_fav = mysql_query("SELECT favourites.*, category.* FROM favourites INNER JOIN ON favourites.favourite = category.catID WHERE favourite.memberID = ".$User['memberID']." ");
while($row1 = mysql_fetch_array($new_fav))
{
if ($row1['favourite']) echo " <li>{$row1['favourite']}</li> ";

}

[kickstart]

Hi

 

You haven't specified which table you want to JOIN, just the

favourites

table

 

All the best

 

Keith

[fife]

sorry that was a typo.  It is there it says.....

 

	$new_fav = mysql_query("SELECT favourites.*, category.* FROM favourites INNER JOIN category ON favourites.favourite = category.catID WHERE favourite.memberID = ".$User['memberID']." ");
	while($row1 = mysql_fetch_array($new_fav))
	{
		if ($row1['favourite']) echo " <li>{$row1['favourite']}</li> ";

	}

 

I dont recieve any error.  Just nothing appears.

[shlumph]

Try this to get the error message:

$new_fav = mysql_query("SELECT favourites.*, category.* FROM favourites INNER JOIN category ON favourites.favourite = category.catID WHERE favourite.memberID = ".$User['memberID']." ") or die(mysql_error());

[fife]

if I include that error checking again nothing appears other than a blank section but it also leaves the rest of the page blank too.

[shlumph]

Sounds like you have errors turned off. Try this to get the error message:

error_reporting(E_ALL);
ini_set('display_errors','On');
$new_fav = mysql_query("SELECT favourites.*, category.* FROM favourites INNER JOIN category ON favourites.favourite = category.catID WHERE favourite.memberID = ".$User['memberID']." ") or die(mysql_error());

[fife]

Brill ok I have my error......

 

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sites/edit_member.php on line 239

 

It seems to be referring to...

 

while($row1 = mysql_fetch_array($new_fav))

 

 

from

 

    $new_fav = mysql_query("SELECT favourites.*, club_category.* FROM favourites INNER JOIN club_category ON favourites.favourite = club_category.catID WHERE favourite.memberID = ".$User['memberID']." " or die(mysql_error())) ;
	while($row1 = mysql_fetch_array($new_fav))
	{
		if ($row1['favourite']) echo " <li>{$row1['favourite']}</li> ";

	}

 

It could also be referencing to the line above though

[fife]

the table structure again......

 

FAVOURITES                                CATEGORY                                                       

FavID,                                            catID

memberID,                                      category

favourite,

order

 

catID and favourite are the same.  1 User will have 5 entries in FAVOURITES and they will all be different.

[kickstart]

Hi

 

Echo out the SQL. I can't spot anything wrong there but wondering is $User['memberID'] is something incorrect.

 

All the best

 

Keith

[shlumph]

Your parenthesis are wrong, which is why $new_fav isn't a valid resource. Change

$new_fav = mysql_query("SELECT favourites.*, club_category.* FROM favourites INNER JOIN club_category ON favourites.favourite = club_category.catID WHERE favourite.memberID = ".$User['memberID']." " or die(mysql_error())) ;

to

$new_fav = mysql_query("SELECT favourites.*, club_category.* FROM favourites INNER JOIN club_category ON favourites.favourite = club_category.catID WHERE favourite.memberID = ".$User['memberID']." ") or die(mysql_error());

[fife]

right I made those changes an I still get this error

 

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sites/members/edit_member.php on line 239

 

How do I go about echoing the actual query?

[fife]

ok if I echo the query as asked I get this........

 

 

SELECT favourites.*, club_category.* FROM favourites INNER JOIN club_category ON favourites.favourite = club_category.catID WHERE favourite.memberID = 0000000003

 

I dont get whats going wrong.  The query works great if I write it.....

$fav = mysql_query("SELECT * FROM favourites WHERE memberID = ".$User['memberID']." ORDER BY `order`");
										while($row = mysql_fetch_array($fav))
{
if ($row['favourite']) echo " <li>{$row['favourite']}</li> ";

}

 

This issue is that it only echos the numbers out this way.  Ive done many JOIN's now and they have never been a problem.  I just cant see whats wrong at all.

[shlumph]

Try pasting the echoed query out into your favorite MySQL editor and see if it gives you any errors, or the results you expected. It might be a logical error. I'd make any needed tweaks in the MySQL editor until you get what you want then move it into your PHP file.

[kickstart]

Hi

 

That the id is 0000000003 suggests that the id field might be a character field. If so then it needs quotes around the value.

 

All the best

 

Keith

[PFMaBiSmAd]

Does your actual code have the or die(mysql_error()); so that any query errors would be reported?