Counting successful queried rows

[Davy]

Hey guys!

I believe this is the first time I've ever asked you people for help so here goes...

I am currently working on a site that stored statistics on benchmarks that user's submit. An admin comes along, logs in and views these submissions and verifies them. I'll state the query for this in a mo, but I want in the list of hardware types to have a number in brackets () stating the amount of successful queries.

How do I do this? I know that the links will go to the next page with the actual queries but for ease' sake, let's assume this number is on the same page as the queries.

I'm gonna use the example from querying the CPU table.

$cpu="SELECT * from cpu WHERE verified='No' ORDER by submissionDate";  // Querying the table for unverified CPU submissions
				$cpuresult=mysql_query($cpu); 

// the below is just the table used for displayign the data in case you need that info. It also says that the results are stored in the array 'CPU'.
while ($cpu=mysql_fetch_array($cpuresult))
					{					
					echo "<tr>";
					echo "<td>";
					echo $cpu['manufacturer']." ".$cpu['model'];
					echo "</td>";
					echo "<td>";
					echo "<a href=hardwareEdit.php?type=cpu&hwID=".$cpu['IDnum'].">Edit</a>";
					echo "</td>";
					echo "<td>";
					echo "<a href=verify.php?type=cpu&hwID=".$cpu['IDnum'].">Verify</a>";
					echo "</td>";
					echo "</tr>";
					}
				echo "</table>";

/* The above table also includes the links that make the changes. Well, the links don't make the changes - they add _GET variables to the URL for the processing page to pick up and make the necessary changes using use of if statements. */

As far as I can see - this is all the info you will need. I hope somebody can help me for a change!! Thanks in advance.

[TheDefender]

You'd use a count() in your query....

SELECT count(fieldname) as whatever FROMM table WHERE fieldname = 'whatever'

I won't go into details, because I know you can get it from here, but you'd just run this query in your loop... Shouldn't be that much of a server load, since I'm pretty sure you'll have the field indexed anyhow.

[alvinkoh]

Originally posted by TheDefender
SELECT count(fieldname) as whatever FROMM table WHERE fieldname = 'whatever'

To add on to the solution that TheDefender has given,

Using SELECT count(*) should give a performance gain, versus selecting a field. Not sure if this varies from database versions, but you can attempt both and view the execution timing for a comparison. It's somewhere in the mysql manual and may help to give better performance.

Another method makes use of the php function -> mysql_num_rows()

$result = mysql_query("SELECT * FROM table");
$total_rows = mysql_num_rows($result);

This allows you to execute the query and to get the total number of rows affected by your query.

Cheers,
Alvin.