Page 1 of 1

Counting Instances and then displaying a result.

Posted: Wed May 16, 2012 3:34 pm
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";

Re: Counting Instances and then displaying a result.

Posted: Wed May 16, 2012 3:49 pm
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

Re: Counting Instances and then displaying a result.

Posted: Wed May 16, 2012 3:55 pm
by gotornot
Will this give a list of multiple results without displaying them:
[ Laptops(127) - Accesories (244) - Components(65) ]

Re: Counting Instances and then displaying a result.

Posted: Wed May 16, 2012 4:11 pm
by Celauran
I can't say for certain as I have no idea how your database is set up, but that's the idea.

Re: Counting Instances and then displaying a result.

Posted: Wed May 16, 2012 4:29 pm
by gotornot
i have tried including it in a while statement and outside
not sure how to execute it to be honest

Re: Counting Instances and then displaying a result.

Posted: Wed May 16, 2012 5:54 pm
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.