Re: OT mysql pivot table problem

[Ashley Sheridan <ash@xxxxxxxxxxxxxxxxxxxx>]

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


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php