find number of updates done in PHP

Question

What I am doing is editing the data of MySQL data using PHP using form.

I have two text field as name and mobile number. When I click on edit, I get same data in text field and below I have Save button. When I do changes I get response as done, but when I click edit and don't do any changes in text field and click Save I get response as fail.

Below is code for SAVE button.

$sql = mysql_query("
    UPDATE userInfo SET fullName='$fullName', 
    mobileNumber='$mobileNumber' 
    WHERE id=$tagNumberId"
);

if (mysql_affected_rows()==1) {
    echo "done";
} else {
    echo "fail";
}

I am worried about mysql_affected_rows().

Above makes me think that if data is same in UPDATE statement, mysql_affected_rows() will return 0 and if data is not same in UPDATE statement, mysql_affected_rows() will return 1.

Is this right? If it is right, how to deal with whether the update is done or not?

Well, **what are you trying to detect?** What is the "fail" branch supposed to mean? Also: the `mysql` extension is deprecated (you should switch to `PDO` or `mysqli` ASAP) and the code might be vulnerable to SQL inject (you should prefer prepared statements instead of injecting variables). — Jon, Feb 21 '13 at 09:44
@Jon : if I don't change any values in textfield and click save, output of `mysql_affected_rows()` comes as 0 (and I was expecting as 0) — Fahim Parkar, Feb 21 '13 at 09:46
Please use PDO and read about SQL Injection!!! In terms of update and your "fail", MySQL is smart enough to detect that your query will not change anything in DB so affected rows = 0 — demsey, Feb 21 '13 at 09:47
@All : I am using PDO but for simplification here, I'm using normal update statement.. — Fahim Parkar, Feb 21 '13 at 09:49
@FahimParkar: OK, it comes as 0. That still doesn't answer my question: what is the "fail" branch supposed to mean? — Jon, Feb 21 '13 at 09:52
@Jon : When fail will come, I want to tell user that user is not edited. Please try again. FAIL means, user is not edited. — Fahim Parkar, Feb 21 '13 at 09:57
@FahimParkar: There are three reasons why no records might be updated: a) the fields were unchanged, b) the `id` does not exist, c) some problem with the server or connection. "Please try again" will only work for (c), which you can detect reliably with `if($sql === false)`. So why are you calling `mysql_affected_rows` in the first place? — Jon, Feb 21 '13 at 10:01
@Jon : this is happening because `a) the fields were unchanged`. Even the fields are not changed, how should I say that edit is done? Also when I do echo $sql, I get response as 1 (always), so I believe false will not be right way to compare... — Fahim Parkar, Feb 21 '13 at 10:04
@FahimParkar: If you had asked "how can I detect that the user has not changed the input fields so I can display an error" we would have gotten here much sooner. And of course the answer is: fetch them from the database and compare them with the values you want to use for the update. — Jon, Feb 21 '13 at 10:06
@Jon : What I was trying to do is when updated_rows==1 (because at one time I can only 1 person data in text field), I will say user data is updated — Fahim Parkar, Feb 21 '13 at 10:06
@Jon : That I understand. But usually I have seen that after clicking edit, if I don't even change data and click save, i get response as data is saved... — Fahim Parkar, Feb 21 '13 at 10:07
@FahimParkar - you say "I am using PDO but..". Not sure what you're saying here; the PDO code to do what you're showing wouldn't be any more complex than the `mysql_query()` code. — SDC, Feb 21 '13 at 10:21

score 3 · Accepted Answer · answered Feb 21 '13 at 10:47

Use mysqli_info, just after executing the query

$string = mysqli_info ( $link );

returns a string having the relevant information, like for UPDATE

Rows matched: 40 Changed: 40 Warnings: 0

in your case, one row updated but no change, you should get

Rows matched: 1 Changed: 0 Warnings: 0

You can retrieve the value from

preg_match("/Rows matched: (\d+)/", $string, $matches);
$number_of_rows = intval($matches[1]);

sectus · Answer 2 · 2013-02-21T10:16:12.100

It's right mysql behavior of UPDATE. But you can use some wiered trick if you really want this.

$sql = mysqli_query("UPDATE userInfo SET id=@id:=id
                    fullName='$fullName',
                    mobileNumber='$mobileNumber'
                    WHERE id=$tagNumberId");
$result = mysqli_query($link, 'select @id');
if (array(null)!==mysqli_fetch_row($result)) {
    echo "done";
} else {
    echo "fail";
}

P.S. You MUST use prepared statements for yours applications.

P.P.S. with this method you can't recognize count, but there is no need if you use update on id.

find number of updates done in PHP

2 Answers2