Subqueries

[bogglebeats]

i am trying to execute the first query and take the values of that result and place them in the second query to be executed one by one. can someone please help me? 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);

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

[bretticus]

I dunno if you included all your PHP syntax but the queries should work fine. If you're getting errors, what are the messages you get?

[bretticus]

Whoops, after lookign at it again a bit closer I think your problem may that that you need to escape your double quotes in your query command.

[bretticus]

Man, I post too fast!

Actually, you'll need to concatenate probably:

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

[bogglebeats]

yeah, the concatenation looks nice but it's still not working man.

i am getting parse and syntax errors. here is what i have now:

$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($result)) {

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

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

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

sorry for the late reply. thank you very much

ameen

[bretticus]

What parse and syntax errors are you getting?

[bogglebeats]

which double quotes should i remove from the query command?

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

Parse error: parse error in /home/bogglepr/public_html/weeklyupdate.php on line 14

the included code above is line 14.

[bretticus]

hmmm....

try changing the double quotes for the row array keys:

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

or build the string first:

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

$result = mysql_query($sql, $db);

[bogglebeats]

i am still getting the same error when i use single quotes instead of double quotes. also when i build the string first, i get the same error. any other ideas?? thanks

[bretticus]

Well I just noticed that myfirstrow['Genre_Select']) needs a $...

$myfirstrow['Genre_Select'])

Or is that just the code you posted only?

[bogglebeats]

no thats not the problem i added one in there. is it the concatenation?

[bretticus]

One of us needs to take a few minutes and look at this little snippet ;-) .... sorry 'bout that.

I see one more problem:

The ) is not in the string and that's my mistake from the very first time I posted the concatenated string, anyways, try this:

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

If you get a MySQL error it might be single-quotes missing from the city and genre select unless they're integer ids or something.

Good Luck! 😃

[bogglebeats]

excellent. yeah that was it. i want to show you the rest of the code too. what i am trying to do is send weekly updates of new members (those who signed up within the past 7 days) to members of my database. the new member information would be sent to those 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.

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

3)send information of these new artists to the existing artists that have the same city and genre of the new artists.

I am having a problem with the $emailresult query. i don't think it likes the Date_Created > curdate()-7 statement. i keep getting the message:

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

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

thank you for all your help.