How To Confirm Row Insertion Is Success in Mysql Using Prepared Statement ?

Question

borobhaisab 117 Posting Whiz

3 Years Ago

Howdy once more,
Just got another question on Sql and Php Prepared Statement.
How would you confirm a successful row insertion to a mysql table ?
Which one of the following lines would you add the IF condition to in order to check insertion successful or not ?

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 based on php's proper way of coding (best practice) ?
Looking below, is there any that is actually unnecessarily going through the IF condition ? (Is pointless). If so, then why ?

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.

$sql = "INSERT into users (username,email) VALUES (?,?)";

$stmt = mysqli_prepare($conn,$sql);
mysqli_stmt_bind_param($stmt,"ss",$input_1,$input_2);
mysqli_stmt_execute($stmt);
echo 'INSERTED 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.

$sql = "INSERT into users (username,email) VALUES (?,?)";

if($stmt = mysqli_prepare($conn,$sql))
{
    mysqli_stmt_bind_param($stmt,"ss",$input_1,$input_2);
    mysqli_stmt_execute($stmt);
    echo 'INSERTED 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.

$sql = "INSERT into users (username,email) VALUES (?,?)";

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

    if(mysqli_stmt_execute($stmt))
    {
        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 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.

$sql = "INSERT into users (username,email) VALUES (?,?)";

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

    if(mysqli_stmt_affected_rows($stmt))
    {
        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 INSERT!');
    }
}
else
{
    echo 'Mysqli Error: ' .mysqli_error();
    echo '<br>';
    echo 'Mysqli Error No: ' .mysqli_errno();
}

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

I experimented with all my example codes and they all work so far.

Cheers!

mysql php

Edited 3 Years Ago by borobhaisab

2 Contributors
5 Replies
131 Views
4 Days Discussion Span
Latest Post 3 Years Ago Latest Post by borobhaisab

Dani AI

Generated 9 Months Ago

Quick summary: use mysqli_stmt_execute() (or its OO equivalent) to detect SQL-level failure, and use affected_rows or insert_id to confirm that a row was actually created. is right that execute() returns false on failure, but an execute() that succeeds can still leave affected_rows at 0 (for example with INSERT IGNORE or certain duplicate-key semantics). Checking > 0 is the correct test to confirm an insert actually changed the table.

A robust pattern is to enable mysqli exceptions, use a try/catch, and then test affected_rows (or insert_id when you rely on an auto-increment PK). This keeps error handling centralized and avoids fragile nested if chains.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('host','user','pass','db');

try {
    $stmt = $mysqli->prepare('INSERT INTO users (username,email) VALUES (?,?)');
    $stmt->bind_param('ss', $username, $email);
    $stmt->execute();

    if ($stmt->affected_rows > 0) {
        echo 'Inserted, id: ' . $mysqli->insert_id;
    } else {
        // Succeeded but 0 rows affected: possible duplicate/IGNORE/no-op update
    }
} catch (mysqli_sql_exception $ex) {
    error_log($ex->getMessage());
    // handle/report
} finally {
    $stmt->close();
    $mysqli->close();
}

Practical notes and pitfalls seen in the thread: your later change to test > 0 is an improvement (truthy checks can misreport because -1 or other non-zero values can be returned in some cases). If using procedural functions, call mysqli_error($conn) / mysqli_errno($conn) (they require the connection parameter) — calling them without the link is incorrect. To distinguish a duplicate-key failure programmatically, inspect the exception/error code (MySQL duplicate-entry is 1062 / SQLSTATE 23000). See the PHP docs for mysqli_stmt_affected_rows, mysqli_report, mysqli_insert_id, and mysqli_sql_exception for the exact behavior and return values.

All 5 Replies

Dani 5,664 The Queen of DaniWeb

3 Years Ago

According to PHP documentation the mysqli_stmt_execute() function returns false on failure.

Personally, I would just do something such as:

if (mysqli_stmt_execute($stmt) !== false)
{
    // Success
    ...
}
else
{
    // Failure
    ...
}

pritaeas commented: Agreed, but in the success part, depending on your business logic, I would still check affected rows +17

Dani 5,664 The Queen of DaniWeb

3 Years Ago

I just want to add that I don't see anything glaringly wrong with what you're currently doing. However, note that there are MySQL insert statements that won't throw an error but will not alter any rows, such as if you're using INSERT IGNORE (which silently discards a failed attempt due to an existing row in the table with a duplicate key, without throwing an error).

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.

Agreed, but in the success part, depending on your business logic, I would still check affected rows

borobhaisab 117 Posting Whiz · Answer 1 · 2023-02-06T18:24:06+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.**


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

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

$sql = "INSERT into users (username,email) VALUES (?,?)";

if($stmt = mysqli_prepare($conn,$sql))
{
    mysqli_stmt_bind_param($stmt,"ss",$input_1,$input_2);
    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 INSERT!');
    }
}
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 2 · 2023-02-10T18:47:07+00:00

never came agross INSERT IGNORE and so won't comment on it now as I won't know what I am talking about fully unless I check it up first.
As for me not willing to stick to:

if (mysqli_stmt_execute($stmt) !== false)

is because the execution might go through but like you said if the query is searching for an id that does not exist then no rows would be updated and that execute function will show update was made since execution went through.

borobhaisab 117 Posting Whiz · Answer 3 · 2023-02-10T18:49:30+00:00

Folks,

Which of you have read the followings ?
I read about 3-4 yrs ago and forgotten what I learnt and so re-reading & re-learning:

Mysqli Error Reporting
https://phpdelusions.net/mysqli/error_reporting

Basic Error Reporting
https://phpdelusions.net/articles/error_reporting

How To Confirm Row Insertion Is Success in Mysql Using Prepared Statement ?

Dani AI

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers