Join Issues

fife · April 18, 2011

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 · April 18, 2011

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 · April 19, 2011

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 · April 19, 2011

Hi

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

favourites

table

All the best

Keith

fife · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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 · April 19, 2011

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

Sign In

Join Issues

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information