Get number of rows in a mysql result set

[Someone]

I am wondering how to get the number of rows in a result set. My best guess is that it should be $result->NbRows holding the value, but it stays at -1

 

// Sample code at http://www.nw-engine.com/wiki/index.php/Database
$result = $db->Execute("SELECT amount, bonus, rate, time FROM ngm_bank WHERE playerid = ? LIMIT 5", $userId);
var_dump($result);
while(!$result->EOF)
{
       print_r($result->fields);
$result->MoveNext();
}
$result->Close();

 

The vardump output:

object(Resultset)[16]
 private 'result' => boolean true
 private 'stmt' => 
   object(mysqli_stmt)[18]
     public 'affected_rows' => null
     public 'insert_id' => null
     public 'num_rows' => null
     public 'param_count' => null
     public 'field_count' => null
     public 'errno' => null
     public 'error' => null
     public 'error_list' => null
     public 'sqlstate' => null
     public 'id' => null
 public 'fields' => 
   array (size=4)
     0 => &int 1000
     1 => &int 10
     2 => &int 1
     3 => &int 1356859428
 public 'EOF' => boolean false
 private 'conn' => 
   object(mysqli)[14]
     public 'affected_rows' => null
     public 'client_info' => null
     public 'client_version' => null
     public 'connect_errno' => null
     public 'connect_error' => null
     public 'errno' => null
     public 'error' => null
     public 'error_list' => null
     public 'field_count' => null
     public 'host_info' => null
     public 'info' => null
     public 'insert_id' => null
     public 'server_info' => null
     public 'server_version' => null
     public 'stat' => null
     public 'sqlstate' => null
     public 'protocol_version' => null
     public 'thread_id' => null
     public 'warning_count' => null
 public 'NbRows' => int -1
 private 'currentRow' => int 0

Edited December 30, 2012 by Someone

[a_bertrand]

I'm unsure if MySQLi returns the number of rows of a query beside for an UPDATE / DELETE one. I could check however.

[Someone]

Reason I am asking is that I often prefer to know if there is rows to show. I often tent to make scripts following this structure.

 

/*
* Manage displaying current transactions and handle payouts
*/
$result = $db->Execute("SELECT id, amount, bonus, rate, time FROM ngm_bank WHERE playerid = ? LIMIT 5", $userId);
//var_dump($result);
while(!$result->EOF)
{
//print_r($result->fields);
$timeLeft = $result->fields[4] - time();
if($timeLeft > 0){
	$left = sec2string(sec2time( $timeLeft ));
	$list .= '<tr><td><a class="btn btn-mini btn-danger" href="index.php?p=ngm_bank&cancel='.$result->fields[0].'">Cancel Transaction</a></td><td> '.number_format($result->fields[1]).'</td><td>'.number_format($result->fields[2]).' '.$result->fields[3].'%</td><td>'.$left.'</td></tr>';
} else {
	//Remove transaction and send money to bank
	$payout = $result->fields[2]+$result->fields[1];
	$payout_total += $payout;
	$userStats['bank']->value += $payout;
	$db->Execute("DELETE FROM ngm_bank WHERE id = ? LIMIT 1", $result->fields[0]);
}


$result->MoveNext();
}
$result->Close();

if(strlen($list) == 0){
$list = '<tr><td colspan="4">You currently do not have any money in the bank</td></tr>';
}

 

Further down the output section

 

<table class="table table-striped table-bordered table-condensed">
<thead>
<tr>
	<th style="width: 13%;"> </th>
	<th>Deposited</th>
	<th>Intrest</th>
	<th>Time Left</th>
</tr>
</thead>

<tbody>
	<?php echo $list; ?>	
</tbody>

</table>

 

As you see I made a tempt solution by using strlen(). That does not work to bad in this example. But would like a way to do this:

 

$r = mysql_query("SELECT * FROM users WHERE id='something'");
if(mysql_num_rows() > 0){
// do stuff

} else {
// No reason todo anything
}

[a_bertrand]

You then simply need to check if you are already at the end of the query:

 

$result = $db->Execute("SELECT id, amount, bonus, rate, time FROM ngm_bank WHERE playerid = ? LIMIT 5", $userId);
// No rows...
if($result->EOF)
{
   // Do something
}
else
{
   // Go through all
   foreach($result as $row)
   {
       // Do something on the row
   }
}

[gmoore]

1331 Are you just trying to see true or false rather than array length? I would have responded earlier just wanted you to post more information. I think Alain (is that right?) may have nailed it though.

Greg

[a_bertrand]

Yes Alain is my first name ;-)

[gmoore]

I've been to Zuirch, Berne and Lugano. Just need to stop the cigarette smoking in the resturants lol

[gmoore]

Sorry not Lugano, Lucerne. Lived in Germany for 3 years.

[Someone]

I am unfamiliar with mysqli, and noticed the db class had $NbRows figured that contained the number of rows. But in the later sample, I asked for a true/false.

So to make it simple, best way to get number of rows returned from a database query?

[a_bertrand]

The best way is to use the SQL function COUNT like

 

select count(id) from users

 

I still need to investigate if MySQLi returns a number of rows before you actually went through all the result, honestly I doubt it.