I have a personal ads website .
First problem: I have 2 tables, category
and ads
.
Category
table (id
is the primary key)
| id | type | subtype | pos |
+-----+-------------+-----------+--------+
| 1 | sell | | 1 |
| 2 | jobs | | 2 |
| 3 | dating | | 3 |
| 4 | dating | boys | 1 |
| 5 | dating | girls | 2 |
| 6 | sell | cars | 1 |
| 7 | jobs | teacher | 1 |
Ads
table (id
is the primary key) – this table has a lot of ads
| id | title | type | subtype|
+-----+-------------+-----------+--------+
| 1 | some text | sell | cars |
| 2 | some text | dating | girls |
| 3 | some text | dating | boys |
I want to read all category types and get the total number of ads for that type, and I need to get the subtypes too.
The output on my page will be something
Sell (1 ads)
Cars
Jobs (0 ads)
Teacher
Dating (2 ads)
Boys
Girls
What I am currently doing is making 3 queries using php and mysql, the first of which returns the categories type is a select where subtype=”” , the second counts existing ads in those categories and the third gives me subtype for each type, I want to optimize mysql, reduce the number of queries and be faster even if it means changing the tables and adding more indexes
Select query:
select *
from category
where subtype = ''
order by pos desc
Count numbers of ads on a type:
select type
from ads
where type = '$type_name'
Select subtype:
select *
from category
where type = '$type_name'
and subtype !=''
order by pos
Advertisement
Answer
You can use this
SELECT c.*,a.c_ads
FROM
category c LEFT JOIN ( SELECT COUNt(type) c_ads,type FROM ads GROUP BY type) a
ON c.type = a.type
ORDER BY c.type,c.pos DESC
id | type | subtype | pos | c_ads -: | :----- | :------ | --: | ----: 3 | dating | | 3 | 2 5 | dating | girls | 2 | 2 4 | dating | boys | 1 | 2 2 | jobs | | 2 | null 5 | jobs | teacher | 1 | null 1 | sell | | 1 | 1 5 | sell | cars | 1 | 1
When you get the data in php check row by row, if the type changes and then you can can use the column c_ads to Display ($row{‘c_ads’] ads) and row[‘type’] to display the text of the category
Where row[‘type’] doesn’t change you find the subcategories to row[‘type’] in row[‘subtype ‘]