Hi,
Currently i got a database with about 6000 clients divided among 6 headcats and 900 subcats. All of these clients are in in one or more subcategories (defined by CSV like 2,54,85,68,548).
So when i wanna show a list of categories i always filter the subcategories with 0 clients in it by just counting how many clients there are in that particular subcategory. At first i exploded the CSV and put it in an array, walked trough the array and stopped when a hit had been found, recently i switched to REGEXP in the mysql query which saves me some processing time since i don't need to walk the array anymore.
So when there are 15 records from the clients table in the "restaurant" category it displays " Restaurants(15) "
But when there are 0 records in the " Chinese food " category it doesn't display this category.
The problem here is that i have a total of 900+ categories and doing it the way i described above results in a massive amount of 900+ queries to the server which obviously is a little too much.
I'm thinking of putting everything in a multidimensional array with one big query. But perhaps someone has another solution?
This is the code i use.
Does anyone have any suggestions to improve this code so less queries are needed and the page loads faster?
// SUBCATS
$subcat_query = "SELECT * FROM subcats WHERE headcat_id = $headcat_id AND active = 1 ORDER BY name";
$subcat_result = mysql_query($subcat_query,$verbinding);
while ($subcat_row = mysql_fetch_object($subcat_result))
{
// COUNT WITH REGEXP
$cur_subcat = $subcat_row->subcat_id;
$count_query = "SELECT * FROM clients WHERE project_id = '$project_row->project_id' AND active = '1' AND subcat_id REGEXP '(^$cur_subcat,.{0,})|(.{0,},$cur_subcat,.{0,})|(.{0,},$cur_subcat$)|($cur_subcat)'";
$count_result = mysql_query($count_query,$verbinding);
$count[$subcat_row->subcat_id] = mysql_num_rows($count_result);
// DISPLAY IF COUNT > 0
if ($count[$subcat_row->subcat_id] > 0)
{
echo "<a href='index2.php?p=overzicht&id=" . $subcat_row->subcat_id . "'>" . $subcat_row->{"name_".$lang} . "</a> (" . $count[$subcat_row->subcat_id] . ")<br>";
}
}
