Welcome Guest, Not a member yet? Register   Sign In
Need Help With Form Submission
#11

[eluser]vincej[/eluser]
Read you - At the at the time of the initial presentation of the dates ie before the amendments are made, a SELECT is done of the DB with a GROUP_CONCT So that the dates can be pivoted from columns to rows. Does it not follow that if I am to keep track of the individual puid's then I need to get rid of the group Group_Concat as well. If I do then I need to figure out how to pivot the data. Here is the original SELECT :


Code:
SELECT `location`, lo.locationid, GROUP_CONCAT( pd.dates
ORDER BY pd.dates ) AS TheDates
FROM locations AS lo
LEFT JOIN pudates AS pd ON lo.locationid = pd.locationid
GROUP BY lo.location
LIMIT 0 , 30
#12

[eluser]CroNiX[/eluser]
Probably just need to add something to your select() like:
Code:
GROUP_CONCAT( pd.guid ORDER BY pd.dates ) AS TheGUIDs
so you get them in the same order as the dates and then retrieve them the same way you are your dates in your form creation loop
#13

[eluser]vincej[/eluser]
Hi CroxNix - I am embarrassed to say that after trying umpteen different permutations I still can not make my query work. Yes, I know that this is not CI but none of my sources know how to make a double group_concat work with a join.

What I have got doesn't work properly. I get all the dates, all the PUIDs adn only 1 location:

Code:
ThePUID  TheDates  location
5,1,2,6,3,7,4,8  1331704800,1331704800,1333704800,1334704800,133570...  Collingwood

My (faulty ) query so far is:

Code:
SELECT
GROUP_CONCAT( pudates.puid
order by pudates.dates asc) AS ThePUID,
GROUP_CONCAT( pudates.dates order by pudates.dates asc )
AS TheDates, locations.location
FROM pudates, locations
where  locations.locationid = pudates.locationid;


could you please be so kind as to help out here ?

Many thanks !
#14

[eluser]vincej[/eluser]
HI again - It Looks Like I have success!!

It's a been a pig though ! I resorted to my old fashioned 'where' clauses rather than the more current joins ( yes I am that old ! ) and it all fell into place :o)

check it out !

Code:
SELECT group_concat(p.puid ORDER BY p.dates) AS ID, location, l.locationid, group_concat(p.dates ORDER BY p.dates) as Dates
FROM locations l, pudates p
WHERE l.locationid = p.locationid
GROUP BY  location;

Many Thanks !!!!




Theme © iAndrew 2016 - Forum software by © MyBB