/    Sign up×
Community /Pin to ProfileBookmark

How To Confirm Data Dump Is Success in Mysql Using Prepared Statement ?

Hello Again,

Have 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()

It would really do me a great favour if you could answer 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) ?

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 = ‘[email protected]’; //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 = ‘[email protected]’; //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 = ‘[email protected]’; //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 = ‘[email protected]’; //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.
Looking above, is there any that is actually unnecessarily going through the IF condition ? (Is pointless). If so, then why ?

Thank you!

to post a comment
PHP

4 Comments(s)

Copy linkTweet thisAlerts:
@NogDogFeb 06.2023 — Same answer as in https://forum.webdeveloper.com/d/402887-how-to-check-for-mysql-row-updates-using-prepared-statements/3

Can I close this as essentially a duplicate post?
Copy linkTweet thisAlerts:
@novice2022authorFeb 06.2023 — @NogDog#1650446

Do not close it as duplicate.

Rather add your code from the other thread onto one of my code here, which you deem is worthy to add to, and then close the thread as ISSUE RESOLVED.

Thanks!
Copy linkTweet thisAlerts:
@novice2022authorFeb 06.2023 — @NogDog

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 = '[email protected]'; //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);
Copy linkTweet thisAlerts:
@novice2022authorFeb 10.2023 — @NogDog

I am told D) is wrong and C) is better. What is your opinion ?

Btw, I never understand these try-catchall code lines. Shall I bug you about the lines I do not understand ?

Better, any workarounds so I do not need to learn the try-catchall stuff ? Seems like the codes lines are in oop. I do not know oop.
×

Success!

Help @novice2022 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 3.29,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...