Left Join On

bogglebeats

I am trying to execute the following query and am getting:

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

$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);

logically seems right but don't know too much about this. Do i need to define mn1 and mn2? maybe mn1.id or mn2.id?

bbaassiri

[man]mysql_fetch_array[/man]

that would help

bogglebeats

i didn't post all of it. here it is.

while(
$myfirstrow = mysql_fetch_array($newartist))

{

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

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

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

Lars_Berg

Do some error checking. Call mysql_error to get a relevant message

$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) or die (mysql_error());

Eric_Bartels

Originally posted by bogglebeats
...ON (mn1.City = mn2.City or mn1.Genre_Select = mn2.Genre Select)

I think there is a "_" missing (mn2.Genre_Select) 🙂

bogglebeats

the code seems to have no errors now, but it is still not returning any values.

$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);

while(
$myfirstrow = mysql_fetch_array($newartist))

{

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

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

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

Is that the right way to display the ids in the printf statements?

ednark

i suggest starting from scratch..

run an empty query...( SELECT * FROM TABLE )

put in one more thing... test it... one more thing... test it

its hard to figure out all the bad things in a large complex chunck of code like your sql statement... hard to keep it all in your head at one time... so break it up into little pieces so you can think better

tomhath

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

No. Remove the table prefix:

$myfirstrow["Contact"]

bogglebeats

thanks for your help guys. it is working for the most part. the email goes out but i cannot get the proper data to display in the sent email. here is the code again:

$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);

while(
$myfirstrow = mysql_fetch_array($newartist))

{

$update_email = $update_email.",".$myfirstrow["Contact"];

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"]);

}

$update_recipient = $update_email;
$update_subject = "A new artist in your city or genre has been added";
$update_message = "Artist Name: $Artist_Name
Location: $City, $State_or_Province, $Country
Genre: $Genre_Select, $Genre_Enter
Function: $Artist_Function
Homepage: $Homepage";
$update_extra = "From: Boggle_Productions<>\r\nReply-To: ()\r\n";
mail ($update_recipient, $update_subject, $update_message, $update_extra);

In the part above, in the Update Message, i can't get the Artist_Name, $City, etc. to display in the messge. how should i display these ids? using $myfirstrow["Artist_Name"] in place of $Artist_Name doesnt work either. thanks.