Query working normally but not working in prepared statements

Question

I have the following code of a prepared statement

// Get the data about the file $stmt = $mysqli->prepare("SELECT * FROM file WHERE generated_name = ?"); $stmt->bind_param('s', $generated_name); $stmt->execute(); if($stmt->num_rows == 0) return 'Error';

The code above is not working, it's always returning an error even though it shouldn't affected_rows is weirdly returning -1 as well. However, if I leave the prepared approach and do a normal query.. it works perfectly

// Get the data about the file $result = $mysqli->query("SELECT * FROM file WHERE generated_name = '$generated_name'"); if($result->num_rows == 0) return 'Error';

This code works perfectly and doesn't return an error. I have no idea what is wrong. Could you please identify the error?

Mihai · Accepted Answer · 2014-07-04 23:50:49Z

3

You can use store_result after execute

$stmt->store_result(); $rows= $stmt->num_rows; if($rows > 0)...

answered Jul 4, 2014 at 23:50

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

r3wt Over a year ago

this is not supported on some php installations, such as HipHopVirtualMachine, which also doesn't support get_result() either

Joe T · Accepted Answer · 2014-07-04 23:46:45Z

I believe you have to do the "fetch" to actually get the rows; http://www.php.net/manual/en/mysqli.prepare.php

Try adding this;

 $stmt->fetch();

like this;

// Get the data about the file $stmt = $mysqli->prepare("SELECT * FROM file WHERE generated_name = ?"); $stmt->bind_param('s', $generated_name); $stmt->execute(); $stmt->fetch(); if($stmt->num_rows == 0) return 'Error';

might be helpful to explain the reason you have to call fetch(), because remember you must optionaly bind_result() after execute() to bind results to variables. if it automatically fetched the result you wouldn't have the chance to bind variables.
good point r3wt, you'll want to use bind_result if you plan to actually get the data.

Danijel · Accepted Answer · 2014-07-05 00:34:05Z

I believe that this comment about num_rows in PHP manual explains it. Quote:

If you do not use mysqli_stmt_store_result( ), and immediatley call this function after executing a prepared statement, this function will usually return 0 as it has no way to know how many rows are in the result set as the result set is not saved in memory yet.

Collectives™ on Stack Overflow

Query working normally but not working in prepared statements

3 Answers 3

1 Comment

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Related