mysqli to prepared statement

Question

Am trying to convert mysqli to prepare statement. Have being making alot of progress with most of them,but is unusual. I hope some can help with it.

here is my mysqli code

 $UpdateQuery = "UPDATE user SET avatar ='$NewImageName' WHERE user_name = '$temp'"; $InsertQuery = "INSERT INTO user (avatar) VALUES ('$NewImageName')"; $result = mysqli_query($con, "SELECT * FROM user WHERE user_name = '$temp'"); if( mysqli_num_rows($result) > 0) { if(!empty($_FILES['ImageFile']['name'])){ mysqli_query($con, $UpdateQuery)or die(mysqli_error($con)); header("location:edit-profile.php?user_name=$temp"); } } else { mysqli_query($con, $InsertQuery)or die(mysqli_error($con)); header("location:edit-profile.php?user_name=$temp"); }

These is my attempt to try and fix it with prepared statement

 if(!($stmtUpdate = $con->prepare("UPDATE user SET avatar = ? WHERE user_name = ?"))) { echo "Prepare failed: (" . $con->errno . ")" . $con->error; } if(!($stmtInsert = $con->prepare("INSERT INTO user ( avatar ) VALUES ( ? )"))) { echo "Prepare failed: (" . $con->errno . ")" . $con->error; } if(!($stmtSelect = $con->prepare("SELECT * FROM user WHERE user_name = ? "))) { echo "Prepare failed: (" . $con->errno . ")" . $con->error; } if(!$stmt->bind_param('sss', $temp, $NewImageName, $temp)) { echo "Binding paramaters failed:(" . $stmt->errno . ")" . $stmt->error; } if(!$stmt->execute()){ echo "Execute failed: (" . $stmt->errno .")" . $stmt->error; } $stmt->store_result(); if($stmt->num_rows == 0) { if(!empty($_FILES['ImageFile']['name'])){ $con->prepare($stmtUpdate)or die(mysqli_error($con)); header("location:edit-profile.php?user_name=$temp"); exit; } } else { $stmt->bind_result($avatar, $avatar, $temp); $stmt->fetch(); header("location:edit-profile.php?user_name=$temp"); } $stmt->close();

I Although i run it once and i get error, i know am most be missing some thing.

here is the error am getting Fatal error: Call to a member function bind_param() on a non-object in ****/edit-profile.php on line 67 — Olamilekan Oshodi
– Olamilekan Oshodi, Commented Oct 3, 2016 at 9:08
Thanks for your help... but that doesn't answer my question much. i have 3 query there Update, Select and Insert — Olamilekan Oshodi
– Olamilekan Oshodi, Commented Oct 3, 2016 at 9:20
the first thing i need to know is, is my prepared statement code right? — Olamilekan Oshodi
– Olamilekan Oshodi, Commented Oct 3, 2016 at 9:20

Rajdeep Paul · Accepted Answer · 2016-10-03 20:06:21Z

Your attempt to change those non-prepared statements to prepared statements is wrong. Few issues are:

There's no need to create three separate statement objects for SELECT, UPDATE and INSERT, only one statement object is enough. Having said that, always close the prepared statement using using it again for your query.
if(!$stmt->bind_param(..., if(!$stmt->execute() etc. $stmt is not a statement object, you never even created or used this variable anywhere. And that's why you're getting this Fatal error: Call to a member function bind_param() on a non-object ... error.
Looking at your non-prepared code above, there's no need to use ->bind_result() or ->fetch() method, simply perform INSERT or UPDATE operation and redirect the user to a different page.

Your prepared code should be like this: (The underlying logic strictly resembles your non-prepared code)

if(!($stmt = $con->prepare("SELECT * FROM user WHERE user_name = ?"))){ die("Prepare failed: (" . $con->errno . ") " . $con->error); } if(!$stmt->bind_param('s', $temp)){ die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error); } if($stmt->execute()){ $stmt->store_result(); $num_rows = $stmt->num_rows; $stmt->close(); if($num_rows){ if(!empty($_FILES['ImageFile']['name'])){ if(!($stmt = $con->prepare("UPDATE user SET avatar = ? WHERE user_name = ?"))){ die("Prepare failed: (" . $con->errno . ") " . $con->error); } if(!$stmt->bind_param('ss', $NewImageName, $temp)){ die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error); } if($stmt->execute()){ $stmt->close(); header("location:edit-profile.php?user_name=" . $temp); exit(); }else{ die("Execute failed: (" . $stmt->errno . ") " . $stmt->error); } } }else{ if(!($stmt = $con->prepare("INSERT INTO user (avatar) VALUES (?)"))){ die("Prepare failed: (" . $con->errno . ") " . $con->error); } if(!$stmt->bind_param('s', $NewImageName)){ die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error); } if($stmt->execute()){ $stmt->close(); header("location:edit-profile.php?user_name=" . $temp); exit(); }else{ die("Execute failed: (" . $stmt->errno . ") " . $stmt->error); } } }else{ die("Execute failed: (" . $stmt->errno . ") " . $stmt->error); }

as always, thanks for having a look at the code. Have used it he works like a charm. I will never have taught to seperate it this way, but now have learn, also closing each statement after each query have learn as well
Thanks so much... am using most of your previous method in my other codes.
@OlamilekanOshodi Glad I could help. Please accept the answer if it resolved your issue. :-)

Collectives™ on Stack Overflow

mysqli to prepared statement

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related