Prepared statements returning 0 rows, normal query returns 1 row

Question

Im having this issue where prepared statement is returning 0 rows while a normal query returns 1 row

This is the prepared statement

$sender = 'marshall';
$accepter = 'LUcase';
$int1 = '1';
$check_request_1 = $friend_zone->prepare("SELECT * FROM friends_request WHERE (sender = ? and accepter = ? and status = ?) OR (accepter = ? and sender = ? and status = ?)");
$check_request_1->bind_param('ssssss', $sender, $accepter, $int1, $sender, $accepter, $int1);
$check_request_1->execute();
$check_request_1->store_result();
$nrow2 = $check_request_1->num_rows;
$check_request_1->fetch();
$check_request_1->close();

echo $nrow2

And yes 1 is a string because the field is an enum in the database

Normal query

$sql = "SELECT * FROM friends_request WHERE (accepter = 'marshall' AND sender = 'LUcase' AND status = '1') OR (accepter = 'LUcase' AND sender = 'marshall' AND status = '1')";
$query = mysqli_query($friend_zone, $sql);
$numrow = mysqli_num_rows($query);

The entire code(This might be the problem, will figure it out in a few minutes hopefully)

  $check_request = $friend_zone->prepare("SELECT * FROM friends_request WHERE (sender = ? and accepter = ? and status = ?) OR (accepter = ? and sender = ? and status = ?)");
  $check_request_1 = $friend_zone->prepare("SELECT * FROM friends_request WHERE (sender = ? and accepter = ? and status = ?) OR (accepter = ? and sender = ? and status = ?)");
  $check_request_2 = $friend_zone->prepare("SELECT * FROM friends_request WHERE accepter = ? and sender = ? and status = ?");

  $check_request->bind_param('ssssss', $sender, $accepter, $int0, $sender, $accepter, $int0);
  $check_request_1->bind_param('ssssss', $sender, $accepter, $int1, $sender, $accepter, $int1);
  $check_request_2->bind_param('sss', $sender, $accepter, $int0);

  $check_request->execute();
  $check_request_1->execute();
  $check_request_2->execute();

  $check_request->store_result();
  $check_request_1->store_result();
  $check_request_2->store_result();

  $check_request->bind_result($reqid, $req_sender, $req_accepter, $req_status);
  $check_request_1->bind_result($reqid1, $req_sender1, $req_accepter1, $req_status1);
  $check_request_2->bind_result($reqid2, $req_sender2, $req_accepter2, $req_status2);

  $nrow1 = $check_request->num_rows;
  $nrow2 = $check_request_1->num_rows;
  $nrow3 = $check_request_2->num_rows;

  $check_request->fetch();
  $check_request_1->fetch();
  $check_request_2->fetch();

  $check_request->close();
  $check_request_1->close();
  $check_request_2->close();

