how to error check mysql update statment using php?

Question

i am updating mysql row using the following code. could any one tell me how i can error check the update query and only print Success if the update query was successful without any error? and print failed if update query was not successful!

<?
    $IdVar = $_POST['rowId'];
    $DataVar = $_POST['NewData'];



    $server   = "localhost"; // MySQL hostname
    $username = "xxxxxx"; // MySQL username
    $password = "xxxxxx"; // MySQL password
    $dbname   = "test"; // MySQL db name

    $db = mysql_connect($server, $username, $password) or die(mysql_error());
          mysql_select_db($dbname) or die(mysql_error());


      $result = mysql_query("update testDb SET username ='$DataVar' WHERE ID = '$IdVar'");
     if (!$result) {
                 die('Invalid query: ' . mysql_error());
                }

    echo "Success";

    ?>

Define 'not successful'. Are you looking for errors, or just that the update query didn't find anything to update? — , Nov 23 '13 at 18:13
Maybe you're looking for mysql_affected_rows(), see: http://us2.php.net/manual/en/function.mysql-affected-rows.php — Dor, Nov 23 '13 at 18:14
Please don't use `mysql_*` functions anymore, they are deprecated. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for details. Instead you should learn about [prepared statements](http://bobby-tables.com/php.html) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you. If you pick PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — Marcel Korpel, Nov 23 '13 at 18:15
BTW, you're vulnerable to [SQL injection](https://www.owasp.org/index.php/SQL_Injection). Use [prepared statements](http://bobby-tables.com/php.html) to prevent this. — Marcel Korpel, Nov 23 '13 at 18:15
thanks all for reply. i just want to echo success if row is updated but not sure if i placed the echo "Success"; in the right place! — user1788736, Nov 23 '13 at 18:18
No, you shouldn't "just want to echo success", you should really learn a few things about database security or your site will be hacked one day! — Marcel Korpel, Nov 23 '13 at 18:26

score 1 · Answer 1 · answered Nov 23 '13 at 18:15

1

Use mysql_affected_rows to count the number of affected rows. If it returns 0, no row was updated.

answered Nov 23 '13 at 18:15

Lorenz Meyer

19,166
22
75
121

score 1 · Answer 2 · 2013-11-23T19:05:41.103

1

mysql_affected_rows count the number of affected rows on success, and -1 if the last query failed.

echo ( mysql_affected_rows() > 0 ) ? "Success update" : "No rows update";

edited Nov 23 '13 at 19:05

answered Nov 23 '13 at 18:17

score 0 · Answer 3 · answered May 10 '16 at 14:53

mysql functions are deprecated, so you may want to use mysqli_ functions.

Using the deprecated mysql functions, one can use mysql_errno to check if an error occurred.

$result = mysql_query("update testDb SET username ='$DataVar' WHERE ID = '$IdVar'");

if(mysql_errno($db)!=0) echo "Something went wrong";

I would not check mysql_affected_rows() as suggested by the other answers, because mysql_affected_rows()=0 only implies that no row was updated, but it does not mean that an error occurred.

For example, if you have username="Rolf" with id=3, then if you execute

$result = mysql_query("update testDb SET username ='Rolf' WHERE ID = '3'")

no rows are affected, even though there was no error during the update.

how to error check mysql update statment using php?

3 Answers3