getting how many of

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dmcglone
Forum Newbie
Posts: 20
Joined: Sun Sep 28, 2003 7:54 pm
Location: Columbus, Ohio

getting how many of

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

Use MySQL instead. This query:

Code: Select all

select count(*) from names where name=1 group by name
would do the trick.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
dmcglone
Forum Newbie
Posts: 20
Joined: Sun Sep 28, 2003 7:54 pm
Location: Columbus, Ohio

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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>';
}

?>
Post Reply