Single Result SQL Code

[EmperorJazzy]

At present, all my queries return multiple records. Now I want to do a record count, and only return the one record.

 

Current Code

$query = "SELECT * FROM tblListField WHERE ListID = '" . $_SESSION['listid'] . "'";

$result = mysqli_query($dbc, $query) or die('Error executing SELECT * statement for tblListField - ' . $query);

	if(mysqli_num_rows($result))
	{
		// at least one row, process the data from the query
		while ($row = mysqli_fetch_array($result))
                        {

 

So the above runs through each record; what do I use to get the one record from the following SQL? (Note; I'm using mysqli_)

 

$query = "SELECT COUNT(ItemField_1) WHERE ListID='" . $listid . "'";

[Maq]

Do this calculation in MySQL using COUNT:

http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

[EmperorJazzy]

Maq - Appreciate it; apologies, I omitted the SQL with the COUNT function (now included). The question is orientated around the PHP syntax.

[Maq]

Maq - Appreciate it; apologies, I omitted the SQL with the COUNT function (now included). The question is orientated around the PHP syntax.

Are you asking how to display the count result?  If not, could you elaborate?

 

I would change the query to alias the count (easier to reference & read):

$query = "SELECT COUNT(ItemField_1) AS cnt WHERE ListID='" . $listid . "'";

 

In your PHP you would then reference 'cnt' from the $row array:

while ($row = mysqli_fetch_array($result))
{
   echo $row['cnt'];
}

[EmperorJazzy]

So essentially there is no syntax similar to the following without having to use the WHILE loop each time;

 

$query = .....

$result = ......

echo "Number of records = " . $result['cnt'];

 

Or could I simply use the mysqli_num_rows()?

 

[Maq]

So essentially there is no syntax similar to the following without having to use the WHILE loop each time;

Then don't use a while loop.

   $row = mysqli_fetch_array($result);
   echo $row['cnt'];

 

Or could I simply use the mysqli_num_rows()?

COUNT returns a single row/value, so mysqli_num_rows() would always return 1.  If you don't use COUNT, then using mysqli_num_rows() would be inefficient.  It's better to use SQL, that's what it was designed for.

[EmperorJazzy]

   $row = mysqli_fetch_array($result);
   echo $row['cnt'];

 

Exactly what I was looking for. Thank you.[/code]