How To Confirm Sql Row Update Using Prepared Statements?

Question

borobhaisab 117 Posting Whiz

1 Year Ago

Hello,

Got questions on Sql and Php Prepared Statements.
How would you check whether the SQL managed to update a row or not ? Which one of the following lines would you add the IF condition to ?

1).

mysqli_stmt_execute()

2).

mysqli_stmt_affected_rows()

Which of the following examples A-D are a good way of coding and why ?
And, which are a bad way of coding and why ?

Here are my notes.
A).

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'magi'; //username.
$input_2 = 'admin@magi.com'; //email.
$input_3 = '0'; //id.

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

$stmt = mysqli_prepare($conn,$sql);
mysqli_stmt_bind_param($stmt,"sss",$input_1,$input_2,$input_3);
mysqli_stmt_execute($stmt);
echo 'UPDATED SUCESSFULLY: ' .mysqli_stmt_affected_rows($stmt);

mysqli_stmt_close($stmt);
mysqli_close($conn);

B).

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'magi'; //username.
$input_2 = 'admin@magi.com'; //email.
$input_3 = '0'; //id.

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if($stmt = mysqli_prepare($conn,$sql))
{
    mysqli_stmt_bind_param($stmt,"sss",$input_1,$input_2,$input_3);
    mysqli_stmt_execute($stmt);
    echo 'UPDATED SUCESSFULLY: ' .mysqli_stmt_affected_rows($stmt);
}
else
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

mysqli_stmt_close($stmt);
mysqli_close($conn);

C).

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'magi'; //username.
$input_2 = 'admin@magi.com'; //email.
$input_3 = '0'; //id.

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if($stmt = mysqli_prepare($conn,$sql))
{
    mysqli_stmt_bind_param($stmt,"sss",$input_1,$input_2,$input_3);

    if(mysqli_stmt_execute($stmt))
    {
        echo 'UPDATED SUCESSFULLY: ' .mysqli_stmt_affected_rows($stmt);
    }
    else
    {
        echo 'Mysqli Error: ' .mysqli_error();
        echo '<br>';
        echo 'Mysqli Error No: ' .mysqli_errno();
        echo '<br>';
        die('Failed to INSERT!');
    }
}
else
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

mysqli_stmt_close($stmt);
mysqli_close($conn);

D).

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'magi'; //username.
$input_2 = 'admin@magi.com'; //email.
$input_3 = '0'; //id.

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if($stmt = mysqli_prepare($conn,$sql))
{
    mysqli_stmt_bind_param($stmt,"sss",$input_1,$input_2,$input_3);
    mysqli_stmt_execute($stmt);

    if(mysqli_stmt_affected_rows($stmt))
    {
        echo 'UPDATED SUCESSFULLY: ' .mysqli_stmt_affected_rows($stmt);
    }
    else
    {
        echo 'Mysqli Error: ' .mysqli_error();
        echo '<br>';
        echo 'Mysqli Error No: ' .mysqli_errno();
        echo '<br>';
        die('Failed to INSERT!');
    }
}
else
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

mysqli_stmt_close($stmt);
mysqli_close($conn);

Looking above, is there any that is unnecessarily going through the IF condition ? (Is pointless). If so, then why ?

Thank you for your helps!

php

Edited 1 Year Ago by borobhaisab

3 Contributors
25 Replies
205 Views
2 Months Discussion Span
Latest Post 1 Year Ago Latest Post by pritaeas

pritaeas 2,194 ¯\_(ツ)_/¯

1 Year Ago

You need to understand that even though mysqli_stmt_execute might return true, mysqli_stmt_affected_rows can return 0. Whether this is a failure or not, depends on your business logic. Suppose you want to update based on a unique ID, you could check whether mysqli_stmt_affected_rows equals 1. If it returns 0 the ID was not found, if it exceeds 1 then you have a database issue, e.g. the primary is not defined correctly.

Dani 4,329 The Queen of DaniWeb

1 Year Ago

I would select C.

A just assumes there are not going to be any issues connecting to the database or executing the statement, and there's no error handling if something goes wrong.

B assumes there are no issues with the MySQL query, such as if the users table doesn't exist, etc., and doesn't provide error handling for these types of situations.

C first does a sanity check on the MySQL connection, and then a second check that the query executed successfully.

D incorrectly assumes that there must be a MySQL error if 0 rows were affected.

