...

View Full Version : Resolved mysql_query in a mysql_query



Serihon
02-24-2012, 10:13 PM
Hey everyone,

I am curious, would this code work? So far it isn't working for me but would appreciate some input on whether there would be a better way to handle it.



$sql="SELECT id, post, username FROM pickles WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) && ageNote = '0' ";
$r=mysql_query($sql);

while($row=mysql_fetch_assoc($r)){
$id=$row['id'];
$post=$row['post'];
$username=$row['username'];

$sql2="SELECT email FROM potatoes WHERE username='$username' ";
$result2=mysql_query($sql2);
while($row2=mysql_fetch_assoc($result2)){ $email=$row['email']; }

$sql3="UPDATE pickles SET ageNote='1' WHERE id='$id' ";
mysql_query($sql3);
}


Thanks!

Serihon

Fou-Lu
02-24-2012, 10:20 PM
Yeah, you don't need to select a thing. Since you are not doing anything with any of this data, simply execute an update:


UPDATE pickles SET ageNote=1 WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) AND ageNote = 0

No need to select at all.

As for "not working", you need to be more specific. The nested loop would work just fine, but if you don't do anything with $email you'd never know. Although its a waste to query within a loop, for that you should use an inner join on your queries to link two tables together. The only thing that may not work is that if these number (which I assumed they are) are actual integers, mysql can reject them when provided as strings. By default I don't believe that mysql operates strict.

Serihon
02-24-2012, 11:17 PM
Well I am actually using the code. I use it to send an email.

ageNote is actually a boolean in the database so not sure if it needs to be in parenthesis?

I was using the first query to find entries that are older than two months. The second query is to pull the users email address and email them to warn them about this. The third and final query is to update the current entry's ageNote to 1. Should I pull the third query out of the first query and just run it as a separate query after the first?

Removing the parenthesis around the integers actually fixed the problem and it is working now. Thanks Fou-Lu!


$sql="SELECT id, post, username FROM pickles WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) && ageNote = 0 ";
$r=mysql_query($sql);

while($row=mysql_fetch_assoc($r)){
$id=$row['id'];
$post=$row['post'];
$username=$row['username'];

$sql2="SELECT email FROM potatoes WHERE username='$username' ";
$result2=mysql_query($sql2);
while($row=mysql_fetch_assoc($result2)){ $email=$row['email']; }

//Email info to user
$from = 'noreply@mywebsite.org';
$to = $email;
$subject = 'Post Pending Deletion Warning: '.$post;
$message = "Dear $username, \n Your post $post has reached the age of two months old. Once the post reaches the age of three months old it will be deleted from our system forever. If you like you can extend the life of your post by following one of the links listed below. If you do not want to keep the post active then please disregard this message. This will be your only warning for the deletion of this post. \n ";

mail($to, $subject, $message, $from);

$sql3="UPDATE pickles SET ageNote=1 WHERE id='$id' ";
mysql_query($sql3);
}

Fou-Lu
02-24-2012, 11:28 PM
Nope, there are no actual booleans in mysql, they are tinyint values, so you shouldn't quote them since they are numbers.
The context is much better. Query with a join on your select and capture the results of the known id's into an array. This can be used within an IN clause:


$sQry = 'SELECT p.id, p.post, po.username, po.email
FROM pickles p
INNER JOIN potatoes po ON po.username = p.username
WHERE p.date < DATE_SUB(NOW(), INTERVAL 2 MONTH) AND p.ageNote = 0';
$qry = mysql_query($sQry) or die(mysql_error());
$aIDs = array();
while ($row = mysql_fetch_assoc($qry))
{
$aIDs[] = $row['id'];
// send an email
}
$sUpdate = 'UPDATE pickles SET ageNote = 1 WHERE id IN (' . implode(', ', $aIDs) . ')';
mysql_query($sUpdate) or die(mysql_error());

Serihon
02-24-2012, 11:53 PM
I will try that.

I have a separate but different question. I want to fill a field in my table with a date that is increased by a month. I think I am close to figuring it out but it isn't working. Do you think this would work? The date in the TIMESTAMPADD would be a field of the entry of the table that is being updated.




include('dbCon.php');

//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
$str = @trim($str);
if(get_magic_quotes_gpc()) {
$str = stripslashes($str);
}
return mysql_real_escape_string($str);
}

$id=clean($_GET['id']);
$term=clean($_GET['term']);

$sql="UPDATE posts SET ageNote=0 keep=TIMESTAMPADD(MONTH, '$term', date) WHERE id='$id' ";
mysql_query($sql);

Fou-Lu
02-25-2012, 12:07 AM
In sql you can use the + overload to use datefield + INTERVAL 1 MONTH for example, or you can use the DATEADD. Timestampadd should also work. Don't forget to add the comma between the fields you are updating.

Serihon
02-25-2012, 12:16 AM
In sql you can use the + overload to use datefield + INTERVAL 1 MONTH for example, or you can use the DATEADD. Timestampadd should also work. Don't forget to add the comma between the fields you are updating.

That worked beautifully! Thanks so much for your expert help!

Serihon
02-27-2012, 07:23 PM
Is this a valid statement?


SELECT p.id p.post p.username u.email
FROM comments c
INNER JOIN posts p ON c.postId = p.id
INNER JOIN users u ON c.username = u.username
WHERE c.date= DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY c.postId ASC

I get the following error from it when running it in a SQL Query window.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.post p.username u.email FROM comments c INNER JOIN posts p ON c.postId = p.id I' at line 1

Fou-Lu
02-27-2012, 07:27 PM
No, you need to add commas between which fields you are selecting.

Serihon
02-27-2012, 07:31 PM
No, you need to add commas between which fields you are selecting.

Thanks. *sigh* Forgot my coffee this morning it seems...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum