EverSQL Database Performance Knowledge Base
Linked Tables and SQL SELECT Queries
Database type:
I have a few linked tables in my custom forum: categories, sub_categories and posts
basically, I have people able to add up to three categories and five sub-categories when they make a new post.
I also enable people to 'listen' to certain categories and sub-categories and have them in an easy to access bar at the side of the page.
My tables are set up thus (only showing relavent fields for ease):
posts:
id INT
category_id VARCHAR(12)
sub_category_id VARCHAR(35)
categories:
id INT
name VARCHAR(20)
sub_categories:
id INT
name VARCHAR(20)
in my posts table, I store the set categories and sub-categories by their ID in the following format:
category_id [2][4][8]
sub_category_id [1][2][3][4][5]
thus enabling me to execute the following query in PHP and get the post based on category and sub-category:
SELECT * FROM posts WHERE category_id LIKE '%[{$id}]%' AND sub_category_id LIKE '%[{$id2}]%'
the problem I have is selecting the sub_categories for the access bar that people 'listen' to...
$sql = "SELECT title, id FROM categories";
$query = mysql_query($sql);
$list = array();
while ($row = mysql_fetch_assoc($query)){
$list[$row['title']] = array();
$sql = "SELECT sub_categories.title FROM sub_categories, posts WHERE (category_id LIKE '%[{$row['id']}]%') AND (????) LIMIT 0,100";
$query = mysql_query($sql);
while($result = mysql_fetch_assoc($query)){
$list[$row['title']][] = $result['title'];
}
}
print_r($list);
Obviously you can see where I am stuck (????), but before I explain what I am trying to do, I'll explain what the output I am looking for is.
when I print the $list array, I want it to print a multi-dimensional array featuring the categories as the first key, with their values being an array of sub-categories that have been tagged in the main category.
The problem I have is that in my sub_category_id field on the post table, remember the values are stored in the format [1][2][3] and I need to check the value against the subcategory field id.
I have tried the following:
"SELECT sub_categories.title FROM sub_categories, posts WHERE (category_id LIKE '%[{$row['id']}]%') AND (sub_category_id LIKE '%[sub_categories.id]%') LIMIT 0,100"
But it didn't work. I don't know whether there is an error in my query or whether it even SHOULD work, but I would be grateful if anyone could tell me how to do it or where I am going wrong in my code!
NB. I am trying to find which sub_categories appear in which categories based on people tagging them in a post together.