Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP MySQL UPDATE not working, need to use REPLACE

Posted on 2013-10-22
10
Medium Priority
?
548 Views
Last Modified: 2013-10-24
Hi Experts

I have a small script that was working well, since updating the server it runs on I have hit an issue (I dont know if this is related or not)

I have the following code that updates a row in a MySQL Database:

$mastercategory=$_POST['mastercategory']; 
$category=$_POST['category']; 
$product_desc=$_POST['product_desc'];
$barcode=$_POST['barcode'];
$serial=$_POST['serial'];
$status=$_POST['status'];

// update data in mysql database 
$sql="UPDATE $tbl_name SET status='$status' WHERE barcode='$barcode'";
 $result=mysql_query($sql);

// if successfully updated. 
if($result){
 echo "Success";
 echo "<br />";
 echo "Database Checked OK";
  }

 else {
 echo "ERROR";
 }
?>

Open in new window


If there is already a value in the status field, then the row doesnt update, and the row also isnt updated with a new timestamp.

I think the following piece of code needs to be replaced with a REPLACE function but I am unsure how it would work:

$sql="UPDATE $tbl_name SET status='$status' WHERE barcode='$barcode'";
 $result=mysql_query($sql);

Open in new window


Your help is greatly appreciated
0
Comment
Question by:Jon C
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39591642
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);

Open in new window

0
 

Author Comment

by:Jon C
ID: 39591665
Hi

Thanks for your reply

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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39591681
Then you have to update the timestamp itself in your statement.

By the way, there is a REPLACE command but it does not do what you want.  http://dev.mysql.com/doc/refman/5.5/en/replace.html
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:Jon C
ID: 39591694
Thanks for your reply again

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.

Thanks again for your assistance
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39591719
??  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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39591820
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?
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39591966
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!

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
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 39591995
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 ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}

// IF WE GET HERE, THE UPDATE SUCCEEDED
echo "Success";
echo "<br />";
echo "Database Checked OK";

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39596930
Thanks for the points and thanks for using EE, ~Ray
0
 

Author Comment

by:Jon C
ID: 39596935
Thanks for your help and advice, it is greatly appreciated,  :)  Jon
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question