[SOLVED] mysql / php nested query

[shanman]

Hi all,

 

I have 2 tables, 1 called favourites, the other merchant

 

For example:

 

favourites

id=user1 mercid=1

id=user1 mercid=3

id=user2 mercid=1

 

merchant

mercid=1 name=merc1 desc=blablabla

mercid=3 name=merc3 desc=blablabla

 

How can I write a php/mysql query to list the name and desc of each merchant according to the users login id? For example if user1 was logged in he would be presented with his favourite merchants and if it was user2 he would get his favourite merchants. The results will need to be output to a browser of course.

 

Hope this makes sense - I'm really struggling.

 

Thanks in advance

[Hybride]

Joins aren't my forte, I can't remember if it's MySQL that requires that same number of rows to link to, but here's a shot at it:

$query = "SELECT merchant.name,merchant.desc, favourites.id FROM merchant,favourites WHERE merchant.mercid=favourites.mercid";

Then loop through the results you want.

Here's a page on joins that can help you out, too: MySQL join tutorial.

[shanman]

I gave it a try but it didnt work:

 

 

 

Here is the code I used (At the moment I just want to output the name of the Merchant - I'll work the detail when I get this bit working.)

 

$query = "SELECT merchant.name from merchant, favourites where favourites.mercid = merchant.id and favourites.user='$user')
$result = mysql_query($query)
while ($row = mysql_fetch_array($result)){
echo $row['name'];
}

 

Not sure if I explained things properly in the first post.

 

User A is logged in - he hits a link called favourites and the code should basically do the following:

 

select mercid from favourites where id='$user' (there will be several mercid's)

 

Then

 

For each mercid ...

 

Select name from merchants where id=mercid

 

and output the list of name to the browser.

 

Hope this makes sense cos this is driving me crazy lols.

[shanman]

Managed to fix it myself but thanks for the help.

 

$sq = mysql_query("SELECT mercid from favourites where user='$user'");
$rows=mysql_num_rows($sq);
for($i=0; $i<$rows; $i++)
{
mysql_data_seek($sq,$i);
$arr=mysql_fetch_array($sq);
extract($arr);
$sq2 = mysql_query("SELECT * from merchant where id=$mercid");
$arr2=mysql_fetch_array($sq2);
extract($arr2);
echo"$name";
}