vincej Posted March 14, 2012 Share Posted March 14, 2012 Hi - I have spent 3 days struggling with this and have gone through several approaches and I continue to fail miserabley what I want to achieve is an HTML table which looks like this: LOCATION DATE 1 DATE 2 DATE 3 New York April 1 May 1 June 1 Chicago April 2 May 2 June 2 Atlanta April 3 May 3 June 3 Note: the user can not be in two places at one - so there is no issue with regards to doubling dates. EG: April 1st Means New York full stop. Note: the dates move further into the future as you progress in the columns. I have 2 tables, 'Locations' and 'Dates' joined via a FK 'LocationID' therefore NewYork has locationID '2', April 1st also has the FK of locationid '2' The Problem: I have 2 problems: 1 - The dates are within their table recorded vertically - this allows me to search them by order and time - very important. However I need them presented horizontally ! 2 - The appropriate date MUST be 'attached' to the appropriate city ie May 2nd MUST be in the Chicago row AND it must sequential under heading 'Date 2'. I have tried building my DB tables the other way so that dates are recorded horizontally across columns but that gave real insurmountable problems in trying to filter them. There is a place in heaven for the person who might be able to shine some light on this. I have googled the problem and Mysql ( 5.5 ) does not have any PIVOT feature. Also the proposed solutions where either horrendously complicated or relied upon being able to uniquely ID the dates in some way. My dates are unpredictable. My most recent Query below, gets all the dates and locations OK, but they are orientated vertically - FAIL SELECT locations.location, locations.locationid,pudates.dates FROM locations JOIN pudates ON locations.locationid = pudates.locationid GROUP By locations.Locationid ASC, DATES asc; MANY THANKS !! Vincej Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 15, 2012 Share Posted March 15, 2012 is there a limit to the number of dates you will be returning? Link to comment Share on other sites More sharing options...
vincej Posted March 15, 2012 Author Share Posted March 15, 2012 Hi ! Thanks for taking an interest in my problem. I'm really struggling. There is only going to be 4 dates presented in the html table. Of course as time moves forward the user will update past dates to future dates impacting the DB. I have tried a few days ago to store my dates horizontally in the DB under column names as 'Date1', 'Date2' etc. - it made creating the HTML table a breeze but it made filtering past and future dates and presenting them in an HTML drop down a nightmare. A drop down of *only* future dates is a feature needed elsewhere in the site. Many Many THANKS ! Vincej Link to comment Share on other sites More sharing options...
mikosiko Posted March 15, 2012 Share Posted March 15, 2012 if you post your tables, some real data covering all your scenarios and expected results sure somebody could be give a more accurate answer... seems that your are looking to produce a PIVOT (or Crosstab) report... for that there are tons of examples if you google for them, all with different degrees of complexity... you must find the one that serve your goals best and test/implement it. in the meantime and having in mind only the information available (therefore the solution could not be exactly what your need) you could try this... sure it could give you more than one idea to solve your issue SELECT lo.location, GROUP_CONCAT(pd.dates ORDER BY pd.dates) AS TheDates FROM locations AS lo LEFT JOIN pudates AS pd ON lo.id = pd.locationid GROUP BY lo.location with this output and post process in php you should be able to get the right display Note: pay attention to the GROUP_CONCAT() limitations ... group_concat_max_len ... default 1024 but can be adjusted Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 15, 2012 Share Posted March 15, 2012 another thing we will need to know is how you are defining which 4 dates are shown out of all the dates in the table that will match each location id. Link to comment Share on other sites More sharing options...
vincej Posted March 15, 2012 Author Share Posted March 15, 2012 HI Guys - Thanks so much for helping out. Adding to the previous info on presentation, in fact I have 2 requirements for presentation: In the *administrators* section of the site, I have to present the 4 dates in an HTML table as featured in previous post. There will only ever be 4 dates, but there might be a gizzilion locations. For the sake of completeness I have added a 4th date to the illustration table below. When the site goes live those 4 dates for each location will all be in the future. However, as time passes, the closest date will inevitably go into the past, leaving 3 future dates. The resulting presentation might look like the illustration table below. At some point the user will go into the Admin suite and update his HTML table eradicating the historical date with a new date also in the future. It is not a necessary requirement for the dates to 'shunt down' to new slots. In terms of defining which dates are show. If the user is maintaining his dates properly, he should only have 4 dates per location in the DB. One or two might be historical dates, but he should not be accumulating more than 4 per location. LOCATION DATE 1 DATE 2 DATE 3 DATE 4 New York March 1 May 1 June 1 July 1 Chicago March 2 May 2 June 2 July 2 Atlanta March 3 May 3 June 3 July 3 In the pubic side of the site I have to present only *future* dates in an HTML drop down. I do not want to include historical dates in the drop down. Therefore I need to filter the past and future dates relative to $Now = time(). I hope this clarifies things, as I am quite lost. MANY MANY THANKS ! Vincej Link to comment Share on other sites More sharing options...
mikosiko Posted March 15, 2012 Share Posted March 15, 2012 did you test the provided query? Link to comment Share on other sites More sharing options...
vincej Posted March 15, 2012 Author Share Posted March 15, 2012 hi Mikosiko, Not yet - but will do .. and will report back, MANY THANKS ! Link to comment Share on other sites More sharing options...
vincej Posted March 15, 2012 Author Share Posted March 15, 2012 Hi Mikosiko - IT WORKS !! Unbelievable ... wow you are brilliant ! You made a good guess at the table name, which I slightly modified. Ok, what I am getting out is : location TheDates Banff 1321746641,1331668779 Canmore 1321746641,1331746641 Collingwood 1321746641,1331746641 Varsity 1321746641,1331746700 I 'Americanised' the location names in the previous illustration tables, but in fact these are Canadian locations Ok, so what I now have to do is get these horizontal values out of THEDates, so that I can populate my HTML table. Each HTML row needs to be like this: LOCATION DATE 1 DATE 2 DATE 3 DATE 4 New York March 1 May 1 June 1 July 1 Chicago March 2 May 2 June 2 July 2 Atlanta March 3 May 3 June 3 July 3 So I have to think of a way of puling each value out that is attributable to that specific location. Many Many Thanks Mikosiko !!! Link to comment Share on other sites More sharing options...
vincej Posted March 15, 2012 Author Share Posted March 15, 2012 HI Mikosiko .. Once again, I am in trouble. I'm getting data out as per my previous post, but I am struggling to get the concatenated string from TheDates into a meaningful format. I need to be able uniquely identify each date attributable to a location. In this way I will then be able to load my html table using perhaps a foreach statement. So far I have tried list() and explode(). I am not succeeding in seperating this concatenated dates. any other you might suggest I look at ?? Many thanks !! Link to comment Share on other sites More sharing options...
vincej Posted March 15, 2012 Author Share Posted March 15, 2012 this last part got solved on the pHP queries side of the house Many Thanks to everyone who dug me out of 3 days of torture !! Link to comment Share on other sites More sharing options...
Recommended Posts