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
– Md. Sahadat Hossain, Commented Sep 19, 2013 at 12:20
oh i tried that but its not working!!but thanks for the help! — Pooja
– Pooja, Commented Sep 19, 2013 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. bobby-tables.com/php has examples to get you started, and this question has many examples in detail. — Andy Lester
– Andy Lester, Commented Sep 19, 2013 at 12:25

asarfraz · Accepted Answer · 2013-09-19 12:24:23Z

4

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

answered Sep 19, 2013 at 12:24

asarfraz

5183 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GautamD31 · Accepted Answer · 2013-09-19 12:25:33Z

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, 2013 at 12:25

answered Sep 19, 2013 at 12:18

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

2 Comments

Pooja Over a year ago

right now i dont want to use mysqli & pdo_mysql i need some simple solution in mysql only!! thanks for the help!!

GautamD31 Over a year ago

I just suggested only....consider that I have given solution in mysql only @Pooja

Community · Accepted Answer · 2017-05-23 12:28:03Z

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, 2017 at 12:28

CommunityBot

11 silver badge

answered Sep 19, 2013 at 12:27

Zeeshan

1,68314 silver badges18 bronze badges

Comments

TheCarver · Accepted Answer · 2013-09-19 12:35:35Z

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, 2013 at 12:35

answered Sep 19, 2013 at 12:29

TheCarver

19.8k27 gold badges106 silver badges154 bronze badges

Collectives™ on Stack Overflow

SQL, PHP update query

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related