Page 1 of 1
Removing MySQL Query by Pull Date
Posted: Sat Oct 20, 2007 2:32 pm
by gregwhitworth
Okay, I know I already have a post concerning this subject, but no one is helpin' me out. Here is the scoop:
I have a field for the admin called Pull Date, and I want the news item to be deleted from MySQL if the current date is greater than the pull date.
Example:
- Current Date: 10/20/2007
Pull Date: 02/03/2008 (This would be kept)
Pull Date 2: 02/03/2007 (This would be deleted)
Now, I have tried - but can't seem to get it to work - I have the following:
Code: Select all
varaible $time = strtotime("now");
mysql_query("DELETE in news where pull < $time");
but it doesn't work correctly. I would love to see if anyone has used this method before and what they did?
--
Greg
Posted: Sat Oct 20, 2007 2:41 pm
by John Cartwright
How are you storing the dates in your database?
Also, try adding some error reporting
Code: Select all
varaible $time = strtotime("now");
mysql_query("DELETE in news where pull < $time") or die(mysql_error())
Posted: Sat Oct 20, 2007 2:49 pm
by superdezign
What format of timestamps does your database use? strtotime() doesn't give you the YYYY-MM-DD HH:MM:SS format. You'll need to utilize
date() as well.
Posted: Sat Oct 20, 2007 3:47 pm
by gregwhitworth
Thanks for replying. My mysql stores the date data in the normal old annoying YYYY-MM-DD, even though I've tried to change it, but that's another story to be told over the camp fire.
Anyways, here is my code - I am posting it all, but there isn't much of it - so it shouldn't be too over bearing. This is excluding the script that I created.
Code: Select all
<?php
/* connection settings */
$username = "";
$password = "";
$hostname = "localhost";
$select = "SELECT header,date,description,pull,id FROM news";
$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
/*print ("Connected to MySQL<br>");*/
//other variables
$time = strtotime("now");
$selected = mysql_select_db("biglakebaptist",$dbh) or die("Could not select biglakebaptist");
/*print ("Selected biglakebaptist<br>");*/
$result = mysql_query($select) or die("Could not select news");
/*print ("Selected news table<br>");*/
while($col=mysql_fetch_assoc($result) ) {
echo '<div class="newsitem">';
//Delete button settings
echo '<form action="admin.php" method="post">'."\n";
echo '<input name="id" type="hidden" value="'.$col['id'].'">';
echo '<div>'."\n";
echo '<input class="deleteButton" type="submit" value="Delete" name="delete">'."\n";
echo "</div>\n";
//Overall Item Display Settings
echo "<h4>Header: ".$col['header']."</h4>";
echo "Date: " .date('m-d-Y', strtotime($col['date']));
echo "<br><br>Description:<br>\n";
echo $col['description']."<br>\n";
echo "<br>Pull Date: " .date('m-d-Y', strtotime($col['pull']))."\n";
echo "</div>";
echo '</form>'."\n";
}
mysql_close($dbh);
?>
Thanks for the help.
--
Greg
Posted: Sat Oct 20, 2007 4:12 pm
by superdezign
What's annoying about YYYY-MM-DD?
And what does this code have to do with your original problem?
Posted: Sat Oct 20, 2007 4:20 pm
by RobertGonzalez
You don't need the date from the user, but you can use it. MySQL has a lot of data functions that you can use to handle this.
Code: Select all
DELETE FROM `news` WHERE `pull` < NOW();
Posted: Sat Oct 20, 2007 6:03 pm
by gregwhitworth
What's annoying about YYYY-MM-DD?
I guess annoying may be the wrong terminology, but I am so used to typing DD/MM/YYYY for my dates that I have to stop and think about it before I type. Just not as comfortable I guess.
And what does this code have to do with your original problem?
This has everything to do with my original problem as this is the code that is displaying the news data that is to be pulled. I said that I didn't include the one line of code that I created, as to not inhibit any other ideas or ways of accomplishing this task.
You don't need the date from the user, but you can use it. MySQL has a lot of data functions that you can use to handle this.
I am very new at this, actually, this is my first MySQL and PHP projects, so any help you can give me would be great. The less that the user has to type in the better, and less opportunities for problems.
Thanks,
Greg