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
