How to do counts from car database for all categories PHP/MYSQL

imi_99

Hello,
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

subwayman

SELECT cartype, count(1) AS noCars FROM cars GROUP BY cartype

This will result in a row for each cartype and the noCars column will have the number of records with that cartype.

imi_99

subwayman wrote:
SELECT cartype, count(1) AS noCars FROM cars GROUP BY cartype

This will result in a row for each cartype and the noCars column will have the number of records with that cartype.

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.

toxic_brain

ask to developers, u 'll lost

subwayman

Yes you could do this but this is a common solution for the less experienced. What happens when somone adds a car but either the count update fails or the count update works but the car insert does not. You will also need extra code to reduce the count if a car gets removed. Far better to use the solution I gave with my last post. This is the regular way to do it and it will ALWAYS return an accurate count.

imi_99

subwayman wrote:
Yes you could do this but this is a common solution for the less experienced. What happens when somone adds a car but either the count update fails or the count update works but the car insert does not. You will also need extra code to reduce the count if a car gets removed. Far better to use the solution I gave with my last post. This is the regular way to do it and it will ALWAYS return an accurate count.

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

subwayman

This is very basic stuff. Perhaps you should read your database/sql notes again.

Just take the query I gave you and run it against your data (obviously changing column and table names) and you will have your answer. (Actually, I told you the answer in my first post).

For the second part you will need to run two queries - simply because you need two interactions: send list of makes - user selects make - send list of cars.

imi_99

subwayman wrote:
This is very basic stuff. Perhaps you should read your database/sql notes again.

Just take the query I gave you and run it against your data (obviously changing column and table names) and you will have your answer. (Actually, I told you the answer in my first post).

For the second part you will need to run two queries - simply because you need two interactions: send list of makes - user selects make - send list of cars.

thanks for your reply.
Once I will get home, I will try your query. could be it very straight