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

[borobhaisab]

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 = '[e-mail]'; //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 = '[e-mail]'; //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 = '[e-mail]'; //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 = '[e-mail]'; //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!

[borobhaisab]

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 = '[e-mail]'; //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);

[Dani]

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
    ...
}

[Dani]

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).

[borobhaisab]

@dani

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]

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
https://phpdelusions.net/mysqli

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