I have a client that needs to be able to post articles quickly due to time constraints, and then have them be erased by a CRON job. I have a form set up that they can submit the articles to a MySQL db. The only problem I have is the date, which is use to identify the old posts. The db needs the date in YYYY-MM-DD format, but that is not natural for my client. I found a javascript that does YYYY-M-D, which is close, but I don't like Javascript due to IE security issues (FF isn't going to be used by everyone visiting the site). I have seen ways to get the date using PHP, but I need a step by step process, because I'm still new with PHP. I can post the code I have right now if that's needed, I just didn't know where to start. Thanks.

use [php]<?PHP date("Y-m-d"); ?>[/php] Or you cand find other letters to use here

Why does the DB need the date in the YYYY-MM-DD format? Or do you just assume that it needs it input in that format? Because you can store the date as anything you want and just have the php script turn the data/time into a human readable date when it is pulled out of the DB.
Like you could make the date like this:

[php]
<?php
// Making dates...
$date = date("Ymd"); // 20070110
$date = date('Y-m-d'); // 2007-01-10
//OR use the time() function


$current_time = time();
$past_time = time() - (7 * 24 * 60 * 60); //One week ago in seconds...

$item_time = ($current_time - $past_time) / 60 / 60 / 24;
// Divide the difference by 60 sec, then 60 mins, and then 24 hours to get the number of days.

echo "Item is $item_time days old";
?>
[/php]
You could have a MySQL query like this:

[php]<?php
// Performing SQL query
$query = 'DELETE * FROM `my_table` WHERE `date` <'. $past_time. '`';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Kills all records with a time more than 7 days ago...
?>[/php]

I might have the "<" facing the wrong way...




[php]<?php
$nextWeek = time() + (7 * 24 * 60 * 60);
// 7 days; 24 hours; 60 mins; 60secs
echo 'Now: '. date('Y-m-d') ."\n";
echo 'Next Week: '. date('Y-m-d', $nextWeek) ."\n";
// or using strtotime():
echo 'Next Week: '. date('Y-m-d', strtotime('+1 week')) ."\n";
?>[/php]

The above example will output something similar to:

I would also recomand to store the date as a integer in the mySql instead of a date... Than you could use time() in the query to insert data...

OK, let me put what I have and we can go from there. Please type your replies slowly, though, as I'm not very good at this. And please do say much about the style. This is patchwork and I sure that it could be improved.

This is what I have to process my form:
[php]<?
$date=$_POST['date'];
$news=$_POST['news'];


mysql_connect("localhost", "db_user", "password") or die(mysql_error());
mysql_select_db("db_name") or die(mysql_error());
mysql_query("INSERT INTO `news` VALUES ('$date', '$news')");

?>[/php]

That posts whatever is entered in the form, and works like it should.

To display the db records:

[php]<?php

// Create the database access variables:
$hostname = "localhost";
$database = "db_name";
$username = "db_user";
$password = "password";


// Open a connection to the server
$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect to the DB Server!");


// Select the database
mysql_select_db($database) or die ("Can't select database!");


// Show data query
$query = 'SELECT * FROM `news` ORDER BY `date` ASC LIMIT 0, 30';

// execute query
$result = mysql_query($query) or die ("Error in query: $query. <br>".mysql_error());


echo '<table width="800" cellspacing="0" cellpadding="5">'
. '<tr>'
. '<td>date</td>'
. '<td>news</td>'
. '</tr>';

while($row = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td>". $row[0]. "</td>";
echo "<td>". $row[1]. "</td>";
echo "</tr>";
}

echo '</table>';

?>[/php]

Again, it works, though it may not be pretty.

And then to delete entries:
[php]<?php
$hostname = "localhost";
$database = "db_name";
$username = "db_user";
$password = "password";

$connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect to the DB Server!");


mysql_select_db($database) or die ("Can't select database!");

$result = mysql_query('DELETE FROM `news` WHERE `date`<CURDATE()') or die ("Error in query: $query. <br>".mysql_error());
?>[/php]
Only works with date in YYYY-MM-DD format.

I wouldn't care to start over from scratch, but like I said, I'm new and will need step by step instructions. I do thank both of you for your help, its just that I don't know how to use what you've told me. I've posted my code so that you can see where I'm at, and tell me where I should go from here. Thanks again.

