- Previous thread: [PHP] Does anyone know how gettext works under the hood?
- Next thread: [PHP] Removing empty values from array
- Threads sorted by date: php 200906
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
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
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
> 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
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
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 {
> $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
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
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 {
> $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
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 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
> 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> =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
>>
>>
>
>
> --
> 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
($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 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
> 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> =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
>>
>>
>
>
> --
> 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
On Thu, Jun 25, 2009 at 3:20 PM, 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 (co=
ntaining
> comma delimited data) into an equal number of rows (see example). =C2=A0A=
ny
> 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
>
The best performance I've seen for a SQL solution uses a tally table.
CREATE TABLE Tally (
N int not null,
PRIMARY KEY (N)
)
The table holds a sequence of numbers from 1 to some large number.
Then you can write a query something like this:
SELECT uid, SUBSTRING(user.table.groups,N+1,LOCATE(',',user.table.groups,N=
+1)-N-1)
FROM Tally, user.table
WHERE N < LENGTH(user.table.groups)
AND SUBSTRING(user.table.groups,N,1) =3D ','
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
> 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 (co=
ntaining
> comma delimited data) into an equal number of rows (see example). =C2=A0A=
ny
> 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
>
The best performance I've seen for a SQL solution uses a tally table.
CREATE TABLE Tally (
N int not null,
PRIMARY KEY (N)
)
The table holds a sequence of numbers from 1 to some large number.
Then you can write a query something like this:
SELECT uid, SUBSTRING(user.table.groups,N+1,LOCATE(',',user.table.groups,N=
+1)-N-1)
FROM Tally, user.table
WHERE N < LENGTH(user.table.groups)
AND SUBSTRING(user.table.groups,N,1) =3D ','
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
On Thu, 2009-06-25 at 16:17 -0400, Eddie Drapkin wrote:
> 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 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
> > 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 >> {
> >> $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
> >
>
Consider my wrists firmly slapped!
Thanks
Ash
www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
> 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 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
> > 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 >> {
> >> $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
> >
>
Consider my wrists firmly slapped!
Thanks
Ash
www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballard wrote:
> On Thu, Jun 25, 2009 at 3:20 PM, 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 (c=
ontaining
>> comma delimited data) into an equal number of rows (see example). =C2=A0=
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
>>
>
> The best performance I've seen for a SQL solution uses a tally table.
>
> CREATE TABLE Tally (
> =C2=A0 =C2=A0N int not null,
> =C2=A0 =C2=A0PRIMARY KEY (N)
> )
>
> The table holds a sequence of numbers from 1 to some large number.
>
> Then you can write a query something like this:
>
> =C2=A0SELECT uid, SUBSTRING(user.table.groups,N+1,LOCATE(',',user.table.g=
roups,N+1)-N-1)
> =C2=A0 FROM Tally, user.table
> =C2=A0WHERE N < LENGTH(user.table.groups)
> =C2=A0 =C2=A0AND SUBSTRING(user.table.groups,N,1) =3D ','
>
>
>
>
> Andrew
>
OK, I actually tested this and it appears I missed something. For this
query to work, the string has to begin and end with the delimiter. So
I just replaced user.table.groups with CONCAT(',', user.table.groups,
',').
SELECT uid, SUBSTRING(CONCAT(',', user.table.groups,
','),N+1,LOCATE(',',CONCAT(',', user.table.groups, ','),N+1)-N-1)
FROM Tally, sample
WHERE N < LENGTH(CONCAT(',', user.table.groups, ','))
AND SUBSTRING(CONCAT(',', user.table.groups, ','),N,1) =3D ','
For more in-depth information, check out some of the links in this search:
http://www.google.com/search?q=3Dsql+split+tally+table
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
> On Thu, Jun 25, 2009 at 3:20 PM, 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 (c=
ontaining
>> comma delimited data) into an equal number of rows (see example). =C2=A0=
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
>>
>
> The best performance I've seen for a SQL solution uses a tally table.
>
> CREATE TABLE Tally (
> =C2=A0 =C2=A0N int not null,
> =C2=A0 =C2=A0PRIMARY KEY (N)
> )
>
> The table holds a sequence of numbers from 1 to some large number.
>
> Then you can write a query something like this:
>
> =C2=A0SELECT uid, SUBSTRING(user.table.groups,N+1,LOCATE(',',user.table.g=
roups,N+1)-N-1)
> =C2=A0 FROM Tally, user.table
> =C2=A0WHERE N < LENGTH(user.table.groups)
> =C2=A0 =C2=A0AND SUBSTRING(user.table.groups,N,1) =3D ','
>
>
>
>
> Andrew
>
OK, I actually tested this and it appears I missed something. For this
query to work, the string has to begin and end with the delimiter. So
I just replaced user.table.groups with CONCAT(',', user.table.groups,
',').
SELECT uid, SUBSTRING(CONCAT(',', user.table.groups,
','),N+1,LOCATE(',',CONCAT(',', user.table.groups, ','),N+1)-N-1)
FROM Tally, sample
WHERE N < LENGTH(CONCAT(',', user.table.groups, ','))
AND SUBSTRING(CONCAT(',', user.table.groups, ','),N,1) =3D ','
For more in-depth information, check out some of the links in this search:
http://www.google.com/search?q=3Dsql+split+tally+table
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Ummm yeah, its going to take me a while to wrap my head around that one.
thanks though!
Matt
On Thu, Jun 25, 2009 at 5:07 PM, Andrew Ballard wrote:
> On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballard wrote:
> > On Thu, Jun 25, 2009 at 3:20 PM, 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
> >>
> >
> > The best performance I've seen for a SQL solution uses a tally table.
> >
> > CREATE TABLE Tally (
> > N int not null,
> > PRIMARY KEY (N)
> > )
> >
> > The table holds a sequence of numbers from 1 to some large number.
> >
> > Then you can write a query something like this:
> >
> > SELECT uid,
> SUBSTRING(user.table.groups,N+1,LOCATE(',',user.table.groups,N+1)-N-1)
> > FROM Tally, user.table
> > WHERE N < LENGTH(user.table.groups)
> > AND SUBSTRING(user.table.groups,N,1) = ','
> >
> >
> >
> >
> > Andrew
> >
>
> OK, I actually tested this and it appears I missed something. For this
> query to work, the string has to begin and end with the delimiter. So
> I just replaced user.table.groups with CONCAT(',', user.table.groups,
> ',').
>
> SELECT uid, SUBSTRING(CONCAT(',', user.table.groups,
> ','),N+1,LOCATE(',',CONCAT(',', user.table.groups, ','),N+1)-N-1)
> FROM Tally, sample
> WHERE N < LENGTH(CONCAT(',', user.table.groups, ','))
> AND SUBSTRING(CONCAT(',', user.table.groups, ','),N,1) = ','
>
>
> For more in-depth information, check out some of the links in this search:
> http://www.google.com/search?q=sql+split+tally+table
>
>
> Andrew
>
--
Matt Giddings
Web Programmer
Information Technology Services
Saginaw Valley State University
Phone: 989.964.7247
http://www.svsu.edu
thanks though!
Matt
On Thu, Jun 25, 2009 at 5:07 PM, Andrew Ballard wrote:
> On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballard wrote:
> > On Thu, Jun 25, 2009 at 3:20 PM, 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
> >>
> >
> > The best performance I've seen for a SQL solution uses a tally table.
> >
> > CREATE TABLE Tally (
> > N int not null,
> > PRIMARY KEY (N)
> > )
> >
> > The table holds a sequence of numbers from 1 to some large number.
> >
> > Then you can write a query something like this:
> >
> > SELECT uid,
> SUBSTRING(user.table.groups,N+1,LOCATE(',',user.table.groups,N+1)-N-1)
> > FROM Tally, user.table
> > WHERE N < LENGTH(user.table.groups)
> > AND SUBSTRING(user.table.groups,N,1) = ','
> >
> >
> >
> >
> > Andrew
> >
>
> OK, I actually tested this and it appears I missed something. For this
> query to work, the string has to begin and end with the delimiter. So
> I just replaced user.table.groups with CONCAT(',', user.table.groups,
> ',').
>
> SELECT uid, SUBSTRING(CONCAT(',', user.table.groups,
> ','),N+1,LOCATE(',',CONCAT(',', user.table.groups, ','),N+1)-N-1)
> FROM Tally, sample
> WHERE N < LENGTH(CONCAT(',', user.table.groups, ','))
> AND SUBSTRING(CONCAT(',', user.table.groups, ','),N,1) = ','
>
>
> For more in-depth information, check out some of the links in this search:
> http://www.google.com/search?q=sql+split+tally+table
>
>
> Andrew
>
--
Matt Giddings
Web Programmer
Information Technology Services
Saginaw Valley State University
Phone: 989.964.7247
http://www.svsu.edu
Matt Giddings wrote:
> Ummm yeah, its going to take me a while to wrap my head around that one.
> thanks though!
> Matt
--
Matt Giddings
Web Programmer
Information Technology Services
Saginaw Valley State University
Phone: 989.964.7247
http://www.svsu.edu
I was born in Saginaw, Michigan.
I grew up in a house on Saginaw Bay.
My dad was a poor hard working Saginaw fisherman:
Too many times he came home with too little pay.
I loved a girl in Saginaw, Michigan.
The daughter of a wealthy, wealthy man.
But he called me: "That son of a Saginaw fisherman."
And not good enough to claim his daughter's hand.
--
Thanks!
-Shawn
http://www.spidean.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
> Ummm yeah, its going to take me a while to wrap my head around that one.
> thanks though!
> Matt
--
Matt Giddings
Web Programmer
Information Technology Services
Saginaw Valley State University
Phone: 989.964.7247
http://www.svsu.edu
I was born in Saginaw, Michigan.
I grew up in a house on Saginaw Bay.
My dad was a poor hard working Saginaw fisherman:
Too many times he came home with too little pay.
I loved a girl in Saginaw, Michigan.
The daughter of a wealthy, wealthy man.
But he called me: "That son of a Saginaw fisherman."
And not good enough to claim his daughter's hand.
--
Thanks!
-Shawn
http://www.spidean.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Related Threads
- [users] Margin measurements in decimals and fractions - openoffice
- [GENERAL] Fail of a return query from plpgsql function for a specific table - pgsql
- [Python-Dev] error in doc for fcntl module - python
- Lock enabling onboard lan (Attansic L1 GbE) on 7.1-PRERELEASE - freebsd
- Python strings and coding conventions - python
- how should I upgrade all these ports?? - freebsd
- Lazy List Generator Problem - python
- rawhide report: 20090124 changes - fedora
- Solaris Compat? - freebsd
- [ruby-core:21606] Binding behaviour in 1.9 - ruby
- Defer Retry - postfix