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. =C2=A0I'm going to take=
the
> advice given by you and others and simply do this in php instead of looki=
ng
> for a fancy mysql solution. =C2=A0; ) =C2=A0Dang, and I was really wantin=
g 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! =C2=A0I need some pointers on how to pivot a mysql column
>> (containing
>> > comma delimited data) into an equal number of rows (see example). =C2=
=A0Any
>> > 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 =3D "SELECT * FROM `user`";
>> $result =3D mysql-query($query);
>> while($row =3D mysql-fetch-array($result))
>> {
>> =C2=A0 =C2=A0$uid =3D $row['uid'];
>> =C2=A0 =C2=A0$groups =3D explode(','$row['groups']);
>> =C2=A0 =C2=A0for($i=3D0; $i<count($groups); $i++)
>> =C2=A0 =C2=A0{
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0$query2 =3D "INSERT INTO `pivot` VALUES($uid,=
$groups[$i])";
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0$result2 =3D mysql-query($query2);
>> =C2=A0 =C2=A0}
>> }
>>
>> 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
>>
>>
>
>
>