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@xxxxxxxx> 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@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
> >
>
Consider my wrists firmly slapped!
Thanks
Ash
www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