First lets put all of our Database stuff in a new file so that we don't have to worry about it:

Config.php:
[php]
<?php
// Create the database access variables:
$hostname = "localhost";
$database = "db_name";
$username = "db_user";
$password = "password";


mysql_connect("localhost", "db_user", "password") or die(mysql_error());
mysql_select_db("db_name") or die(mysql_error());
?>
[/php]


Then name this page "add_news.php" or whatever and it is the page that will take the news that was posted and add it to the database:
add_news.php:
[php]
<?php
//$date=$_POST['date']; bad way of making date...
$current_time = time();
$news = trim($_POST['news']); //Get rid of extra whitespaces etc...

require_once(config.php);

$query = "INSERT INTO `news` VALUES ('$current_time', '$news')";
$result = mysql_query($query) or die ("Error in add query: $query. <br>".mysql_error());

echo "The News was added!";
?>
[/php]

Now that we want to show the news (you can name this whatever you want).
show_news.php
[php]
<?php
require_once(config.php);
$past_time = time() - (7 * 24 * 60 * 60);
// One week ago in seconds...
// If you want to change to 30 days do this:
// $past_time = time() - (30 * 24 * 60 * 60);
// Or 1 day
// $past_time = time() - (1 * 24 * 60 * 60);
// (Day * Hours * Minutes * Seconds)













/*######### DELETE OLD VALUES ###################*/

$query = 'DELETE * FROM `news` WHERE `date` < '. $past_time .' LIMIT 0, 200';
// execute query
$result = mysql_query($query) or die ("Error in delete query: $query. <br>".mysql_error());









/*######### GET NEWS ITEMS ###################*/
// Show data query
$query = 'SELECT * FROM `news` LIMIT 0, 200';

// execute query
$result = mysql_query($query) or die ("Error in query: $query. <br>".mysql_error());


echo '<table width="800" cellspacing="0" cellpadding="5">'
. '<tr>'
. '<td>date</td>'
. '<td>news</td>'
. '</tr>';

/*
Notice I am using mysql_fetch_assoc() instead of
mysql_fetch_row() because this way you don't have to have $row['1']
instead you can name it $row['name_of_DB_column'];
*/

while($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>". $row['date']. "</td>";
echo "<td>". $row['news']. "</td>";
echo "</tr>";
}

echo '</table>';

?>

[/php]

OK, David. Everything works, except I get an error that says

Error in delete query: DELETE * FROM `news` WHERE `date` < 1169065513 LIMIT 0, 200.
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 '* FROM `news` WHERE `date` < 1169065513 LIMIT 0, 200' at line 1

when I run the script to erase old data. I figure its something simple, but I don't know what. I copied the code from your post, so there may be something I need to change.

And while I have your ear, where do I put the code to format the date into something readable when I let people view it. I'm sorry if this is bothersome, but I'm having trouble getting all of this in my head where I can understand it.

Ok, I never tested this code, so I am not surprised there are small syntax errors...but I am sure that this would be the way to do it.

If you ever have a problem with something like a DELETE statement it is time to hit google and search for any info on the DELETE command so that you will understand how to use it.

http://www.tizag.com/mysqlTutorial/mysqldelete.php
http://www.php-mysql-tutorial.com/mysql ... delete.php
http://www.w3schools.com/php/php_mysql_delete.asp

Sorry, but I don't have time to install a test DB and try to turn this into a full working script.

Also, as far as turning time into human readable - I am pretty sure that since it is the time in seconds since the Unix Epoch (January 1 1970 00:00:00 GMT) you just need to use some basic math to divide by 60 (seconds) then 60 (minutes) and then the number of hours (24 in a day) to get a more understandable date.

http://www.computerworld.com.au/index.p ... fpid;76768

http://us3.php.net/date
http://us2.php.net/time
http://www.w3schools.com/php/php_date.asp



Hope this helps!

Thanks David and Alxandr, with what you both wrote and a few other things I found while researching the links, I have something that will work. When I get a little time, I'll work on getting the code cleaned up.

try where date < 'somNumber' (Use ' arround the number...)