[SOLVED] Multiple queries from one row in database ordered by date

[cclark40]

Hi Folks,

Hoping to solve the following problem.

I have a database table for croquet tournaments.  The table has many fields and around 60 data rows.  There is a single row for each club's tournament.  Each tournament has up to five divisions and some of the tournaments run their separate divisions on different dates (sometimes months apart).  I want to extract each separate divisional event from the database and then display them in date order...... 

 

So for example the table fields I want to use are

tournamentid, tournamentname, tournamenthostclub, event1startdate, event1enddate, event1lowrange,  event1highrange,  event2startdate, event2enddate, event2lowrange,  event2highrange,  event3startdate, event3enddate, event3lowrange,  event3highrange etc.

 

and what I want to display is a list of events ordered by startdate (so if a row has an event1, event2 and event3 they show as separate lines and in the correct date order - they may well need be interspersed with other club's events e.g. Club A may have event1 on 5th Jan and event2 on 8th Jun where club B may have event 1 on 5th Feb so the display order would be Club A event 1, Club B event 1, Club A event 2)

 

tournamentid, tournamentname, tournamenthostclub, eventstartdate, eventenddate, eventlowrange,  eventhighrange

 

I am not sure whether this can be achieved in the SELECT sql statement or whether it needs some later manipulation?

 

Hope this makes sense - bit hard to explain.....

Website is http://www.croquetvic.asn.au    Click on club finder and then select Wiliamstown may give a small idea of the sort of data.  I am basically trying to construct a tournament calender drawn in from the database....

 

Many Thanks

[discomatt]

Well, I think you're doing this the hard way. Personally, I'd have a tournaments table and a child events table, then you could simply select events order by date.

 

I think the only way you're going to do this is to create a temporary table, with a row per event, and then order by startdate. Pretty much the solution above, only on-the-fly.

[cclark40]

Hi Matt,

Thanks for your response.  I certainly agree in principal but I have been thinking about it and can see a few problems..

1. I am working with an ISP mysql database via a control-panel interface and can't see how to link tables or make child tables etc.

2. Not sure what you mean by "temporary table" do you mean in PHP or back in the database?

3. Some clubs host multiple tournaments so would need some way to link the events to the correct tournament

 

[discomatt]

Easy enough here

 

2. This would be entirely in the database. I'm going to avoid explaining this until you're sure you don't want to go the other route -> it's much more complicated.

 

1,3.

 

You'd do something like this

 

TOURNAMENTS table would have the rows

id, name, hostclub

 

EVENTS table would have the rows

id, tid, startdate, enddate, lowrange, highrange

 

'id' in both tables will be a primary key, auto_increment and unique.

 

Now, to add a tournament, you'll use a query like this

 

INSERT INTO `tournaments` ('name', 'hostclub') VALUES ('Matts Tourney', 'FooBar Group')

 

You can then use mysql_insert_id() in PHP to grab the unique id that was generated and add events like this

 

INSERT INTO `events` ('tid', 'startdate', 'enddate'... ect ) VALUES ( mysql_insert_id(), 'some date', 'another date'... ect )

 

The idea here is that events.tid will equal the tournament.id that it's part of. You can then use a select/join query to grab the tournament names for each event

 

If you still don't follow me, let me know

[cclark40]

Thanks Matt,

I am full of the flu at them moment so it will take a while for my partially-functioning brain to digest that but it seems pretty logical on the first look!!

Colin

[cclark40]

OK - I've created the tables.

 

At this stage I am only using UPDATE rather than INSERT as my users are all little old ladies who are not very tech-savvy.  Letting them with insert could lead to all sorts of mayhem so best to populate the database myself and then just let them modify the data!  Presumably this doesn't change much except I need to insert 5 events in the events table for each 1 in the tournament table?  Obviously the SELECT statement can exclude the null entries on the display page but all 5 events need to appear on the password protected update page.