PHP MySQL date problem

[plastik77]

Hi, this is probably fairly straightforward but I'm struggling to solve it - I have a CMS where the user can edit existing articles they have uploaded. The problem is that the article date is taken using the MySQL timestamp function. If an article is edited, I want the article to retain its original posting date, and not to take the date of the edit. In my update statement after an article has been edited, I do the following:

$updateQuery = sprintf("update cmsarticles set title='%s', thearticle='%s', creator='%d', date='%s' where ID = $_SESSION[id]",             
	mysql_real_escape_string($title),             
	mysql_real_escape_string($article), 	
        $date, //where $date is date of the original posting
	$_SESSION['user_id']);    

The problem is that this code just inserts 0000-00-00 00:00:00 into the date field. I realise the above code is certain to be wrong (i've specified date=%s, when it won't be a string), but I can't find any pointers on how to rectify it. Any help would be greatly appreciated!

[rajivgonsalves]

what is the format for $date, and what is date datatype in the db ?

[plastik77]

In the database, the date datatype is timestamp (with ON UPDATE CURRENT_TIMESTAMP). The $date variable is where I've stored the original posting date, which I want to insert into the database as part of the update, in order to override the timestamp from taking the current date.

[rajivgonsalves]

whats the format I mean when you do a echo $date? what does it show you

[plastik77]

ah sorry about that - the format is (for example) 2008-01-23 09:42:34 when i echo the $date. I used an echo to check that $date was picking up the correct date. The problem is that when i try to insert this value it is inserting 0000-00-00 00:00:00 instead of the correct value

[rajivgonsalves]

try this

$updateQuery = sprintf("update cmsarticles set title='%s', thearticle='%s', creator='%d', date='$date' where ID = $_SESSION[id]",             
	mysql_real_escape_string($title),             
	mysql_real_escape_string($article), 	
	$_SESSION['user_id']);    

[plastik77]

Thanks for the suggestion Rajiv, but this is still just giving me 0000-00-00 00:00:00 in the date record. Here's more of the code, in case I'm making an error elsewhere:

//check for item to be edited
if ($_GET['id']) {
//get id of question to be edited
$id = $_GET['id'];
$_SESSION['id'] = $id;
$result = $connector->query("select * from cmsarticles where ID = $id");
$row = $connector->fetchArray($result);
$title = $row['title'];
    $article = $row['thearticle'];
$date = $row['date']; //storing the original posting date here	

//check if there is an image attached to the story
$result2 = $connector->query("select * from file_table where article_id = $id");
$numrows = $connector->getNumRows($result2);
$row2 = $connector->fetchArray($result2);
}
// Check whether a form has been submitted. If so, carry on
if (isset($_POST['Submit'])){

$title = $_POST['title'];
$article = $_POST['thearticle'];
// Validate the entries
$validator = new Validator();
$validator->validateGeneral($title,'Article Title');
$validator->validateGeneral($article,'Article');

//if a file has been attached, check for the alt tag first, before processing
if (!empty($_FILES['upload']['name'])) {
	$validator->validateGeneral($_POST['description'],'Description');
}
// Check whether the validator found any problems
if ($validator->foundErrors() ){
	$error = "<h5 class='error'>Error! There was a problem with the following fields: <br/>".$validator->listErrors('<br/>')."</h5>"; // Show the errors, with a line 	between each
}
else{
      	//Use mysql_real_escape_string to escape special characters in a string for use in a SQL statement - makes statement safe
	$updateQuery = sprintf("update cmsarticles set title='%s', thearticle='%s', creator='%d', date='$date' where ID = $_SESSION[id]",             
	mysql_real_escape_string($title),             
	mysql_real_escape_string($article), 	
	$_SESSION['user_id']);  

	// Save the form data into the database
	if ($result = $connector->query($updateQuery)){

		if(!empty($_FILES['upload']['name'])) {
			//populate file upload variables
			$my_upload->upload_dir = $_SERVER['DOCUMENT_ROOT']."/home/lomond_school/CMS/upload/files/"; // "files" is the folder for the uploaded files 
			$my_upload->extensions = array(".jpg"); // specify the allowed extensions here
			// $my_upload->extensions = "de"; // use this to switch the messages into an other language (translate first!!!)
			$my_upload->max_length_filename = 100; // change this value to fit your field length in your database (standard 100)
			$my_upload->rename_file = true;
			$my_upload->the_temp_file = $_FILES['upload']['tmp_name'];
			$my_upload->the_file = $_FILES['upload']['name'];
			$my_upload->http_error = $_FILES['upload']['error'];
			$my_upload->replace = "y";
			$my_upload->do_filename_check = "n"; // use this boolean to check for a valid filename
			if ($my_upload->upload()) { // new name is an additional filename information, use this to rename the uploaded file
				mysql_query(sprintf("INSERT INTO file_table (file_name,description,article_id) values ('%s', '%s', '%d')",
				mysql_real_escape_string($my_upload->file_copy),
        			mysql_real_escape_string($_POST['description']),
        			$_SESSION['id']));
			}
		}
		if(trim($my_upload->show_error_string())=="")
		header("location: latest_news_edit.php");
		else
			$error = "<h5 class='error'>".$my_upload->show_error_string()."</h5>";
	}		
	else{
		// It hasn't worked so stop. 
		$error = "<h5>Sorry, there was an error saving to the database</h5>";
	}
}
}

[rajivgonsalves]

try this if you wanna retain the date

 

$updateQuery = sprintf("update cmsarticles set title='%s', thearticle='%s', creator='%d', date=date where ID = $_SESSION[id]",             
	mysql_real_escape_string($title),             
	mysql_real_escape_string($article), 	
	$_SESSION['user_id']);

 

hope its helpful

[plastik77]

Works a treat -thanks for your help Rajiv!