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
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
