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

A

Anonymous

Guest
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
 
please mail your exact table structure..

probably

Code: 
select category, count(1) from 
cars
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.
Click to expand...
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.

like

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
Code: 
category
* cat_id //auto_increment
* cat_name // mazda, honda, nissan

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

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

while($row=mysql_fetch_array($result))
{

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
example

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
 
You must log in or register to reply here.
Back
Top