To be complete, a [sample](http://sscce.org/) should include data and expected/desired results. Since the data is stored with SQL, a complete specification of sample data would include the SQL schema (as a `CREATE TABLE` statement). — outis, Mar 25 '12 at 07:05
Don't use [`SELECT *`](http://stackoverflow.com/questions/321299/) unless you're writing a DB administration program; select only the columns you need. — outis, Mar 25 '12 at 07:05
Read the link. As the comment says, your example is missing sample data and the definition of table `friends_request`. — outis, Mar 25 '12 at 07:11
Could you also show us the code you have for the standard query, please? — Michael, Mar 25 '12 at 07:13
also i have many other queries in the same format to the same table and they return the correct number of rows. — Marshall Mathews, Mar 25 '12 at 07:17
I can't reproduce. When filled out with suitable data, the sample code returns a single row for me. You've probably made some simple error elsewhere. These are some of the big reasons to create a sample that reproduces the issue: so others have a hope of answering your question, so you can catch simple errors, so you can respect the time of others. — outis, Mar 25 '12 at 07:18
I'm curious, why use OOP-style for the prepared statement but procedural-style for the non-prepared statement? — Michael, Mar 25 '12 at 07:21
Does `$check_request_1->error` return anything at any point? — Michael, Mar 25 '12 at 07:24
i get a commands out of sync. I think its because I have three prepared statement.. Updating with my entire code.. i think i should execute them one by one rather than all together — Marshall Mathews, Mar 25 '12 at 07:27
@MarshallMathews: if a question is answered, don't put "solved" in the title, [accept](http://meta.stackexchange.com/questions/5234/) the answer that resolved the issue. You should be able to [answer your own question](http://meta.stackexchange.com/q/17463/133817). — outis, Mar 25 '12 at 18:49
... `$check_request` and `$check_request_1` are the same statement. As you're going to execute them serially, re-use the first statement instead of using a duplicate. — outis, Mar 25 '12 at 18:52
Wise words from @outis so no Solved on title; marking an answer as accepted (even OP's own answer) is the path of the righteous. — Alfabravo, Jun 07 '17 at 16:18

tusar · Answer 1 · 2012-03-25T07:29:44.203

0

Update : After seeing comments, it is clear that both conditions are not same. So here is the fix for your code.

$sender = 'marshall';
$accepter = 'LUcase';
$int1 = '1';
$check_request_1 = $friend_zone->prepare("SELECT * FROM friends_request WHERE (sender = ? and accepter = ? and status = ?) OR (accepter = ? and sender = ? and status = ?)");
$check_request_1->bindParam(1, $sender);
$check_request_1->bindParam(2, $accepter);
$check_request_1->bindParam(3, $int1 );
$check_request_1->bindParam(4, $accepter);
$check_request_1->bindParam(5, $sender);
$check_request_1->bindParam(6, $int1);
$check_request_1->execute();
$check_request_1->store_result();
$nrow2 = $check_request_1->num_rows;
$check_request_1->fetch();
$check_request_1->close();

echo $nrow2

edited Mar 25 '12 at 07:29

answered Mar 25 '12 at 07:08

tusar

3,364
6
37
60

no Im checking if accepter = $sender, sender = $accepter In the second condition and accepter = $accepter, sender = $sender In the first condition. Also execute expects 0 parameters exactly. – Marshall Mathews Mar 25 '12 at 07:11
ah yes, you are not using PDO. updating my answer – tusar Mar 25 '12 at 07:22
check the comments on my question. I figured out what the problem is :) – Marshall Mathews Mar 25 '12 at 07:32

Michael · Answer 2 · 2012-03-25T07:53:49.030

Change

  $check_request->execute();
  $check_request_1->execute();
  $check_request_2->execute();

  $check_request->store_result();
  $check_request_1->store_result();
  $check_request_2->store_result();

  $check_request->bind_result($reqid, $req_sender, $req_accepter, $req_status);
  $check_request_1->bind_result($reqid1, $req_sender1, $req_accepter1, $req_status1);
  $check_request_2->bind_result($reqid2, $req_sender2, $req_accepter2, $req_status2);

  $nrow1 = $check_request->num_rows;
  $nrow2 = $check_request_1->num_rows;
  $nrow3 = $check_request_2->num_rows;

  $check_request->fetch();
  $check_request_1->fetch();
  $check_request_2->fetch();

  $check_request->close();
  $check_request_1->close();
  $check_request_2->close();

To

  $check_request->execute();
  $check_request->store_result();
  $check_request->bind_result($reqid, $req_sender, $req_accepter, $req_status);
  $nrow1 = $check_request->num_rows;
  $check_request->fetch();
  $check_request->free_result();
  $check_request->close();

  $check_request_1->execute();
  $check_request_1->store_result();
  $check_request_1->bind_result($reqid1, $req_sender1, $req_accepter1, $req_status1);
  $nrow2 = $check_request_1->num_rows;
  $check_request_1->fetch();
  $check_request_1->free_result();
  $check_request_1->close();

  $check_request_2->execute();
  $check_request_2->store_result();
  $check_request_2->bind_result($reqid2, $req_sender2, $req_accepter2, $req_status2);
  $nrow3 = $check_request_2->num_rows;
  $check_request_2->fetch();
  $check_request_2->free_result();
  $check_request_2->close();

Prepared statements returning 0 rows, normal query returns 1 row

2 Answers2