Re: [PHP] OT mysql pivot table problem
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 aball...@gmail.com wrote:
On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballardaball...@gmail.com wrote:
On Thu, Jun 25, 2009 at 3:20 PM, Matt Giddingsmcgid...@svsu.edu 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
Re: [PHP] OT mysql pivot table problem
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
[PHP] OT mysql pivot table problem
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
Re: [PHP] OT mysql pivot table problem
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; $icount($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
Re: [PHP] OT mysql pivot table problem
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
a...@ashleysheridan.co.ukwrote:
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; $icount($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
Re: [PHP] OT mysql pivot table problem
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 Giddingsmcgid...@svsu.edu 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
a...@ashleysheridan.co.ukwrote:
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; $icount($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
Re: [PHP] OT mysql pivot table problem
On Thu, Jun 25, 2009 at 3:20 PM, Matt Giddingsmcgid...@svsu.edu 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] OT mysql pivot table problem
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 Giddingsmcgid...@svsu.edu 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
a...@ashleysheridan.co.ukwrote:
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; $icount($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
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
Re: [PHP] OT mysql pivot table problem
On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballardaball...@gmail.com wrote:
On Thu, Jun 25, 2009 at 3:20 PM, Matt Giddingsmcgid...@svsu.edu 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
