Thanks to this forum I’ve learned to create a table that combines the PK’s from two tables to create a many to many relationship. My case is an events table, a categories table and an events_cat_rel table (PK’s are event_id and cat_id).
Here is my DB query and results process:
$q = "SELECT * FROM event_cat_rel INNER JOIN events USING (event_id) INNER JOIN categories USING (cat_id) WHERE event_date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY event_date";
$r = @mysqli_query($dbc, $q); // Run the query.
// Count the number of returned rows:
$num = mysqli_num_rows($r);
if ($num > 0) { // If it ran OK...
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
echo $row['event_id'];
}
}
This worked fine when I was displaying events and they only had one category, but when there is more than one category it displays he same event each time the ID is found in the table. Instead, I would like to display the event information, and list the categories the event is related to.
SELECT e.name, GROUP_CONCAT(c.name)
FROM `events` e
LEFT OUTER JOIN event_cat ec ON e.id = ec.event_id
LEFT OUTER JOIN cats c ON c.id = cat_id
GROUP BY e.name
name GROUP_CONCAT(c.name)
event1 cat1,cat2,cat3
event2 cat2,cat4
Thanks for the response, hash. This looks so new to me, I can’t follow the example. I’m not sure when you’re using names from the database and when you’re creating new names. Could you dumb it down for a beginner? Don’t hold back!
try like this, these are your table/column names –
SELECT events.name
, events.event_date
, GROUP_CONCAT(categories.name) AS categories
FROM events
LEFT OUTER
JOIN event_cat_rel
ON event_cat_rel.event_id = events.event_id
LEFT OUTER
JOIN categories
ON categories.cat_id = event_cat_rel.cat_id
WHERE events.event_date > CURRENT_DATE - INTERVAL 1 DAY
GROUP
BY events.name
ORDER
BY events.event_date
i had to guess only for events.name and categories.name
Heh, sorry, a bit lazy there. As far as creating new names, it’s just aliases to make it shorter to type, these are all the same, AS is optional.
SELECT e.name FROM events AS e
SELECT e.name FROM events e
SELECT events.name FROM events
A bit more explanation on how to build this query, I’ll use Rudy’s code as it’s probably close to your db. Open up phpmyadmin and copy paste these to see what happens.
First you want all events, as the goal is to display events with some extra data.
SELECT events.name
FROM events
WHERE events.event_date > CURRENT_DATE - INTERVAL 1 DAY
name
------
Triathalon
Marathon
Next you want all categories for each event from the relationships table. The join will create new rows for each additional category an event has.
SELECT events.name
, event_cat_rel.cat_id
FROM events
LEFT OUTER
JOIN event_cat_rel
ON event_cat_rel.event_id = events.event_id
WHERE events.event_date > CURRENT_DATE - INTERVAL 1 DAY
cat_id isn’t much use by itself though, so you can grab the category name from the categories table with another join.
SELECT events.name, event_cat_rel.cat_id
, categories.name
FROM events
LEFT OUTER
JOIN event_cat_rel
ON event_cat_rel.event_id = events.event_id
LEFT OUTER
JOIN categories
ON categories.cat_id = event_cat_rel.cat_id
WHERE events.event_date > CURRENT_DATE - INTERVAL 1 DAY
name cat_id name
--------- --------- --------
Triathalon 1 Running
Triathalon 2 Swimming
Triathalon 3 Cycling
Marathon 1 Running
Now that’s almost what you want, but you just want one row per event with the categories in a list, so you use GROUP_CONCAT to join them together. You can remove the cat_id as it is not needed and will just complicate things. This is similar to grouping counts, if you’re familiar with that, SELECT age, count(age) FROM table GROUP BY age
SELECT events.name
, GROUP_CONCAT(categories.name) AS categories
FROM events
LEFT OUTER
JOIN event_cat_rel
ON event_cat_rel.event_id = events.event_id
LEFT OUTER
JOIN categories
ON categories.cat_id = event_cat_rel.cat_id
WHERE events.event_date > CURRENT_DATE - INTERVAL 1 DAY
GROUP
BY events.name
name categories
--------- -----------
Triathalon Running, Swimming, Cycling
Marathon Running
Hope that helps, I guess main point is you can build more complex queries bit by bit starting simple. You can google for plenty of JOIN tutorials, and check the mysql manual for more info on different grouping functions.
and isn’t it interesting how many threads started in the php forum end up being a mysql problem, and conversely how many threads started in the mysql forum end up being a php problem
Hash, first, thank you for believing that people can learn how to do this as you did and produce great web sites. And thank you for putting together more than a detailed example, but an example of your process in problem solving.
Thank you rudy, for your fast and accurate code samples. It’s so cool of both of you.
I’ve been using the example, step by step, and I ran into a snag. The list of events returned is combining events if they have the same name. My event calendar often has events with the same event name, but will have a different date (and event_id). The code sample combines events when they have the same name.
How would I generate a list of events with unique event id’s?
Changing it to GROUP BY event_id certainly did the trick, thanks. I’ve never used LEFT OUTER joins before, so it is great to learn how you used it to grab what you wanted, from which table, and combine them with like names in other tables.
I need to determine the cat_id’s for each cat_name in the categories group. I am using the the cat_id’s to create a link to all events under that cat_id. When I only had one category per event, this was easy! The code currently serves up the first cat_id in the group of categories. How can I separate them to give me the cat_id for each category in the group?
That’s true, I added the event_cat_rel.cat_id to the SELECT, but I’ll try adding categories.cat_id to the GROUP_CONCAT to grab it for each event.
How could I then tell the script to output the cat_id and the cat_name as separate information? When I’m requesting the result for GROUP categories my code looks like this:
But $row[‘$cat_id’] only gives me the first cat_id for the link. I might need some PHP to separate the cat_id’s from the cat_names. And I say might because I don’t know what it would be!
Then we use a foreach to loop through that array. Exploding again makes another array, and we use list() to assign it. Play around, I’m sure you’ll figure out how it works =D
SELECT events.name
, GROUP_CONCAT(CONCAT(categories.cat_id,'=',categories.name)) AS categories
FROM events
LEFT OUTER
JOIN event_cat_rel
ON event_cat_rel.event_id = events.event_id
LEFT OUTER
JOIN categories
ON categories.cat_id = event_cat_rel.cat_id
WHERE events.event_date > CURRENT_DATE - INTERVAL 1 DAY
GROUP
BY events.name
You will also want to make sure your only exploding up to the first = in case the category itself contains an = character.
Remember though that GROUP_CONCAT() truncates anything beyond 1024 characters.
Pretty easy, indeed. I couldn’t have done it without you! Thanks for the help.
I’m now working on a way to INSERT the selection of multiple categories into the database. I’ve got a form with checkboxes next to the list of category names. The checked cat’s will need to be inserted into the event_cat_rel table. Not sure how I’ll get it, but will post here when in trouble.
My first question is which input type to use for the user. The user will need to select multiple categories. When the user could select only one category, the input was a drop-down list of cat_name’s with a value array of integers for the cat_id. Now that they will choose multiple categories, I could either use multiple drop-down lists (list1=category choice 1, list2=category choice 2, …) or a series of check boxes for all cat_name’s where the user can check (or uncheck) the cat_name’s they want.
Any preference of what will be easier to add to the INSERT query?
That’s actually how I do it for anything moderately difficult 
- step by step, make sure each gives the correct result.