Counting Instances and then displaying a result.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gotornot
Forum Commoner
Posts: 54
Joined: Fri Jul 31, 2009 2:30 am

Counting Instances and then displaying a result.

Post by gotornot »

Hi

I am building a product search engine using MySql and PHP (Jquery / AJAX).
As i am getting the search results i wondered is it possible to count the instances of a category?
For example:
If i search for "Dell Laptops" i get 400+ results back because it has lots of laptops and accessories.
What i am aiming for it to do is while looping through look at the categories and if its the first instanc eof it i want it to begin a count. until it has finished looping and then i can display it.
Results: 436 [ Laptops(127) - Accesories (244) - Components(65) ]

Im not even sure where to start here so any advice will be helpful :)

Here is the code im using to generate the search:
$id = str_replace(" ", "%", "$id");
$q = "SELECT * FROM products WHERE prod_name LIKE '%$id%' order by fee asc LIMIT 20";
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Counting Instances and then displaying a result.

Post by Celauran »

If you're just trying to get a count, why not use COUNT?

Code: Select all

SELECT COUNT(category)
FROM products
WHERE prod_name LIKE '%foo%'
GROUP BY category
gotornot
Forum Commoner
Posts: 54
Joined: Fri Jul 31, 2009 2:30 am

Re: Counting Instances and then displaying a result.

Post by gotornot »

Will this give a list of multiple results without displaying them:
[ Laptops(127) - Accesories (244) - Components(65) ]
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Counting Instances and then displaying a result.

Post by Celauran »

I can't say for certain as I have no idea how your database is set up, but that's the idea.
gotornot
Forum Commoner
Posts: 54
Joined: Fri Jul 31, 2009 2:30 am

Re: Counting Instances and then displaying a result.

Post by gotornot »

i have tried including it in a while statement and outside
not sure how to execute it to be honest
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Counting Instances and then displaying a result.

Post by Celauran »

Queries don't belong inside while statements.

Try something like this:

Code: Select all

$query = "SELECT category, COUNT(category) AS num
          FROM products
          WHERE prod_name LIKE :id
          GROUP BY category";
$stmt = $sql->prepare($query);
$stmt->execute(array(':id' => '%' . $id . '%'));

$result = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach ($result as $row)
{
    echo "{$row->category} ({$row->num}) ";
}
where $sql is your PDO object.
Post Reply