prepared statement mysqli not returning rows

Question

I have some php code with a prepared statement. It is called via Ajax in javascript. I am sure there is an account called mark (no caps) in the accounts table, but when I call this with $_POST['query'] set to m it finds $stmt->num_rows===0 to be true. No errors can be seen, it just doesn't work! As you can tell, it is echoing content into a table. I tried this query in phpMyAdmin: SELECT username FROM accounts WHERE username LIKE '%m%' and it worked fine. I created this code to search a mysql database for usernames. In case your wondering, $conn is a valid mysqli object defined in the include file.

<?php require_once('./include.php'); $stmt=$conn->stmt_init(); $stmt->prepare('SELECT username FROM accounts WHERE username LIKE ?'); $compquery='%'.$_POST['query'].'%'; $stmt->bind_param('s',$compquery); $stmt->execute(); echo '<tr><td>'; if($stmt->num_rows!==0){ $stmt->bind_result($name); while($stmt->fetch()){ echo "$name</td></tr><tr><td>"; } echo '</td></tr>'; } else echo 'No Results Found</td></tr>';

already did error checking, just took it out of the question. — markasoftware
– markasoftware, Commented Oct 18, 2012 at 3:39
no quote around ? in prepared statement. it is added automatically with bind_param argument s. — markasoftware
– markasoftware, Commented Oct 18, 2012 at 3:40
Thank you @Passerby. That completely makes this work. I'll wait 5 minutes if you want to post the answer, but if you don't I'll just post the answer and credit you. — markasoftware
– markasoftware, Commented Oct 18, 2012 at 3:42

Passerby · Accepted Answer · 2012-10-18 03:48:02Z

Extending from comment:

You have to use mysql_stmt::store_result() before you can use mysqli_stmt::num_rows:

$stmt=$conn->stmt_init(); $stmt->prepare('SELECT username FROM accounts WHERE username LIKE ?'); $compquery='%'.$_POST['query'].'%'; $stmt->bind_param('s',$compquery); $stmt->execute(); $stmt->store_result();

Collectives™ on Stack Overflow

prepared statement mysqli not returning rows

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related