Jump to content

[SOLVED] Alternative to using GROUP_CONCAT?


svivian

Recommended Posts

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! :)

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>';
?> 

Link to comment
Share on other sites

  • 3 weeks later...

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.

Link to comment
Share on other sites

  • 1 year later...

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.