GROUP BY Query + Alternating Table Row Classes

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DevBear
Forum Newbie
Posts: 4
Joined: Mon May 09, 2005 11:34 pm

GROUP BY Query + Alternating Table Row Classes

Post by DevBear »

I apologize in advance for this lengthy post, but I don't think I'm going to solve this problem without tackling two problems at the same time - writing a script that includes a GROUP BY query and a script for generating alternating table classes. When I focus on just one, there's inevitably a conflict with the other.

However, I don't think my question is exceptionally hard for advanced PHP users. Anyway, here goes.

First, imagine a database table that includes place names and mammal group names, like this:

Africa | mammals
Asia | mammals
Asia | birds
Alaska | birds
California | reptiles
Kenya | mammals

However, I don't want to display six rows; rather, I want to display 1 row for each group of animals, with all the place names associated with that animal group in the other column, like this:

Africa, Asia, Kenya | mammals
Asia, Alaska | birds
California | reptiles

In fact, I already have such a table online at http://www.geosymbols.org/animals/insects/ The only problem is that it only lists ONE place name per row. The first row is...

Delaware | Coleoptera (beetles)

when it should be...


Delaware, Massachusetts, New Hampshire, New York, Latvia | Coleoptera (beetles)

I've discovered a few scripts that do the job, but I often run into problems when displaying data; they're simply too complex to work with.

This is the display table I'm currently working with, and I'm reluctant to replace it with a big blob of code with long foreach array statements, or whatever they're called:

Code: Select all

echo <<<EOD
   <tr class="{$row["IDParent2"]} {$row["DesigGeneral"]}" id="$mycodeID" style="background-color:$c">
     <td class="tdname" style=\"background-color:$color\">{$row["Area"]}</td>
     <td class="tdsymbol"><a href="$LinkSymbol">{$row["Order"]}</a> ({$row["OrderCommon"]})</td>
   </tr>
EOD;
Similarly, I tried various SELECT statements with various degrees of success before someone told me about the GROUP_CONCAT function. My attempt gets an error message:

Code: Select all

$res = mysql_query("SELECT GROUP_CONCAT(DISTINCT Area SEPARATOR ', ') as Area, Class, OrderCommon, `Order` FROM symbols
  WHERE Class = '$myname' ORDER BY `Order` GROUP BY Area");
I'll append my current script, which I THINK is pretty close, except for a few technical glitches. But first let me comment on the alternating table row classes.

I decided they'd be better than alternating row colors, partly because they generate less html and also because they're more flexible. Alternating rows give me yellow and blue. Alternating row CLASSES allow me to also assign colors to individual table cells with CSS, like this:

<tr class="one"><td class="name"><td class="symbol">
<tr class="two"><td class="name"><td class="symbol">

In my stylesheet, I can then write this...

tr.one td.name { background: #ff0; }
tr.one td.symbol { background: #0ff; }

I think that's enough information to give you the gist of what I'm trying to do. The source code I'm going to append includes a script for alternating row colors, which I'd like to remove or convert to a script that produces alternating row CLASSES. The other major problem is the GROUP_CONCAT SELECT script, which gets an error message.

There are probably some other errors, perhaps with closing brackets. Finally, the closing PHP tag (?>) is faded out, suggesting an error somewhere above.

I've been wrestling with this for days, and I just run into one brick wall after the other. I'd be overjoyed if someone can help me make this script work. I'm also open to alternatives, though I prefer to keep them fairly simple. I don't know how to work with things like

Code: Select all

<?php echo htmlspecialchars(implode(', ', $areas)); ?>
or

Code: Select all

$block = .<tr>


Here's my source code. Thanks!

Code: Select all

<?php
switch ($Animals['SymClass']) {
case 'animals':
$colors = array( 'first', '' );
// $colors = array( '#f4eec2', '' );
$n=0;
$size=count($colors);
break;
case 'plants':
$colors = array( '#80ff80', '', '#bfffbf', '' );
$n=0;
$size=count($colors);
break;
default:
break;
}

switch ($Animals['SymClass']) {
case 'animals':
$BG = '#900';
$n=0;
$size=count($colors);
break;
case 'plants':
$BG = '#060';
$n=0;
$size=count($colors);
break;
default:
break;
}


// QUERY...


// if type contains content then print out list
$result = mysql_query('select count(*) from symbols');
if (($result) && (mysql_result ($result , 0) > 0)) {
} else {
die('Invalid query: ' . mysql_error());
}
{

 $res = mysql_query("SELECT GROUP_CONCAT(DISTINCT Area SEPARATOR ', ') as Area, Class, OrderCommon, `Order` FROM symbols
  WHERE Class = '$myname' ORDER BY `Order` GROUP BY Area");

if ( mysql_errno() > 0 ) {
  echo mysql_error();
  exit;
}
else {

echo '<table class="sortphp_ref" id="TableTax" style="margin: 0px auto 25px; border-bottom: 1px solid #000; font-size: 85%;">
<thead>
<tr><th style="background: #666; color: #fff;">Places</th>
<th style="background: ' . $BG . '; color: #fff;">Orders</th></tr>
</thead>
<tbody>';
//<!-- BeginDynamicTable -->
{

while ( $row = mysql_fetch_array( $res ) ) {


// ALTERNATING ROW COLORS SCRIPT...


$c=$colors[$rowcounter++%$size];
$color=$n++%2?"#eee":"";
$foo = array("us-", "ca-");
$mycodeID = str_replace($foo, "", $row["IDArea"]);

// Adjust it for bony fishes...
switch ($Animals['Class']) {
case 'Osteichthyes':
$LinkSymbol = '/animals/fishes/' . strtolower(substr($row["Order"],0,3)) . '/';
break;
default:
$LinkSymbol = '/animals/' . strtolower($row["ClassCommon"]) . '/' . strtolower(substr($row["Order"],0,3)) . '/';
break;
}

echo <<<EOD
   <tr class="$c {$row["IDParent2"]} {$row["DesigGeneral"]}" id="$mycodeID" style="background-color:$c">
     <td class="tdname" style=\"background-color:$color\">{$row["Area"]}</td>
     <td class="tdsymbol"><a href="$LinkSymbol">{$row["Order"]}</a> ({$row["OrderCommon"]})</td>
   </tr>
EOD;
}
}
}
}
?>
</tbody>
</table>
DevBear
Forum Newbie
Posts: 4
Joined: Mon May 09, 2005 11:34 pm

Post by DevBear »

P.S. I just learned that GROUP_CONCAT isn't supported by the MySQL version my host is using. So much for that idea!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Well, the two problems can be unrelated:

Code: Select all

$query = "get stuff: 1 row per entry";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
  //this line will concatinate the current continent found for a given
  //animal, onto any pre-existing continents
  $animal_list[$row['animal_type']] .= $row['continent'] . ',';
}

//first problem is solved now

//now, animal_list is what you want to output

$class = 'yellow_class';
echo '<table>';
foreach($animal_list as $animal_type=>$continents)
{
  //alternates row classes.  This should solve your second problem
  $class = ($class == 'yellow_class') ? 'blue_class' : 'yellow_class';
  echo "<tr class = '$class'><td>$animal_type</td><td>" . rtrim($continents,',') . '</td></tr>';
}
echo '</table>';
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
DevBear
Forum Newbie
Posts: 4
Joined: Mon May 09, 2005 11:34 pm

Post by DevBear »

OK, thanks; I'll check that out.
Post Reply