SQL, PHP update query

Question

I was wondering if there is any way to check if the update query has executed successfully or else I will execute insert query.

$record = mysql_query("update table set a='$b', b='$c' where id = '$id' "); 
echo $record; // returns always 1;

Thank You.

You Should keep php code outside of quotes. Try this `$record = $mysql_query("update table set a='".$b."', b='".$c."' where id = '".$id".' ");` — Md. Sahadat Hossain, Sep 19 '13 at 12:20
oh i tried that but its not working!!but thanks for the help! — Pooja, Sep 19 '13 at 12:21
Using prepared statements would solve your problem. **By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Sep 19 '13 at 12:25

score 4 · Accepted Answer · answered Sep 19 '13 at 12:24

Use the mysql_affected_rows function to find the number of records affected. Please see following code.

 $record = mysql_query("update table set a='$b', b='$c' where id = '$id' "); 
 $total_rows = mysql_affected_rows($conn);
 echo $total_rows;

where $conn is the connection object

GautamD31 · Answer 2 · 2013-09-19T12:25:33.943

2

It will be like

$records = mysql_query("UPDATE `table` SET a='$b', b='$c' WHERE id = '$id' "); 
$affected_rows = mysql_affected_rows($records);
if($affected_rows >= 1) {
    echo "Updated ".$affected_rows." rows";
}

Need to remove $ before mysql_query.And consider that table is your table name and also makesure that your connection to the DB is active.

And Try to avoid mysql_* statements due to the entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_*, is officially deprecated as of PHP v5.5.0 and will be removed in the future.

There are two other MySQL extensions that you can better Use: MySQLi and PDO_MySQL, either of which can be used instead of ext/mysql.

edited Sep 19 '13 at 12:25

answered Sep 19 '13 at 12:18

GautamD31

28,552
10
64
85

1

right now i dont want to use mysqli & pdo_mysql i need some simple solution in mysql only!! thanks for the help!! – Pooja Sep 19 '13 at 12:24
I just suggested only....consider that I have given solution in `mysql` only @Pooja – GautamD31 Sep 19 '13 at 12:24

score 1 · Answer 3 · edited May 23 '17 at 12:28

1

Try this ... It returns the number of modified rows in the last query run.

$Rows = mysql_affected_rows($record).

example $Rows = 1, 2, 3 etc......

mysql_query(): how to check whether any rows are updated in case of UPDATE SQL

edited May 23 '17 at 12:28

Community

1
1

answered Sep 19 '13 at 12:27

Zeeshan

1,659
13
17

TheCarver · Answer 4 · 2013-09-19T12:35:35.150

1

mysql_affected_rows() is what you need.

Docs: http://php.net/manual/en/function.mysql-affected-rows.php

mysql_query("UPDATE mytable SET used=1 WHERE id < 10");
if (mysql_affected_rows() > 0) {
    echo 'You updated '.mysql_affected_rows(). 'rows.';
} else {
    echo 'Nothing updated';
}

edited Sep 19 '13 at 12:35

answered Sep 19 '13 at 12:29

TheCarver

19,391
25
99
149

SQL, PHP update query

4 Answers4