Suppose there is a row in the users table where username is set to magi, email is set to admin@magi.com, and id is set to 0. Now assume we run your code. The UPDATE query will execute successfully, and not throw any errors, and yet no rows were affected because nothing was changed compared to what already existed in the database.

Dani 4,329 The Queen of DaniWeb

1 Year Ago

"if 0 rows got affected, it means update WAS successful!"

That is correct. Suppose there is a MySQL query UPDATE users SET username="Dani" where id=5 and now also imagine there is no existing database record with id 5. The UPDATE query executed successfully, and 0 rows were affected.

mysqli_stmt_affected_rows() returns the number of rows affected by a successful query. It's quite possible for UPDATE queries to affect 0 rows, just as it's quite possible for a SELECT query to return 0 rows that match the constrains of the query. If the query didn't execute due to an error (e.g. the query was invalid, etc.) then it will return -1 instead.

pritaeas 2,194 ¯\_(ツ)_/¯

1 Year Ago

Do you use pdo instead or prepared statements ?

This makes no sense, PDO supports prepared statements too. Personal preference: I use PDO because it supports named parameters, much more readable IMHO.

Dani 4,329 The Queen of DaniWeb

1 Year Ago

My library of course uses MySQLi in order to connect and query the database. What I did is rolled my own ORM, which is a technique used to map my PHP objects to rows in my database. That's why I can do something like the above, in which the push_to_db() function then uses PHP's mysqli library to push the appropriate SQL query to the database. In other words, really all that it does is write simple SQL queries for me.

borobhaisab commented: Very good! +4

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

borobhaisab 117 Posting Whiz · Answer 1 · 2023-02-06T15:30:30+00:00

@pritaeas

If it returns 0 the ID was not found, if it exceeds 1 then you have a database issue, e.g. the primary is not defined correctly.<<

Thanks.
But I defined my PRIMARY to id column.
Q1. So, I should really not check agains mysqli_stmt_execute() but mysqli_stmt_affected_rows() if PRIMARY was defined ?

Q2. If PRIMARY was not set then how would you check whether row was updated or not, in this case using following query ?

$country = 'usa';
$result = 'rejected';

$sql = "UPDATE users SET result=? WHERE country=?";
mysqli_stmt_bind_param($stmt,"ss",$result,$country);

I would still check whether rows got updated or not successfully like following but you let me know the proper way of checking using php prepared statement in procedural style.

if(mysqli_stmt_affected_rows($stmt))

borobhaisab 117 Posting Whiz · Answer 2 · 2023-02-06T17:50:30+00:00

@dani

If this following line shows: 0
Then, does not that mean no records got updated ?

echo 'UPDATED SUCESSFULLY: ' .mysqli_stmt_affected_rows($stmt);

And if it shows 1, then does not it means 1 row got updated ?
Look at my A) code above and kindly answer.

I ask because someone told me the following and confused me:
"if 0 rows got affected, it means update WAS successful!"

Thanks!

borobhaisab 117 Posting Whiz · Answer 3 · 2023-02-06T18:21:36+00:00

Folks,

I think all the codes shown above in my op are not perfect. Especially, C) I suspect is not right and D is downright wrong way of coding.
And so fixed it to the following. I need your opinion on it now.**

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'magi'; //username.
$input_2 = 'admin@magi.com'; //email.
$input_3 = '0';

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if($stmt = mysqli_prepare($conn,$sql))
{
    mysqli_stmt_bind_param($stmt,"sss",$input_1,$input_2,$input_3);
    mysqli_stmt_execute($stmt);

    if(mysqli_stmt_affected_rows($stmt)>0)
    {
        echo 'INSERTED SUCESSFULLY: ' .mysqli_stmt_affected_rows($stmt);
    }
    else
    {
        echo 'Mysqli Error: ' .mysqli_error();
        echo '<br>';
        echo 'Mysqli Error No: ' .mysqli_errno();
        echo '<br>';
        die('Failed to UPDATE!');
    }
}
else
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

mysqli_stmt_close($stmt);
mysqli_close($conn);

borobhaisab 117 Posting Whiz · Answer 4 · 2023-02-10T14:58:07+00:00

@Dani

Thank you for your reply. Sorry for late response as do not always manage to sit infront of pc everyday.
After reading your answers, a few questions have arisen in my mind. Kindly, clear my confusions.

First of all, let me see if I understood you correctly or not. After reading your answers, I drew-up the following.

Q1.
Glancing at it, can you see if I understood you ok or not ?

