[RESOLVED] Update Query Problem

obrienkev

Hi all,

For some reason the database table is not updated.
Why is this?

// perform query
$query = "UPDATE centralised_archive.notes ".
			 	 "SET note = '$note' ".
				 "WHERE date = $record";

echo $query;

$result = mysql_query($query) or die(mysql_error());

echoing displays: UPDATE archive.notes SET note = 'TestEDIT archive 27th oct.' WHERE date = 2005-10-26

thorpe

are you getting an error? what is it?

obrienkev

Getting no error. The data is not being updated in the database and I don't know why.

if ($result)
{
 	 echo '<p>Updated</p>';
}

Updated is always displayed but the database entry has not been updated.

Snake_PHP

<?
 // perform query 
$query = "UPDATE centralised_archive.notes ". 
                  "SET note = '$note' ". 
                 "WHERE date = $record"; 

echo $query; 

$result = mysql_query($query) or die(mysql_error());  

?>

<?
 // perform query 
$query = "UPDATE centralised_archive.notes ". 
                  "SET note = '$note' ". 
                 "WHERE date = $record"; 

// echo $query; 

$result = mysql_query($query) or die(mysql_error());  

if($result){

ECHO " done !";

}else{

ECHO " Error ";

}
?>

Roger_Ramjet

It will not be updating any records because none match your $record var. Echo the query to the screen to see what EXACTLY is being sent to the db.

Also, you should NEVER name a column as 'date'. It is a reserved word - and the name of an sql function. Mysql will accept it for backward compatibility reasons, but just about every other db engine will not so you are writing non-portable code and getting into bad habits that will trip you up when you start working with another db engine one day.
It is also meaningless in the real sense: what date? date entered? date updated? birthdate? etc. Always name your tables and columns as meaningfully as possible.

justsomeone

Should the date in your query be in quotes?

I have a feeling that 2005-10-26 without quotes may be interpreted as a math function (2005 minus 10 minus 26) so that it becomes interpreted as:

UPDATE archive.notes SET note = 'TestEDIT archive 27th oct.' WHERE date = 1969

which may not be true for any records in your table. SO it would be syntactically correct ( hence no errors) but just not what you expect.

You can quickly test this theory by going to your mysql prompt and seeing fir the following commands produce the same result.

SELECT count(1) from archive.notes WHERE date = 2005-10-26

SELECT count(1) from archive.notes WHERE date = '2005-10-26'

Do they both produce different values? If so, that may be your problem.

If they both produce a value of zero, could it be that your date field holds more detail than date, does it also have the time in there?

obrienkev

Tried all there and no success.

Here's my php code:

// declare variables
$note = $_POST['note'];
$record = $_POST['record'];

// connect to db
include('../../db_connect.php');

// perform query
$query = "UPDATE centralised_archive.notes ".
			 	 "SET note = '$note' ".
				 "WHERE file_date = $record";

echo $query;

$result = mysql_query($query) or die(mysql_error());

if ($result)
{
 	 echo '<p>Updated</p>';
}

Then this is displayed on the browser:

UPDATE centralised_archive.notes SET note = 'testingedit' WHERE file_date = 2005-06-20

Updated

Yet the database is not updated.

Here is my database structure:

id{int}
note{text}
file_date{date}

Why is this??

justsomeone

justsomeone wrote:
You can quickly test this theory by going to your mysql prompt and seeing fir the following commands produce the same result.

SELECT count(1) from archive.notes WHERE date = 2005-10-26

SELECT count(1) from archive.notes WHERE date = '2005-10-26'

Do they both produce different values? If so, that may be your problem.

If they both produce a value of zero, could it be that your date field holds more detail than date, does it also have the time in there?

Did you try this? What were the results?

obrienkev

Tried the select and worked same for both.

Just realised that there are no '' around $record.

Works now.

Thanks for the help guys!