Using prepared statement with update set, immediately following a while loop in php

Question

When I try to change the code from the first block to second block, I get an error message saying it returned false. This is always how I use prepared statements for all the other methods (delete, insert, like). I believe the while loop is causing the issue.

This code below code works (but probably not SQLI injection proof):

$id=$_GET['edit'];    
$result = mysqli_query($con, "SELECT * FROM employees WHERE id=$id");

This code below gets an error message basically saying that it returned false:

$result = $con->prepare("SELECT * FROM employees WHERE id=?");
$result->bind_param('i', $_GET['edit']);
$result->execute();

while($row = mysqli_fetch_assoc($result)) {
    $id= $row['id'];
    $first_name = $row['first_name'];
    $last_name = $row['last_name'];
    $position = $row['position'];

This code below I used for a search bar and it works 100%. However, for update it gets the following error message:

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::fetch_array():

while ($row = $result->fetch_array(MYSQLI_NUM))

so what's the *real* error here? Edit: @u_mulder the OP deleted. I know you can see this message *lol!* — Funk Forty Niner, Mar 06 '18 at 13:40
Please, read a manual. It will tell you difference between direct query and prepared. — u_mulder, Mar 06 '18 at 13:40
@FunkFortyNiner real error is the `$result` is not `mysqli_stmt` ( — u_mulder, Mar 06 '18 at 13:41
while ($row = $result->fetch_array(MYSQLI_NUM)) is what I used for a search. Did not use mysqli_stmt but will look into it. — user9449525, Mar 06 '18 at 13:49
Yeah. fetch_array is what I used for my search bar which is working. I bet if I use the foreach loop after it will work. — user9449525, Mar 06 '18 at 13:53

score 0 · Answer 1 · answered Mar 14 '18 at 19:14

This is the correct answer:

if($stmt = $con->prepare("SELECT * FROM employees WHERE id=?")){
    $id=$_GET['edit']; //the ID is in the url
    $stmt->bind_param('i',$id);
    $stmt->execute();
    $result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
$id= $row['id'];
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$position = $row['position'];
?>

Also, after reading Example of how to use bind_result vs get_result, I concluded that using get_result is the easiest method. But that is up to the person/employer.

If this question was 'so easy', why did I get a -2? I ended up having to answer my own question after many hours of serious study. Would like a moderator or someone to explain to me how I can get 'better postings' in the future. Thanks.

score -1 · Answer 2 · answered Mar 06 '18 at 14:20

Try this,

$con    = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$result = $con->prepare("SELECT * FROM employees WHERE id=?");
$qryres = $result->execute('i', $_GET['edit']); //$result->bind_param('i', $_GET['edit']);
$row    = ($qryres) ? $result->fetchAll(PDO::FETCH_ASSOC) : array(); 
$con    = null;
//You can then loop over the row

Using prepared statement with update set, immediately following a while loop in php

2 Answers2