Re: OT mysql pivot table problem
| From: | Ashley Sheridan | Date: | Thu, 25 Jun 2009 19:51:48 +0000 |
| Subject: | Re: OT mysql pivot table problem | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-294609@lists.php.net to get a copy of this message | ||
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