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
getting how many of
Moderator: General Moderators
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
Use MySQL instead. This query:would do the trick.
Code: Select all
select count(*) from names where name=1 group by name- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
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:
Regards,
Scorphus.
Code: Select all
select name, count(*) as total from names group by nameScorphus.
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
$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
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
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:
Also, are you using LIMIT in your first SELECT statement? You could do it to improve performance and retrive only essential data.
Regards,
Scorphus.
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";Regards,
Scorphus.
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>';
}
?>