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

Question

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

is `bind()` a custom function? I doubt it. the syntax is `bind_param`. http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php — Funk Forty Niner, May 11 '16 at 00:39
Read it. I cant seem to figure out why `res` returns *Fatal error: Call to a member function fetch_assoc() on a non-object* — Fallenreaper, May 11 '16 at 00:44
`$mysqli_stmt_fetch($stmt)` This is variable not a function and it's not object oriented at all — Chay22, May 11 '16 at 00:44
`get_result()` may not be available for you to use. You'll need to loop over the results or bind them. *"MySQL Native Driver Only"* http://php.net/manual/en/mysqli-stmt.get-result.php *"Available only with mysqlnd."* — Funk Forty Niner, May 11 '16 at 00:55

Rajdeep Paul · Accepted Answer · 2016-05-11T01:03:49.653

1

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:

mysqli_stmt::fetch

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:

mysqli_stmt::get_result

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.

edited May 11 '16 at 01:03

answered May 11 '16 at 00:53

Rajdeep Paul

16,801
3
16
34

Seems like this isnt quite working to for me. See my edit please – Fallenreaper May 11 '16 at 00:56
1

*"You're mixing up procedural and object oriented style of mysqli"* - Not recommended but it does work ;-) – Funk Forty Niner May 11 '16 at 01:01
@Fallenreaper `get_result()` method is available only with `mysqlnd`, see [the documentation](http://php.net/manual/en/book.mysqlnd.php). – Rajdeep Paul May 11 '16 at 01:02
@Fred-ii- Yes, it's more like a *polite suggestion* rather than a fatal error.:-) – Rajdeep Paul May 11 '16 at 01:02
@RajdeepPaul hehe, I know. ;-) – Funk Forty Niner May 11 '16 at 01:03
So this isnt a fatal error? Seems like it. – Fallenreaper May 11 '16 at 01:12
is there an easy way to install the driver? Seems like im running an older version of mysql. I think 5.1? – Fallenreaper May 11 '16 at 01:13
@Fallenreaper For Ubuntu system, open up the terminal and type `sudo apt-get install php5-mysqlnd` to install the required driver, and then restart the server. – Rajdeep Paul May 11 '16 at 01:16
Im fairly certain im running red-hat – Fallenreaper May 11 '16 at 01:20
@Fallenreaper Then open up the terminal and type `yum install php-mysqlnd` to install the driver, and then restart the server. – Rajdeep Paul May 11 '16 at 01:23
1

@Fallenreaper If you come across any issue during installation, see [this SO Q/A](http://stackoverflow.com/q/13159518/5517143), and the *accepted* solution. – Rajdeep Paul May 11 '16 at 01:35

Darwin von Corax · Answer 2 · 2016-05-11T00:57:46.943

0

As explained in the documentation, mysqli_stmt::fetch does not return a result, it sets the values of variables bound with mysqli_stmt::bind_result.

To retrieve an iterable result set from mysqli, you use mysqli_stmt::get_result, which in turn requires that the mysqlnd native driver package be installed. On Ubuntu this can be installed as the package php5-mysqlnd, for example. Check the output from phpinfo(); there should be an entire section for mysqlnd.

edited May 11 '16 at 00:57

answered May 11 '16 at 00:52

Darwin von Corax

5,026
3
15
26

That might be the issue. I might not have the driver installed – Fallenreaper May 11 '16 at 00:56

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

2 Answers2