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.
PHP Code:
$sql="SELECT id, post, username FROM pickles WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) && ageNote = '0' ";
$r=mysql_query($sql);
Yeah, you don't need to select a thing. Since you are not doing anything with any of this data, simply execute an update:
Code:
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.
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!
PHP Code:
$sql="SELECT id, post, username FROM pickles WHERE date < DATE_SUB(NOW(), INTERVAL 2 MONTH) && ageNote = 0 "; $r=mysql_query($sql);
$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); }
Last edited by Serihon; 02-24-2012 at 10:26 PM..
Reason: Solved
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:
PHP Code:
$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());
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.
PHP Code:
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); }
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.
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!
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
Before you post, read our: 
