[SOLVED] Alternative to using GROUP_CONCAT?

[svivian]

I am trying to generate a neat list of Simpsons episodes for my web site, grouped by Season. I used this MySQL query:

SELECT Season, GROUP_CONCAT( '<li><a href=\"episodes.php?episode=',Pcode,'\">',EpisodeName,' (',Pcode,')</a></li>' SEPARATOR '\n') AS Episodes FROM EpisodeGuide GROUP BY Season

 

The idea was to generate some HTML quickly to avoid lots of looping etc in PHP. But I found that GROUP_CONCAT truncates results to 1024 characters.

 

Is there a better way to do this? There reason for grouping is because I want to do something like:

<h2>Season 1</h2>
<ol>
<li>[Episode Name]</li>
<li>...etc</li>
</ol>

<h2>Season 2</h2>
...

which I don't think can be done easily with a simple select all rows query. The only other way I can think of is to use one query to get the number of Seasons, then a query per Season to list the episodes...but this seems like overkill.

 

Suggestions welcome!

[effigy]

From the docs:

 

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the maximum effective length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val  is an unsigned integer:

 

SET [sESSION | GLOBAL] group_concat_max_len = val;

[svivian]

Hmm, well that could work for this, but in general there isn't really a way of knowing how long the final query will be.

 

I've had another idea, which is this query:

SELECT Season, GROUP_CONCAT( Pcode SEPARATOR '\n' ) AS Pcodes, GROUP_CONCAT( EpisodeName SEPARATOR '\n') AS Names FROM `episodeguide` GROUP BY `Season`

 

A bit simpler since it doesn't add all that HTML. I can split each result by "\n" in PHP and get an array.

[fenway]

Hmm, well that could work for this, but in general there isn't really a way of knowing how long the final query will be.

 

I've had another idea, which is this query:

SELECT Season, GROUP_CONCAT( Pcode SEPARATOR '\n' ) AS Pcodes, GROUP_CONCAT( EpisodeName SEPARATOR '\n') AS Names FROM `episodeguide` GROUP BY `Season`

 

A bit simpler since it doesn't add all that HTML. I can split each result by "\n" in PHP and get an array.

Why would you do anything of the sort? Issue two separate queries... and deal with in PHP.

[Barand]

try something like

<?php
$sql = "SELECT season, episodename FROM episodeguide
        ORDER BY season";
$res = mysql_query ($sql);

$prevSeason = '';
while (list($season, $name) = mysql_fetch_row($res))
{
    if ($prevSeason != $season)
    {
        if ($prevSeason != '')
            echo '</ol>';
        echo "<h3>$season</h3><ol>";
        $prevSeason = $season;
    }
    echo "<li>$name</li>";
}
echo '</ol>';
?> 

[svivian]

Why would you do anything of the sort? Issue two separate queries... and deal with in PHP.

I don't understand, what would the two queries be? As far as I'm aware, besides selecting all episodes in one go and using PHP to sort them out, the only other way would be a query for each Season (19+ in the case of The Simpsons!)

 

Barand's solution is pretty good though, I think I did something like that for something else but it came out a big mess of PHP code but this is neat enough.

[fenway]

You're trying to build HTML from MySQL...

[svivian]

I know this is a huge bump, but thought I'd post a new solution for this problem that I just came up with while working on something else.

 

Basically, you do a straightforward select all rows query, ordered by your 'grouping' field (season in my case), then by any secondary fields. Then, loop through the MySQL result and store the data in a multi-dimensional array. The first dimension is the grouping field, and the second is your order.

 

The code is below. Hope this helps someone!

 

$result = mysql_query( "SELECT pcode, name, season, sindex FROM episode ORDER BY season, sindex" );
$episodes = array();
  
while ( ($e=mysql_fetch_assoc($result)) !== false )
{
  $s = $e['season'];
  $i = $e['sindex'];
    
  $episodes[$s][$i] = array(
    'pcode' => $e['pcode'],
    'name' => $e['name']
  );
}

 

You can then use a foreach to loop seasons, and another inside that to loop episodes.