I thought I would just take a minute to share this little code snippet with you as I had some trouble finding posts online that would tell me if I was going about this the right way! The problem is that I needed a fast way to count how many rows were in a database without using a CPU demanding function like mysql_num_rows().
<?php
$query = 'SELECT COUNT(*) FROM `users`';
$result = mysql_query($query) or die('Sorry, we could not count the number of results: ' . mysql_error());
$numberofresults = mysql_result($result, 0);
?>You can also limit the count to only rows that meet a certain value:
<?php
//Where the user id is greater than 149.
$query = 'SELECT COUNT(*) FROM `your_table` WHERE `id` > 149';
$result = mysql_query($query) or die('Sorry, we could not count the number of results: ' . mysql_error());
$numberofresults = mysql_result($result, 0);
?>Keep in mind that this is for counting all the rows in the table. For example, if you had a database like this with 40 of your "users" in it:
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(20) NOT NULL default '',
`password` varchar(20) NOT NULL default '',
`email` varchar(70) NOT NULL default '',
)Remember, it is slower to select a single field (SELECT COUNT(id)FROM `users`) than it is to just count the whole thing (SELECT COUNT(*)FROM `users`). Because MySQL already knows how many rows are in each database - so it can just tell you. But if you specify a certain field - then MySQL has to open each row and look around for that field.
Thx! This was just what I were looking for!! thx again!
I have a select of header and detail records, which i would like to return the count of details for each header. Using the below i get an error
SELECT TOP 1000 ab.*, abd.Key, COUNT(abd.UPC) as cnt FROM AuditBatches as ab INNER JOIN AuditBatchesDetail as abd ON ab.Key = abd.Key WHERE (ab.Dept = '3')
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Key' as part of an aggregate function., SQL state 37000 in SQLExecDirect
Any help would be appreciated,
Steve
Like the error states - it doesn't like the "abd.Key" and "ab.Key = abd.Key" parts. Is "Key" the name of one of your table columns? Or are you using it when you should be using "ab.id = abd.id"?
How to extract just a Xls sheet from a database table.?
