PHP /MySQL - *-to-Many Relationships

Question

Howdy!

So, I understand how the relationships work in mysql but I'm having a hard time figuring out how its implemented in my code.

For example, say I have the 3 tables.

Table 1: users - user id, username, user city

Table 2: categories - category id, category name

Table 3: user_categories - user id, category id

If I were to query the database for every user that was in a particular city and list them out with the all of the categories they belong to... How would I do this? Would I need to loop through the results and do a separate query for each user, then list the results? Or, is there some magic query that will return a multidimensional array?

I believe the above would be many-to-many, correct me if I'm wrong....

EDIT In the user_categories table, a user can contain more than 1 category, I'm trying to figure out how to return all of them

Thanks!

Answer 1

You need to JOIN the tables.

If I were to query the database for every user that was in a particular city and list them out with the all of the categories they belong to

SELECT *
FROM users
INNER JOIN user_categories
  ON (user_id)
INNER JOIN categories
  ON (category_id)
WHERE ...

Answer 2

You could try:

SELECT u.user_id, u.username, u.user_city, c.category_id, c.category_name
FROM users u 
INNER JOIN user_categories uc ON u.user_id = uc.user_id
INNER JOIN categories c ON uc.category_id = c.category_id
WHERE u.user_city = 'Cityname';

I haven't tested this, and there might be a more efficient way to do it, but it should work.

If you are unfamiliar with joins in mysql, check this out.

Answer 3

You're absolutely right, it is a many-to-many query. And from what I understand, what you're looking for is the ability to have some kind of hierarchical result to display, meaning for one user, have an array of all the categories he's assigned to...

Couple of things you could do: Option 1: Query the users table:

SELECT u.user_id, u.username, u.user_city WHERE city = 'somecity';

From the results, get all the user_id's that match, put them in an array.

array(1,3,4,5)

Then execute a query by joining the 2 tables categories and user_categories, and passing the array as a comma separated list in a where in:

SELECT user_categories.user_id, categories.category_name
FROM user_categories INNER JOIN categories ON user_categories.category_id = categories.category_id
WHERE user_categories.user_id IN (1,3,4,5)

This will give you a list of user-id, category name that you can use in your script with the previous results to build your result set

option 2: my preferred, use MySQL's GROUP_CONCAT(http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat).

SELECT users.user_id, users.user_name, GROUP_CONCAT(categories.category_name) AS categories
FROM users
INNER JOIN user_categories ON users.id = users_categories.user_id
INNER JOIN categories ON user_categories.category_id = category.id
WHERE user.user_city = 'somecity'
GROUP BY user.user_id

This will return something like:

user_id       username      categories
1             u1            cat1, cat2, cat3
2             u2            cat1, cat3

You can specify the separator by using SEPARATOR in group_concat.