Re: OT mysql pivot table problem

On Thu, 2009-06-25 at 16:17 -0400, Eddie Drapkin wrote: > You'd be much, much better off creating a query by concatenating ", > ($uid, $groups[$i])" into one huge insert query. > > YOU SHOULD NEVER, EVER EVER EVER EVER RUN QUERIES IN A LOOP! > > > On Thu, Jun 25, 2009 at 4:11 PM, Matt Giddings<mcgiddin@svsu.edu> wrote: > > Thanks for taking the time to provide an example. I'm going to take the > > advice given by you and others and simply do this in php instead of looking > > for a fancy mysql solution. ; ) Dang, and I was really wanting to wow > > myself today... > > Thanks again! > > Matt > > > > On Thu, Jun 25, 2009 at 3:51 PM, Ashley Sheridan > > <ash@ashleysheridan.co.uk>wrote: > > > >> On Thu, 2009-06-25 at 15:20 -0400, Matt Giddings wrote: > >> > I know this is the off topic (sorry), but it is a php project that I'm > >> > working on! I need some pointers on how to pivot a mysql column > >> (containing > >> > comma delimited data) into an equal number of rows (see example). Any > >> > direction (pointers to links, etc. would be appreciated). > >> > > >> > From this: > >> > > >> > user.table > >> > uid|name|groups > >> > 1|mcgiddin|1,4,7,10,12 > >> > > >> > > >> > To this: > >> > > >> > pivot.table > >> > uid|group > >> > 1|1 > >> > 1|4 > >> > 1|7 > >> > 1|10 > >> > 1|12 > >> > >> I don't know of any fancy ways of doing it just in MySQL, but if the > >> records are all as simple as that, something like this should do the > >> trick: > >> > >> $query = "SELECT * FROM user"; > >> $result = mysql_query($query); > >> while($row = mysql_fetch_array($result)) > >> { > >> $uid = $row['uid']; > >> $groups = explode(','$row['groups']); > >> for($i=0; $i<count($groups); $i++) > >> { > >> $query2 = "INSERT INTO pivot VALUES($uid, > >> $groups[$i])"; > >> $result2 = mysql_query($query2); > >> } > >> } > >> > >> Also, I'd recommend having some sort of auto increment value on that > >> pivot table, so you can manipulate specific rows at a later date. > >> > >> Thanks > >> Ash > >> www.ashleysheridan.co.uk > >> > >> > > > > > > -- > > Matt Giddings > > Web Programmer > > Information Technology Services > > Saginaw Valley State University > > Phone: 989.964.7247 > > > > http://www.svsu.edu > > > Consider my wrists firmly slapped! Thanks Ash www.ashleysheridan.co.uk