SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Setting up database: Concert listings

    I have built a simple concert listing database, however we have decided to make it a little more complex. For every concert, we currently have the following:

    Date
    Title (i.e. "New Years Extravaganza")
    all the bands (diffentiate between headliners and openers)
    Which stage (we have two)
    Ticket price
    Time
    Promoter (What concert company put the show together)
    Status (cancelled, sold out, nothing)
    Purchase method (ticketmaster or window only)

    All of this works perfectly for us right now. We just manually type in the names of the bands for the headliners and openers each show. However, we want to add the following:

    Each bands website next to their name (or name links to it)
    Another page that lists all the bands that have played at our venue, and also be able to list every concert they have played here.

    I thought of making a table for bands with:

    id
    name
    url

    The problem I am running into is that I don't know how to associate a band with a show. I could have the following:

    band1_id
    band1_type
    band2_id
    band2_type
    band3_id
    band3_type
    etc...

    (_type is there because I need to differentiate between headliners and opener)

    However, some shows will have only 4 bands while other, special events, will have as many as 20!

    Hopefully this all makes sense to you. If so, how could I organize the database to get this situation squared away?
    Then, in the concert table

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't you think a third table would be great?

    *** concert_band
    concert_id
    band_id
    type
    order

    Example
    concert_id, band_id, type, order
    123, 55, 1, 3
    123, 66, 2, 2
    123, 77, 2, 1
    456, 55, 2, 1
    456, 66, 1, 2

    where type 1 = headliners and 2 = openers
    If band 55=Anthrax, 66=Virus and 77=Tinnitus, then order in which they play on the event with id 123 is
    [openers]
    Tinnitus
    Virus
    [headliners]
    Anthrax

  3. #3
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, so let me see if I can get this right here...

    ~~~~~~~~~~~~~~~~~~
    TABLE SETUP

    BANDS

    id
    name
    url

    CONCERTS

    id
    datetime
    title
    stage
    price
    status
    promoter
    ticket_method

    CONCERT_BANDS

    concert_id
    band_id
    type
    order
    ~~~~~~~~~~~~~~~~~~

    Now, when I wanted to display the concerts, how would I go about performing the query? The only way I know how to query would involve one main query to get all the concerts, then for each concert a query to pull from CONCERT_BANDS and then a query for each band. That seems like way too much and I'm sure there is a much better way to do it.

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  6. #6
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not getting this multiple table SELECT thing AT ALL. I have the following structure:

    BANDS:

    band_id int(100) NOT NULL auto_increment,
    name varchar(255) NOT NULL default '',
    url varchar(255) default NULL,

    EVENTS:

    event_id int(100) NOT NULL auto_increment,
    datetime datetime NOT NULL default '0000-00-00 00:00:00',
    title varchar(255) NOT NULL default '',
    stage varchar(25) NOT NULL default '',
    price varchar(30) NOT NULL default '',
    status tinyint(1) NOT NULL default '0',
    promotor tinyint(1) NOT NULL default '0', // promoters are identified by an id
    ticket_method tinyint(1) NOT NULL default '0' // 0 = no online sales, 1 = TicketMaster

    EVENT_BANDS:

    event_id int(100) NOT NULL default '0',
    band_id int(100) NOT NULL default '0',
    type tinyint(1) NOT NULL default '0', // 1 = Headliner, 0 = Opener
    order_number tinyint(4) NOT NULL default '0'


    How the heck do I do the SELECT statement? I have looked at all the JOIN stuff and it just doesn't make sense. The following is what I have put together for the query:

    SELECT *, DATE_FORMAT(e.datetime, '%W, %M %D') AS date, DATE_FORMAT(e.datetime, '%l:%i %p') AS time FROM events AS e, event_bands AS eb, bands AS b WHERE e.event_id = eb.event_id AND b.band_id = eb.band_id ORDER BY e.datetime DESC


    This isn't right because I only have one event in the test database, but I get 4 displayed. Even if I did get this right, how would I access the right information from the array? What part of the array would have the band info?

    So lost

  7. #7
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This isn't right because I only have one event in the test database, but I get 4 displayed
    That's because the result is something like
    event_id, datetime, ..., band_id, name, date, time
    1, 2002-10-06 20:00, ..., 101, AAA, Sunday, Oct 6th, 8:00PM
    1, 2002-10-06 20:00, ..., 102, BBB, Sunday, Oct 6th, 8:00PM
    1, 2002-10-06 20:00, ..., 103, CCC, Sunday, Oct 6th, 8:00PM
    1, 2002-10-06 20:00, ..., 104, DDD, Sunday, Oct 6th, 8:00PM

    You have to check in the php code, for each new row, if the event_id is the same as in the previous row.
    If it is => just print out the band info
    If it's not => end this row and begin a new row (in the html table or what it is you are printing)

    Yes, you get a lot of data that you don't use in the page, but one single query is better than multiple "select bands" for each event

    Recommendation 1; change the query from
    SELECT *, DATE_FORMAT(e.datetime, '%W, %M %D') AS date, DATE_FORMAT(e.datetime, '%l:%i %p') AS time ...
    to
    SELECT event_id, title, stage, ... DATE_FORMAT(e.datetime, '%W, %M %D') AS date, DATE_FORMAT(e.datetime, '%l:%i %p') AS time ...
    (i.e. don't use * if you don't need all fields)
    NB! if you use *, you get multiple event_id and band_id columns - that's really redundant!

    Recommendation 2; change
    ... FROM events AS e, event_bands AS eb, bands AS b
    WHERE e.event_id = eb.event_id AND b.band_id = eb.band_id ...
    to
    ... FROM events AS e
    LEFT JOIN event_bands AS eb ON e.event_id = eb.event_id
    LEFT JOIN bands AS b ON eb.band_id = b.band_id ...
    (i.e. don't use WHERE for joins)

    LEFT JOIN => if band_id == null, there is no band for this event (yet)
    Last edited by jofa; Oct 5, 2002 at 17:54.

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One last thing:
    ORDER BY e.datetime DESC, eb.order_number ASC

    Then you will get the bands in the correct order on each date

  9. #9
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I understand the SELECT statement with the JOIN and stuff...thanks

    However, now my problem is display. The bands are in the middle of a table. I can't "just print out the band info" if it is the same event_id.

    http://www.somasandiego.com/main.php?p=events2.php

    That is what it looks like, so you can get an idea of the table layout. I made a second event, both events have 4 bands (so 8 are being displayed right now).

    jofa, thanks for the help, MUCH APPRECIATED

  10. #10
    SitePoint Member
    Join Date
    May 2002
    Location
    Annapolis, Md
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey sccr410,

    No one has mentioned it yet, but what is staring you in the face is a classic "many to many" database relationship. There are "many" concerts with the possibility of "many" bands. You have done well in normalizing the structure. Now it's time to get at that data.

    Trying a "many to many" three table join is tough if you are not familiar with joins at all.

    One question on the EVENT_BANDS table, what is the order_number column for?

    Also, your query resulted in whats known as a cartesian join, basically you made every row(1) in each table join to every row in every other table. cartesian join = bad

    I'll try to get you started with a basic query. The thing with SQL is, when you have trouble, simplify the query. Just try to get some of the data you want even if it is not formatted right. Once that is in place you can continue to add-on to the statement, format your dates, and do all that fun stuff.

    Also, practice through a console or through PHPMyAdmin front end instead of through code. That way you can bang out a bunch of SQL statements until you get the right one.

    Below I will assume you are wanting to list out the info for event id "1". This should select all of the proper information for that event.

    Code:
    SELECT * FROM BANDS, EVENTS, EVENT_BANDS
    WHERE EVENTS.event_id = 1 
    AND EVENT_BANDS.event_id = EVENTS.event_id 
    AND EVENT_BANDS.band_id = BANDS.band_id
    Do you see the difference here? We are first selecting the event we want to show. Then we are selecting everything from EVENT_BANDS where the event_id matches the event_id from the EVENTS table. This has joined those two tables at the proper places. Now we need to get the band info by joining EVENT_BANDS.band_id to the BANDS.band_id column.

    My suggestion if you want to output the contents of all the shows is to have two "views". On the first page just loop through all the shows and print one line that displays the show name, date, etc, then a "View Details" button could pass the event_id to the next page that shows the extended band information from the SQL statement above.

    If you really want to display all events and all bands for each event on the same page you would have to get a bit more fancy with the SQL statement using (I believe) GROUP BY's on the event_id's. Or multiple queries in a while loop if you want to be inefficient

    Let us know how this works out for you.

    Good Luck!

    Off to for now....

  11. #11
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.somasandiego.com/main.php?p=events2.php

    WOOT! It looks just like it is supposed to. However, I have a feeling there is still a much better way of doing the display than how I did it.

  12. #12
    jigga jigga what? slider's Avatar
    Join Date
    Oct 2002
    Location
    Utah (USA)
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by exodigital
    If you really want to display all events and all bands for each event on the same page you would have to get a bit more fancy with the SQL statement using (I believe) GROUP BY's on the event_id's. Or multiple queries in a while loop if you want to be inefficient
    I don't think this would be inefficient.

    You run one master query to pull all the event information you want to look at (IE future events, events matching certain criteria, whatever). You set up a while loop to loop through this query's results. The first thing you do inside that loop is run a tertiary query that then pulls the band info for that event (using the known eventID for that event), and use another, nested loop to display that info.

    Logical, easy, straight-forward, and IMO very effecient. The first query queries only the main event table. The second query joins the two remaining tables and queries those with a known eventID (not querying the main event table again).

    You could also do this with a single query, not using GROUP BY by simply only displaying the event information the one time. Your dataset may have that information for each "record" now, but that doesn't mean you have to display it. This is, IMO, less effecient, as you have a larger data return (IE repeated data).

    Quite honestly though, the effeciency of this small database is not going to be much of a concern until you get hundreds of thousands of records in the database. So I would recommend you do it the way that seems the most logical to you and will make for the easiest coding (as well as upkeep on the code). Both methods have their trade-offs.
    $slider = 'n00b';

  13. #13
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by exodigital
    One question on the EVENT_BANDS table, what is the order_number column for?
    Wild guess: make bands that play on the same event appear in the correct order?
    Originally posted by exodigital

    Code:
    SELECT * FROM BANDS, EVENTS, EVENT_BANDS
    WHERE EVENTS.event_id = 1 
    AND EVENT_BANDS.event_id = EVENTS.event_id 
    AND EVENT_BANDS.band_id = BANDS.band_id
    No no no, never use WHERE for JOIN conditions
    You can do it, but
    select x, y, z
    from a inner join b on a.id = b.parent_id
    where a.x=123
    is more readable than
    select x, y, z
    from a, b
    where a.id = b.parent_id and a.x=123

  14. #14
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by sccr410
    WOOT! It looks just like it is supposed to. However, I have a feeling there is still a much better way of doing the display than how I did it.
    How did you do it?

    Maybe a better method: two queries, one for all events and one for all event_id:s and bands, and then use array_filter to get bands for each event
    Last edited by jofa; Oct 6, 2002 at 14:26.

  15. #15
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by exodigital
    Also, your query resulted in whats known as a cartesian join, basically you made every row(1) in each table join to every row in every other table. cartesian join = bad
    No, it's not a cartesian join

    table A
    x
    1
    2
    3

    table B
    y
    2
    3
    4

    full join, AxB (cartesian product);
    select * from A, B
    1, 2
    2, 2
    3, 2
    1, 3
    2, 3
    3, 3
    1, 4
    2, 4
    3, 4

    left join;
    select * from A left join B on A.x = B.y
    1, null
    2, 2
    3, 3
    Last edited by jofa; Oct 6, 2002 at 20:22.

  16. #16
    jigga jigga what? slider's Avatar
    Join Date
    Oct 2002
    Location
    Utah (USA)
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jofa
    Maybe a better method: two queries, one for all events and one for all event_id:s and bands, and then use array_filter to get bands for each event
    That's what I recommend also (see above reply).
    $slider = 'n00b';

  17. #17
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wooosh! <---sound of this discussion going over my head

    Anyway, the way it is now, I get all the information in one query. I set the current event_id to a variable that is used the next time around the while loop. If the current event_id is different than the previous, then I go ahead and begin displaying the row. Then, display the current band that is associated with that array regardless. Then, another check to see if the current event_id is the same as the last...if not, then display the finishing of the row. If it is the same, it skips the display of the row and does the band name display that always happens regardless. TADA! It works, however, it seems fairly inefficient to me and there is probably a much better way of doing this.

  18. #18
    SitePoint Member
    Join Date
    May 2002
    Location
    Annapolis, Md
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One question on the EVENT_BANDS table, what is the order_number column for?
    Wild guess: make bands that play on the same event appear in the correct order?
    I was assuming that but, how would this work? By listing multiple band_id's in order in the same column? Is there a way to use this order_number field properly? The only way I can see how is with a few other tables listing the event, a band, and a time or order number they go on. Anyway, I'm going off on a minor detail for now.

    Also, is it bad practice/inefficient to use a where clause for inner joins? Or is it just a readability issue?

    How is it going now sccr410? Any luck?

  19. #19
    jigga jigga what? slider's Avatar
    Join Date
    Oct 2002
    Location
    Utah (USA)
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by sccr410
    wooosh! <---sound of this discussion going over my head.
    Look over this code and then read through some of the replies again. Try to get past the fluff that a lot of people have posted as there's some good solutions for you in a few of the replies but a lot of stuff that'll get you off track.

    PHP Code:
    $today getdate();
    $eventQuery mysql_query("SELECT * FROM tblEvent WHERE event_date >= '$today' ORDER BY event_date");

    while (
    $eventRow mysql_fetch_array($eventQuery)) {
      
    $eventID $eventRow['event_id'];
      
    $venueQuery mysql_query("SELECT * from tblEventBands WHERE event_id = '$eventID' ORDER BY order_number");
      echo 
    '...'//formatted event fields here

      
    while ($venueRow mysql_fetch_array($venueQuery)) {
        echo 
    '...'//formatted venue fields here
        
    }
      } 
    Last edited by slider; Oct 7, 2002 at 09:10.
    $slider = 'n00b';

  20. #20
    jigga jigga what? slider's Avatar
    Join Date
    Oct 2002
    Location
    Utah (USA)
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I forgot to do the join in that second query to get the band information.

    Code:
    SELECT * from tblEventBands 
    INNER JOIN tblBand on tblEventBands.band_id = tblBand.band_id 
    WHERE tblEventBands.event_id = '$eventID' 
    ORDER BY tblEventBands.order_number;
    I think that code is correct.
    $slider = 'n00b';

  21. #21
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So say we have 30 upcoming events (which is very likely). With that code, it would mean 31 queries (one main query for all the events and 1 query for each event). Is that good practice?

  22. #22
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  23. #23
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by exodigital
    I was assuming that ["make bands that play on the same event appear in the correct order"] but, how would this work? By listing multiple band_id's in order in the same column? Is there a way to use this order_number field properly?
    Yes, listing multiple bands (if they play at more than one event) is how it would work

    Let's take a simple example; a table with persons
    select p1.firstname as parent, coalesce(p2.firstname, '(no info)') as children
    from person p1 left outer join person p2 on p1.id = p2.parent_id
    order by p1.birth_year, p2.birth_year

    Possible result:
    parent,children
    Adam,Cain
    Adam,Abel
    Adam,Seth
    Cain,Enoch
    Abel,(no info)
    Seth,Enos
    Enos,(no info)
    Enoch,(no info)

    (The father-child relation is similar to the band-event relation, birth_year comparable to order_number)
    Anyway, we get three rows with Adam as parent
    Is this bad or good? I think it's OK ))
    You use one query (instead of one to get all persons, and then another one for each person to get the children => n + 1 queries), and the code on the client side for displaying the result isn't that difficult (use a variable for the previous parent and check for every new row if the current parent is the same as the previous, just like sccr410 did)

    Originally posted by exodigital
    Also, is it bad practice/inefficient to use a where clause for inner joins?
    It's bad practise
    And you can't do an outer join with "where"
    (maybe you want to change the inner join to an outer, then it's easier to do the change if you use "inner join ... on ..." from the start)

    Example; this query will only return persons with children:
    select p1.firstname as parent, coalesce(p2.firstname, '(no info)') as children
    from person p1, person p2 where p1.id = p2.parent_id
    order by p1.birth_year, p2.birth_year
    Last edited by jofa; Oct 7, 2002 at 15:51.

  24. #24
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select x, y, z
    from a inner join b on a.id = b.parent_id
    where a.x=123

    or

    select x, y, z
    from a, b
    where a.id = b.parent_id and a.x=123
    In mysql, which one is more efficient or better performance?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •