-1

I am trying to figure out why the num_rows always return 1? I searched high and low to find out what I am doing wrong here. Any help would be grateful! In my query where I have status = '' that should find the rows where status is NULL correct? If so then the count should be 2.

$query = "SELECT * FROM referrals WHERE (status = 'W' OR status = '') AND referral_id = '".$thisId."'";
$result = $mysqli->query($query);
$TotalRcount = $result->num_rows;
while($row=$result->fetch_assoc()){
   //read columns
}

$inprogress =      $TotalRcount;
echo $TotalRcount; // Always returning 1
  • Why would a "NULL" row be returned. Can you show the contents of a simple database that gives you this problem? Without knowing what is in your DB it's hard to comment... – Floris Jun 14 '13 at 02:17
  • Basically the column `status` in each `row` may have `NULL` if it has not been set yet. Make since? I updated my question to make better since :) –  Jun 14 '13 at 02:18
  • Nothing matches NULL. See this earlier question / answer: http://stackoverflow.com/questions/12853944/why-in-sql-null-cant-match-with-null – Floris Jun 14 '13 at 02:20
  • [`mysqli_result::$num_rows`](http://php.net/manual/en/mysqli-result.num-rows.php) "returns number of rows in the result set." If you are getting `1` returned, then there is `1 result`. – Aiias Jun 14 '13 at 02:20
  • Thanks Floris! Please submit your answer and I will accept it. I did not know that ;) –  Jun 14 '13 at 02:23

3 Answers3

0

Your status field may be empty, or it may have a zero-length string in it. They are not the same.

Try (status = 'W' or status <= '')

0

Change your query to test for a null with IS NULL

$query = "SELECT * FROM referrals WHERE (status = 'W' OR status = '' OR status is null) AND referral_id = '".$thisId."'";

While you can't test for equality of null you can test to see if a field is undefined.

Orangepill
  • 24,500
  • 3
  • 42
  • 63
0

Use 

$TotalRcount = mysqli_num_rows($result);
jay.jivani
  • 1,560
  • 1
  • 16
  • 33