help with complex queries with nested statements

[bogglebeats]

what i am trying to do is send weekly updates of new members (those who signed up within the past 7 days) to existing members of my database. the new member information would be sent to those existing members that have the same city and genre_select of the new members.

i am attempting the queries in 3 steps.

1)select the city and genre of entries in the database that have been created in the past 7 days. $newartist query

2)match these cities and genres of already existing members that have answered YES to receive the updates. $result query

3)collects the new member information and sends this information to the existing artists that have the same city and genre of the new artists. $emailresult query

Here is the code:

$newartist = mysql_query("SELECT Distinct City, Genre_Select FROM Musicians_Network WHERE Date_Created > curdate()-7", $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);

$emailresult = mysql_query("SELECT Artist_Name, City, State_or_Province, Country, Genre_Select, Genre_Enter Artist_Function, Homepage FROM Musicians_Network WHERE (City = ".$myfirstrow['City']." or Genre_Select = ".$myfirstrow['Genre_Select'].") and Date_Created > curdate()-7", $db);

if ($myrow = mysql_fetch_array($emailresult)) {

do {
$update_email = $update_email.",".$myrow["Contact"];

} while ($myrow = mysql_fetch_array($emailresult));

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

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

everytime i try to run this, i get an error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/bogglepr/public_html/weeklyupdate.php on line 20
which refers to the last line in the $emailresult query around Date_Created > curdate()-7. i am on a serious deadline and don't know what to do. can someone please help me?

[RoadKingHD]

Have you tried your SQL in say PhpMyAdmin or another db tool to see that the sql actually acts as you expect it to?

Maybe echo the $sql var (your select statement) to the screen and look it over to see that it is formed as it should be.

How about echo the mysql_error() stuff to see what the database is having the problem with?

I have found solving complex sql statements are easy using a db tool to test my sql code before placing it in PHP.

Just my approach...

[bogglebeats]

you are absolutely right. i have been testing the queries in phpmyadmin and they seem to be fine. i think there is something wrong with the nested IF or DO WHILE statements. It is very frustrating as it seems to be something small..

[RoadKingHD]

Maybe break down your DO...WHILE statements (maybe in a new .php file) and simply echo stuff to the screen. This might help you find where the problem is.

The error:

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

would suggest the some where in your loop you are calling a reference id that is not there or other wise not there.

[RoadKingHD]

Upload a text file with your actual code and if you could, the db schema if you like and I'll look it over in more detail.

[bogglebeats]

i took out the do while segments and echoed the $newartist query and i am getting a message:

Resource id #2 when i run it through internet explorer.

when i put it in myphpadmin in the database tools it works fine. what does that resource id message mean?

[bogglebeats]

the code should be uploaded.

what i am trying to do is send an email to current members of my database information about new members that signed up within the last week.

the way it works is that ONLY the existing members that have the same City and Genre_Select of the new members AND selected 'Yes' to the Updates field will be getting this email with information regarding the new artists.

Three queries have been made: $newartist, $result, and $emailresult.

so for example if a new aritist with the city=chicago and genre_select = hiphop signs up, then i want all of the artists affiliated with chicago or hip hop to get an email stating the new artists presence. the email will include the new artists name, city, genre, homepage, etc. Details should be apparent in the code. thanks.

ameen

[RoadKingHD]

I'll look over your code.

The resource ID is the "link" to the data, a pointer if you will. This is the result of mysql_query($sql, $link) or mysql_connect(). Make sure all of your db functions are working properly.

$link = mysql_connect($hostname, $username, $password)
OR DIE(echo "Database connect error".mysql_error());

or something like that with all of your db functions, at least for debugging. You might want to hush (@mysql_connect()) the errors in production code and handle them with an error page for the user rather than the system error codes.

What does your connect code look like? I'll look in your text file.

[bogglebeats]

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

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

when i do this, the first 2 printf statements are successful (city and genre_select), but the 3rd one, contact which corresponds to the the $result query, is not printing.

[RoadKingHD]

Hey bogglebeats,

I spent some time on your code. I was able to run your code after fixing some minor things. Some missing single-quotes in the two sql's that city & genre. There were a couple of other small things but now I don't recall.

So the code works, but it is not producing data results.

I'll upload a .zip of what I did and some new code that pulls data from my "test" database.

Hope this is of some use.

Kevin