Re: OT mysql pivot table problem

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 >