Removing MySQL Query by Pull Date

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gregwhitworth
Forum Commoner
Posts: 53
Joined: Tue Oct 09, 2007 1:00 am
Location: Wasilla, Alaska

Removing MySQL Query by Pull Date

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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())
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
gregwhitworth
Forum Commoner
Posts: 53
Joined: Tue Oct 09, 2007 1:00 am
Location: Wasilla, Alaska

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

What's annoying about YYYY-MM-DD?

And what does this code have to do with your original problem?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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();
User avatar
gregwhitworth
Forum Commoner
Posts: 53
Joined: Tue Oct 09, 2007 1:00 am
Location: Wasilla, Alaska

Post 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
Post Reply