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@xxxxxxxxxxxxxxxxxxxx>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
