Counting Instances and then displaying a result.

[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";

 

[smoseley]

2nd query:

 

SELECT cat_name, COUNT(*)

FROM products

WHERE prod_name LIKE '%$id%'

GROUP BY cat_name;

[gotornot]

how would i add that in?

Or am i being blonde?

[scootstah]

You probably don't want to work with 400+ results all at once. If you have a few thousand users pulling hundreds of results at the same time you're going to use a lot of unnecessary memory. You'd be better off with pagination and only displaying 20-30 at a time.

 

Therefore you'll probably need some sub-queries to count the categories that correspond to the keyword. I'm having a hard time coming up with a good example, though.

[gotornot]

I do limit the result to 20.

Just racking my brain trying to think how i am going to show the result preferably without having multiple duplicate results displayed....

[smoseley]

how would i add that in?

Or am i being blonde?

 

either execute them as 2 separate queries, or use mysqli_multi_query and separate the queries by a semicolon.

[gotornot]

I looked at doing that but their are many cats and we don't need them all so its only needed to be counted if we have product in that area.

 

[smoseley]

I looked at doing that but their are many cats and we don't need them all so its only needed to be counted if we have product in that area.

The query i gave you would only return results for cats with products in them.

[gotornot]

All sorted thanks )