E).

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'mogambo'; //username.
$input_2 = 'admin@mogambo.com'; //email.
$input_3 = '0'; //id.

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if(!$stmt = mysqli_prepare($conn,$sql))
{
    echo 'Mysqli Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_stmt_errno();
}
else
{
    mysqli_stmt_bind_param($stmt,"sss",$input_1,$input_2,$input_3);

    if(!mysqli_stmt_execute($stmt))
    {
        echo 'No Row was UPDATED as SQL syntax Error!';
        echo 'Mysqli Statement Error: ' .mysqli_error();
        echo '<br>';
        echo 'Mysqli Statement Error No: ' .mysqli_errno();
    }

    if(mysqli_stmt_affected_rows($stmt)==-1) //This means only one thing: SQL syntax error.
    {
        echo 'No Row UPDATE as SQL syntax Error!';
        echo 'Mysqli Statement Error: ' .mysqli_error();
        echo '<br>';
        echo 'Mysqli Statement Error No: ' .mysqli_errno();
    }
    elseif(mysqli_stmt_affected_rows($stmt)==0)
    {
        echo 'No Row UPDATE as no Row MATCH!: ' .mysqli_stmt_affected_rows($stmt);
    }
    else
    {
        echo 'UPDATED SUCESSFULLY number of ROWS: ' .mysqli_stmt_affected_rows($stmt);
    }
}


mysqli_stmt_close($stmt);
mysqli_close($conn);

I used to wrongfully assume that if zero rows got affected then there must be error somewhere. Forgot to take into account that there could be no matches in the rows. Now that is clear.

borobhaisab 117 Posting Whiz · Answer 5 · 2023-02-10T15:02:27+00:00

@dani

Q2.
Is it true that on the above code E), I should never write both of these following IFs but either one ? If so, then which one I should stick to and why that one over the other and what is wrong with the other ? I mean, what weakness it would output if nothing really wrong with the code ?

1

if(!mysqli_stmt_execute($stmt))
{
    echo 'No Row was UPDATED as SQL syntax Error!';
    echo 'Mysqli Statement Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_errno();
}

2

if(mysqli_stmt_affected_rows($stmt)==-1) //This means only one thing: SQL syntax error.
{
    echo 'No Row UPDATE as SQL syntax Error!';
    echo 'Mysqli Statement Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_errno();
}

borobhaisab 117 Posting Whiz · Answer 6 · 2023-02-10T15:07:38+00:00

@dani

Q3A.
Regarding code E, on these following 2, which one's error lines are incorrect and why ?

1

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'mogambo'; //username.
$input_2 = 'admin@mogambo.com'; //email.
$input_3 = '0'; //id.

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if(!$stmt = mysqli_prepare($conn,$sql))
{
    echo 'Mysqli Stmt Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Stmt Error No: ' .mysqli_stmt_errno();
}

2

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'mogambo'; //username.
$input_2 = 'admin@mogambo.com'; //email.
$input_3 = '0'; //id.

$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if(!$stmt = mysqli_prepare($conn,$sql))
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

borobhaisab 117 Posting Whiz · Answer 7 · 2023-02-10T15:14:27+00:00

@dani

Q3B.
Regarding modifying code E on the error codes, on these following 2, which one's error lines are incorrect and why ?
Note code Q3A uses:

mysqli_prepare()

while this one uses:

mysqli_stmt_prepare()

1

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'mogambo'; //username.
$input_2 = 'admin@mogambo.com'; //email.
$input_3 = '0'; //id.

$stmt = mysqli_stmt_init($conn);
$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if(!mysqli_stmt_prepare($stmt,$sql))
{
    echo 'Mysqli Stmt Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Stmt Error No: ' .mysqli_stmt_errno();
}

2

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

$input_1 = 'mogambo'; //username.
$input_2 = 'admin@mogambo.com'; //email.
$input_3 = '0'; //id.

$stmt = mysqli_stmt_init($conn);
$sql = "UPDATE users SET username=?,email=? WHERE id=?";

if(!mysqli_stmt_prepare($stmt,$sql))
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

borobhaisab 117 Posting Whiz · Answer 8 · 2023-02-10T15:20:21+00:00

Q4A.

If I use:

mysqli_prepare()

then which of the following error code is incorrect and why:

1

if(!mysqli_stmt_execute($stmt))
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

2

if(!mysqli_stmt_execute($stmt))
{
    echo 'Mysqli Statement Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_stmt_errno();
}

