The issue is if status field it already has the value "CheckedOK", then it doesnt update the timestamp ...
That is expected behavior. The timestamp is only updated if data was actually changed in the row.
Do you expect the barcode to be UNIQUE? If so, adding LIMIT 1 to the UPDATE query would be a good idea. It would also be a good idea to sanitize and escape the external inputs before using them in a query!
There is no REPLACE function in MySQL and there is nothing wrong with the code you have posted. However, it is not complete. $tbl_name is not defined in the code you have posted. What I would do is 'echo' the SQL string right after you create it to make I was getting what I thought I should.
$sql="UPDATE $tbl_name SET status='$status' WHERE barcode='$barcode'";echo $sql;$result=mysql_query($sql);
I have added the code you suggest and it gives the following which is correct:
UPDATE main_stock SET status='CheckedOK' WHERE barcode='039718'
The issue is if status field it already has the value "CheckedOK", then it doesnt update the timestamp on that row, if the status field is empty or has a different value to "CheckedOK" then it updates as it should.
So I suppose the question is how to I get it to update the field status with "CheckedOK" even if that field already has that value, so that the timestamp field gets updated ??
Sorry also tbl_name is definied within the connection to the db as follows:
$tbl_name="main_stock"; // Table name
Thanks for your assistance
0
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
So if I get it to update the timestamp as you suggest, which sounds logical, is there a way to also get it to add "CheckedOK" to the status field if it doesnt exist, but to ignore it if it does exist so it wont stop the update from working ??
I need the field "status" to be changed to "CheckedOK" if it doesnt have that value.
?? Just update the timestamp in the same UPDATE statement. I don't know how you are creating your timestamp but the update should probably be the same.
First, the timestamp column will not be updated by default unless you define a trigger on the table. Any column you need to modify has to be part of the UPDATE... SET column list.
Second, if the value of status is the same, why do you want to change the timestamp value?
Here are the essential moving parts (but still using the deprecated MySQL extension). The article will show you how to map this code to the newer MySQLi or PDO extensions. I have found the easiest conversion from MySQL is to use the MySQLi Object-Oriented notation.
// ASSUMING THAT $tbl_name GETS DEFINED SOMEWHERE/* THESE LINES ARE NOT USED$mastercategory=$_POST['mastercategory']; $category=$_POST['category']; $product_desc=$_POST['product_desc'];$serial=$_POST['serial'];*/// ESCAPE DATA FOR SAFE USE IN A QUERY$barcode = mysql_real_escape_string($_POST['barcode']);$status = mysql_real_escape_string($_POST['status']);// BUILD THE QUERY STRING AND RUN THE QUERY$sql="UPDATE $tbl_name SET status='$status' WHERE barcode='$barcode' LIMIT 1";$res = mysql_query($sql);// TEST FOR SUCCESS, LOG AND SHOW ANY ERRORif (!$res){ $err = "QUERY FAIL: " . $sql . ' ERRNO: ' . mysql_errno() . ' ERROR: ' . mysql_error() ; trigger_error($err, E_USER_ERROR);}// IF WE GET HERE, THE UPDATE SUCCEEDEDecho "Success";echo "<br />";echo "Database Checked OK";
Do you expect the barcode to be UNIQUE? If so, adding LIMIT 1 to the UPDATE query would be a good idea. It would also be a good idea to sanitize and escape the external inputs before using them in a query!
The update query you posted appears to be OK. What is not OK is the use of the MySQL extension. PHP is doing away with MySQL. This article explains why and what you must do to keep your scripts running.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html