calling a query result within queries

bogglebeats

in the code below, i am trying to call the result of the $newartist query inside the $result query. Am i in the right direction?

$newartist = mysql_query("SELECT Distinct City, Genre_Select FROM Musicians_Network WHERE Date_Created > DATE_SUB( CURDATE(), INTERVAL 7 DAY )", $db);

if ($myfirstrow = mysql_fetch_array($newartist)) {

do {

$result = mysql_query("SELECT Contact FROM Musicians_Network WHERE (City = ".$myfirstrow['City']." or Genre_Select = ".$myfirstrow['Genre_Select'].") and Updates = 'Yes'",$db);

}while ($myfirstrow = mysql_fetch_array($newartist));
}

tomhath

Not really.

There's no reason for a second query. What you want is a LEFT JOIN. And simplify the php code by using a WHILE loop instead of a DO loop inside an IF.

SELECT Distinct mn1.City, mn1.Genre_Select, mn2.Contact
FROM Musicians_Network mn1
LEFT JOIN Musicians_Network mn2
ON (mn1.City = mn2.City OR mn1.Genre_Select = mn2.Genre_Select)
AND mn2.Updates = 'Yes'
WHERE mn1.Date_Created > DATE_SUB( CURDATE(), INTERVAL 7 DAY

bogglebeats

$newartist = mysql_query("SELECT Distinct mn1.City, mn1.Genre_Select, mn1.Artist_Name, mn1.State_or_Province, mn1.Country, mn1.Genre_Enter, mn1.Artist_Function, mn1.Homepage, mn2.Contact FROM Musicians_Network
mn1 LEFT JOIN Musicians_Network mn2 ON (mn1.City = mn2.City or mn1.Genre_Select = mn2.Genre Select) AND mn2.Updates = 'Yes' WHERE mn1.Date_Created > DATE_SUB( CURDATE(), INTERVAL 7 DAY )", $db);

$myfirstrow = mysql_fetch_array($newartist);

printf("<tr><td><div align =center>%s</div></td></tr>\n",
$myfirstrow["City"]);

printf("<tr><td><div align =center>%s</div></td></tr>\n",
$myfirstrow["Genre_Select"]);

printf("<tr><td><div align =center>%s</div></td></tr>\n",
$myfirstrow["Contact"]);

I have added a few fields in the query. When i try to run it i get:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/bogglepr/public_html/weeklyupdate.php on line 10

which refers to the line somewhere around mn1 LEFT JOIN in the query. Should i put the AND statement after the WHERE?