borobhaisab 117 Posting Whiz · Answer 9 · 2023-02-10T15:24:50+00:00

Q4B.

If I use:

mysqli_stmt_prepare()

then which of the following error code is incorrect and why:

1

if(!mysqli_stmt_execute($stmt))
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

2

if(!mysqli_stmt_execute($stmt))
{
    echo 'Mysqli Statement Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_stmt_errno();
}

NOTE: Previous post's code used:

mysqli_prepare()

while this one is using:

mysqli_stmt_prepare()

borobhaisab 117 Posting Whiz · Answer 10 · 2023-02-10T15:28:37+00:00

@dani

Q5A

If I use:

mysqli_prepare()

then which of the following error code is incorrect and why:
1

if(mysqli_stmt_affected_rows($stmt)==-1)
{
    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

2

if(mysqli_stmt_affected_rows($stmt)==-1)
{
    echo 'Error!';
    echo 'Mysqli Statement Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_stmt_errno();
}

borobhaisab 117 Posting Whiz · Answer 11 · 2023-02-10T15:29:11+00:00

@dani

Q5B

If I use:

mysqli_stmt_prepare()

then which of the following error code is incorrect and why:
1

if(mysqli_stmt_affected_rows($stmt)==-1)
{
    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

2

if(mysqli_stmt_affected_rows($stmt)==-1)
{
    echo 'Error!';
    echo 'Mysqli Statement Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_stmt_errno();
}

borobhaisab 117 Posting Whiz · Answer 12 · 2023-02-10T15:31:12+00:00

@dani

Q6A

If I use:

mysqli_prepare()

then which of the following error code is incorrect and why:
1

    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_error($stmt);
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno($stmt);

2

    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();

3

    echo 'Error!';
    echo 'Mysqli Statement Error: ' .mysqli_stmt_error($stmt);
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_stmt_errno($stmt);

4

    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_stmt_errno();

borobhaisab 117 Posting Whiz · Answer 13 · 2023-02-10T15:37:08+00:00

@dani

Q6B

If I use:

mysqli_stmt_prepare()

then which of the following error code is incorrect and why:
1

    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_error($stmt);
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno($stmt);

2

    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();

3

    echo 'Error!';
    echo 'Mysqli Statement Error: ' .mysqli_stmt_error($stmt);
    echo '<br>';
    echo 'Mysqli Statement Error No: ' .mysqli_stmt_errno($stmt);

4

    echo 'Error!';
    echo 'Mysqli Error: ' .mysqli_stmt_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_stmt_errno();

NOTE: Previous post's code used:

mysqli_prepare()

while this one is using:

mysqli_stmt_prepare()

Dani 4,329 The Queen of DaniWeb Administrator Featured Poster Premium Member · Answer 14 · 2023-02-10T20:33:22+00:00

I'm sorry, I actually don't have any experience with prepared statements. I should be using them, but I'm not.

borobhaisab 117 Posting Whiz · Answer 15 · 2023-02-13T16:16:12+00:00

@dani

Do you use pdo instead or prepared statements ?

borobhaisab 117 Posting Whiz · Answer 16 · 2023-02-13T17:05:48+00:00

@dani

If you decide to learn prepared statements more then do read this famous tutorial recommended by lots of programmers:
https://phpdelusions.net/mysqli

Dani 4,329 The Queen of DaniWeb Administrator Featured Poster Premium Member · Answer 17 · 2023-02-14T07:34:55+00:00

I don’t use PDO either. Rather, I wrote my own ORM library for DaniWeb that let’s me do things such as:

// Build a new post
$post_obj = new Post(array(
    'message' => 'Hello World',
    'member_id' => 1,
));

// Publish it
$post_obj->push_to_db();

I’m not doing anything that is easing the MySQL bandwidth, however. I probably should be.

borobhaisab 117 Posting Whiz · Answer 18 · 2023-02-24T15:20:21+00:00

@dani

You created your own library to deal with mysql database ? Wow!
But why go that difficult route to reinvent the wheel when you got pdo, mysqli, etc ?

borobhaisab 117 Posting Whiz · Answer 19 · 2023-05-07T14:50:43+00:00

@pritaeas

Since I do not know pdo but mysqli. Then, care to answer my original post (op) by choosing one of the codes and letting me know why you favoured that one over the others ?

Thanks!

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 20 · 2023-05-08T07:03:20+00:00

Probably C, because it is the most complete (of your examples) in error-handling. It's the only one that checks the return value of mysqli_stmt_execute.