Fallenreaper Fallenreaper - 6 months ago 13
SQL Question

running mysqli command with binding nets a error fet_row is non-object

I was writing the following code:

$conn = new mysqli($server,$user,$pw,$db) or die ('Could not connect to DB'.mysqli_error());

$id = 12345;

$sql = 'select * from invTypes where typeId = ? and published = 1';
$stmt = $conn->prepare($sql);
$stmt->bind_param("i",$id);
$stmt->execute();
$res = $stmt->get_result();

$row = $res->fetch_assoc();

//process $row's data.


When running the query inside of mysql with an int instead of a question mark, it works. It seems that I am doing something wrong with the way I bind, and then fetch.

I was flipping through docs and it seems Im a bit confused as to how i should go forward with this error.

Edit: I read this in the docs.

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();


and applied it to above. Still. the error is: Fatal error: Call to undefined method mysqli_stmt::get_result()

Answer

The problem is because of this line,

$res = $mysqli_stmt_fetch($stmt);

Few errors are:

  • There's an incorrect dollar($) sign before mysqli_stmt_fetch() function.
  • mysqli_stmt_fetch() function actaully fetch results from a prepared statement into the bound variables, and you're not using any ->bind_result() method in your code.

Here's the reference:

So the solution is, since you're using SELECT * rather than SELECT column1, column2, it's better that you use ->get_result() method to get the result set.

Here's the reference:

So your code should be like this:

// your code

$sql = 'select * from invTypes where typeId = ? and published = 1';
$stmt = $conn->prepare($sql);
$stmt->bind_param("i",$id);
if($stmt->execute()){
    $res = $stmt->get_result();
    $row = $res->fetch_assoc(); 
}

Suggestion: Don't mix up the procedural and object oriented style of mysqli.