Page 1 of 1

getting how many of

Posted: Tue Jan 13, 2004 11:59 am
by dmcglone
Is there a way to use php to count how many of the identical names are in the db?

for instance lets say I have 10 1's in the database, and 20 2's. I want to be able to echo back 10 when I ask how many 1's are in the db and 20 when I ask how many 2's there are.

Thanks
David

Posted: Tue Jan 13, 2004 12:04 pm
by scorphus
Use MySQL instead. This query:

Code: Select all

select count(*) from names where name=1 group by name
would do the trick.

Posted: Tue Jan 13, 2004 12:08 pm
by scorphus
The above query will show how many 1's there are in the data base. The following query will show the correspondiong number for each name:

Code: Select all

select name, count(*) as total from names group by name
Regards,
Scorphus.

Posted: Tue Jan 13, 2004 12:22 pm
by dmcglone
Thanks a lot scorphus, I was hoping I could do it a different way without having to use a select statement so I wouldn't mess up the statement Im using right now which looks like this:

$query = "SELECT products.name_id, product_names.product_name AS NAME, products.available from products, product_names where products.name_id = product_names.product_name_id and available='Yes' group by name_id";

the above select statement groups everything together perfectly and a link is used to switch to a different name_id which is a different category. and each link only displays the products in that category, but What im trying to accomplish here is having only 4 products displayed per page and have previous and next links at the bottom of the page if there are more than 4 of the same products in that perticular category.

This requires calculating how many of the same thing I have in the DB which is identified by the name_id and so on and so forth.

Do you think that I should write a seperate SELECT statement or use the exsisting one?

Thanks,
David

Posted: Tue Jan 13, 2004 2:22 pm
by scorphus
Hmm... I got what you mean. Anything you would do using PHP would be less efficient than MySQL. So it is not a bad idea to perform another select query on the database. So you could do this:

Code: Select all

$query = "select product_names.product_name as name, count(*) as total from products, product_names where products.name_id = product_names.product_name_id and products.available='Yes' group by name";
Also, are you using LIMIT in your first SELECT statement? You could do it to improve performance and retrive only essential data.

Regards,
Scorphus.

Posted: Tue Jan 13, 2004 4:45 pm
by infolock
or something liek this :

Code: Select all

<?php

$sql = "SELECT count(myfield) as repetitions, myfield from mytable group by myfield having repetitions > 0";
$result = mysql_query($sql) or die(MySQL_Error());

while($row = mysql_fetch_array($result))
{
   echo 'Field Name : '.$row['myfield'].' <br> Repetitions Found : '.$row['repetitions'].' <br> <br>';
}

?>