2

I am new-ish to SQL queries and I'm trying to write a query properly which displays an event, its information, and the categories to which it belongs.

I want a result which looks like this:

TITLE: Band Battle!

WHEN: 7:00pm

VENUE: Parkland School

CATEGORIES: Music, School, Public

In my example, I have an event with three categories and I'm having trouble displaying this properly. I can get it to display all information including ONE category, or repeat three times displaying one category for EACH repeated listing. I also notice that if I have the same row in two tables, it'll only display the one it loads last. Is there a way I can tell it which row cell to load?

I've done this successfully in the past by having multiple SQL queries, but I know that's not proper. I'd have a query which displays all items, and each time one displays, have another query go out and get associated data, and then another for that data, etc. I'd run several hundred queries on each page load!

I'm sure that's not the best approach, so I'm trying to write one query to handle everything. I think JOINS are the way to go, specifically inner joins.

The information I need to display is spread across a few tables: an Events table, which has info regarding the event, including IDs associating it to the Venues table and EventCategories list.

Here's my SQL query:

from
    `Events` e
        inner join
    `EventCategories` c
        on e.ID = c.EventID
        inner join 
    `Venues` v
        on e.ID = v.ID

In my PHP, I call each data cell like this:

$row["Title"]   or   $row["CategoryID"]

It works, but there are a couple problems. I can only display one category at a time. Also, if I call something like $row["image"], and I have this row in both my Events and Venue table, it displays the wrong one.

I don't get any error messages, but I think I know the problem and solution, and I just don't know how to write it.

I THINK I have to say which table from which to call when I put a variable down, like $row["v.Title"] will call from Venue (but it won't). I also think I have to take a variable like the $row["Categories"] and break it down in to components, but I can't seem to find any information on how to do that either.

Any help would be much appreciated!

Here's my data, showing relevant parts only:

EVENT
ID    Title         Time     Image      VenueID
20    Band Battle!  7:00pm   band.jpg   1

VENUES
ID    Address      Name             Image
1     123 Street   Parkland School  school.jpg

EVENT_CATEGORIES
ID    EventID    CategoryID
1     20         54
2     20         12
3     20         84

CATEGORIES
ID    Name
12    School
54    Music
84    Public

Not a duplicate: Can I concatenate multiple MySQL rows into one field?

This might not be the solution to my problem (though it may be a part of it) and it doesn't seem to address how to grab multiple pieces of information from another database, which is the main part of my question.

jkdev
  • 11,360
  • 15
  • 54
  • 77
Brendan
  • 107
  • 2
  • 13

1 Answers1

2

You can use aggregation and GROUP_CONCAT():

SELECT 
    e.title, 
    e.time as `when`, 
    v.name as venue, 
    GROUP_CONCAT(c.name) as categories
FROM 
    Events e
    INNER JOIN Venues v ON e.VenueID = v.ID
    INNER JOIN EventCategories ec ON ec.EventID = e.ID
    INNER JOIN Categories c ON c.ID = ec.CategoryID
GROUP BY e.title, e.time, v.name
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks GMB! I think this is on the right track but still doesn't completely solve it. I added my data into the question to clarify what I'm doing. – Brendan Sep 16 '19 at 19:26
  • Wow, you wrote the whole code for me perfectly! I tailored it to do exactly what I need and it's working great! THANK YOU VERY MUCH!! Not only did you provide a meaningful and helpful response, but I learned something. I now know how to use "AS" to assign a different name, and GROUP_CONCAT to merge data. I also have a better understanding of groups, inner joins, and this whole process. Thanks A LOT! – Brendan Sep 16 '19 at 20:09