How to do counts from car database for all categories (PHP)



I have a database for car in which different categories are like Nissan, Mazda, Vauxhall, etc.
Suppose in my database I have 10 Nissan cars, 33 Mazda cars, 44 Vauxhall cars, 100 Mercedece cars etc., then how can I do a single query from mysql and write on my html page using php to count these numbers like as given below,
Nissan(10), Mazda(33), vauxhall(44), Mercedece(100) etc.

Does anyone know how to do this.
Any help will be appreciated. thanks
please mail your exact table structure..


select category, count(1) from 
group by category
Hi I got another idea may be u agreed, if you think it is best to do it. any other suggestion will be considered.
I am thinking to create table with column name 'counts'.
SO whenever customer add or delete a car, it will add and delete in counts table.
and this seems to me easy way to count for all categories as well what u think.
thanks for your help.
imi_99 said:
I am thinking to create table with column name 'counts'.
SO whenever customer add or delete a car, it will add and delete in counts table.
that is a bad way of maintaining the count, you don't do that...
but if you have a very large table and you need the count very frequently, and the query that gives the count is very slow, then it is good make a table which will store counts for that specific date range or something like that
You are right. Can you explain me your query how will it work, mean
when we do query like count(1) from cars, what will it count and bring back.
If it bring only one car name like 20 mazda then what about other types.
DO i need to run in that case query for each car type.

thanks for your help and advice

I am going to explain u further more will help u what I am trying to do.
I got two tables, one is all types of car like accord , mazda, nissan, AND in second table its sub categories like there are 7 types of accord car, 11 types of nissan.

SO I am thinking to show on my first page all cars model name and number of customers car existing in the database EXAMPLE Nissan(30), Accord (40) bla bla.
When customer click on one of car model(like on Nissan(30)) then it shows their sub categories with their description.
Do I need to run your query, u mentioned above or different.
thanks again for your help
As told by ruturajv,

We will be in better position to resolve your query, if we have exact database structure.

Anyway query provided by ruturaj will display the number of records for each category in category table.


Category Name count(*)
Honda 2
Maruti 3

I think this query will not solve your purpose definately. As you want to fatch the result from two tables.

For this you have to use JOIN and there must be some relation in both the tables. SO i must suggest you to provide us the exact table structure.
considering you have table as
* cat_id //auto_increment
* cat_name // mazda, honda, nissan

* car_id  //auto_increment
* car_name //accord, maxima, etc
* cat_id // auto_incement id from category

so  a query to give you this
Nissan (100), Honda(20)
you have to

select a.cat_name, count(1) count_of_cats from
cars b, category a
where a.cat_id = b.cat_id
group by cat_name
order by count_of_cats
I tried your first query yesterday at home, and it was very straight and useable. I never use count for categories so was not sure how will it work. Now I can implement it using two or three tables, like if first table is for model make second for its category and third for each car description filled by customer.
Hopefully this will work. Will do on weekend because at work.

Any way thanks for your help.
both of you

I have one problem in displaying data when run mysql query.
When I use cross join query it brings data of cars then I use while loop like this


echo "I want to print result";


The proble is in displaying result, so instead of displaying result in rows I want to display result in 2/3 columns

instead of displaying like this
echo row1;
echo row2;
echo row3;

I want to display result like this

echo row1 echo row2 echo row3
echo row4 echo row5 echo row6
echo row7 echo row8 echo row9
................ ................. ................

Any one know how to do it. thank in advance