GROUP BY results in a table.

nearenough · August 5, 2010

i think this is more of a HTML problem, but it is in my PHP code so i will ask anyway!

if i have the search code:

if (count($error) < 1) {

$searchSQL = "SELECT id, placing, racedate, horseid FROM race WHERE ";

// grab the search types.

$types = array();

$types[] = isset($_GET['placing'])?"`placing` LIKE '%{$searchTermDB}%'":'';

$types[] = isset($_GET['racedate'])?"`racedate` LIKE '%{$searchTermDB}%'":'';

$types[] = isset($_GET['horseid'])?"`horseid` LIKE '%{$searchTermDB}%'":'';

$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

if (count($types) < 1)

$types[] = "`placing` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked

$andOr = isset($_GET['matchall'])?'AND':'OR';

$searchSQL .= implode(" {$andOr} ", $types) . " GROUP BY `horseid` ORDER BY `racedate`"; // order by title.

$searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

if (mysql_num_rows($searchResult) < 1) {

$error[] = "The search term provided {$searchTerms} yielded no results.";

}else {

$results = array(); // the result array

$i = 1;

echo '<table border="1"><tr><th>Num</th><th>Placing</th><th>Race Date</th><th>Horse ID</th></tr>';

while ($row = mysql_fetch_assoc($searchResult))

{

echo "<tr><td>$i</td>

<td>{$row['placing']}</td>

<td><a href=\"viewrace.php?date=" . urlencode($row['racedate']) . "\">{$row['racedate']}</a></td>

<td>{$row['horseid']}</td></tr>";

$i++;

}

echo '</table>';

how do i get the 'horseid' to appear at the header of the table rather than in a column?

thank you for all the brilliant help this site provides!!

iJoseph · August 5, 2010

I don't get what you mean, could you clarify more?

nearenough · August 5, 2010

if i search for all the races run by "Jimmy" for example i may get returns

Jimmy 2nd 2010-3-5

Jimmy 4th 2010-6-5

Jimmy 1st 2010-7-5

how do i then output those results in a table as:

Jimmy

2nd 2010-3-5

4th 2010-6-5

1st 2010-7-5

sinista · August 5, 2010

this might be a bit of crap reply as i havent read all of the code but if you output

Jimmy 2nd 2010-3-5

could you do something like

//start you table

echo '<table>';

echo '<tr>';

//explode the string by a space

$a = explode('Jimmy 2nd 2010-3-5', ' ' )

//foreach array part put it in a td tag

foreach($a as $b)

{

echo '<td>'.$b.'</td>';

}

//end table

echo '</tr>';

echo</table>'

sinista · August 5, 2010

forget what I last replied something like this might be better

<?php
//these could be the rows in the table
$a = array('Jimmy', '2nd', '2010-3-5');
$b = array('Jimmy', '4th', '2010-6-5');
$c = array('Jimmy', '1st', '2010-7-5');
$d = array('Timmy', '2nd', '2010-3-5');
$e = array('Timmy', '4th', '2010-6-5');
$f = array('Timmy', '1st', '2010-7-5');

//this could kind of be your query select * from horses 
$horses = array($a, $b, $c,$d, $e, $f);

$last_horse_names="";

echo "<table border='1'>";

//loop through each row
	foreach ($horses as $horse)
	{

		if($last_horse_names != $horse[0])
		{	
			echo "<tr><td colspan='2'>".$horse[0]."</td></tr>";
		}

			echo '<tr>';	
			echo "<td>".$horse[1]."</td>";
			echo "<td>".$horse[2]."</td>";
			echo '</tr>';

			$last_horse_names = $horse[0];	
	}

echo "</table>";
?>

when it dispays you should see the table in the picture

[attachment deleted by admin]

nearenough · August 6, 2010

thank you thats exactly how im trying to get it.

how do i integrate that script into the script i already have?

sinista · August 6, 2010

what are the $row's are they

$row[''horseid'] = 'timmy'

$row['placing' = '2nd'

$row['racedate'] = '10-06-210'

would this work

<?php

if (count($error) < 1) {
$searchSQL = "SELECT id, placing, racedate, horseid FROM race WHERE ";

// grab the search types.
$types = array();
$types[] = isset($_GET['placing'])?"`placing` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['racedate'])?"`racedate` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['horseid'])?"`horseid` LIKE '%{$searchTermDB}%'":'';

$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

if (count($types) < 1)
{
$types[] = "`placing` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
}

$andOr = isset($_GET['matchall'])?'AND':'OR';
$searchSQL .= implode(" {$andOr} ", $types) . " GROUP BY `horseid` ORDER BY `racedate`"; // order by title.

$searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

if (mysql_num_rows($searchResult) < 1) 
{
	$error[] = "The search term provided {$searchTerms} yielded no results.";
}
else 
{
	$results = array(); // the result array
	$i = 1;
	echo '<table border="1">';

	echo "<tr>";
		echo '<th colspan="2">Horse Id:</th>';
	echo "</tr>";

	echo "<tr>";
		echo '<td>Placing</td>';
		echo '<td>Race Date</td>';
	echo "</tr>";

	$last_horse_names ='';

	while ($row = mysql_fetch_assoc($searchResult)) 
	{


		if($last_horse_names != $horse[0])
		{	
			#not sure where the $i is going so just put it here
			echo "<tr><td colspan='2'>".$i."</td></tr>";
			echo "<tr><td colspan='2'>".$row['horse_id']."</td></tr>";
		}

			echo '<tr>';	
			echo "<td>".$row['placing']."</td>";
			echo'<td><a href=\"viewrace.php?date=" . urlencode($row['racedate']) . "\">{$row['racedate']}</a></td>'
			echo '</tr>';

			$last_horse_names = $row['horseid'];
			$i++;
	}
	echo '</table>';
}

?>

nearenough · August 6, 2010

thank you for that, almost there

the script throws out this line

echo '<td><a href=\"viewrace.php?date=" . urlencode($row['racedate']) "\">{$row['racedate']}</a></td>'

unexpected T echo expectin , or ; it claims....

sinista · August 6, 2010

try this

<?php
echo "<td><a href='viewrace.php?date=".urlencode($row['racedate'])."'>".$row['racedate']."</a></td>";  
?>

nearenough · August 7, 2010

closer!

it makes the table header

"horseid:" but it doent populate it

thanks for all the help so far, very kind

sinista · August 7, 2010

I thought you would have noticed the typo, change the line

<?php 

if($last_horse_names != $horse[0]) 

?>

to

<?php 

if($last_horse_names != $row['horseid'])

?>

and see if its any closer

nearenough · August 7, 2010

cracked it, thanks a million, works a treat now

now all i have to do is adapt it to my more complicated real results template...

sinista · August 7, 2010

no worries all it does is,

take the values of the first row,

put it into a var

print out the table heading

on the next row

if the row value is the same as the one in the var do nothing else print out a new table heading

and so on and so on.....

nearenough · August 7, 2010

OK so far so good

http://trotdata.byethost32.com/tester2.php?search=amman&submit=Search!

(the links on that page go to a non existent page as yet..)

each race has a Track, Racedate, RaceName, RaceDist field as well as a RaceNumber which currently appears as the header for each race (so it says "494, Amman Valley, 2/8/2010, Maiden Race, 1 Mile"

how do i join those fields into the header of each race result?

ive tried 'RaceNumber'.'Racedate' etc but thats not right...

echo '<th colspan="8">Trotdata Results Sheet:</th>';

echo "</tr>";

$last_race_number ='';

while ($row = mysql_fetch_assoc($searchResult))

{

if($last_race_number != $row['RaceNumber'])

{

echo "<tr><td colspan='8'>".$row['RaceNumber']."</td></tr>";

}

echo '<tr>';

echo "<td>".$row['Placing']."</td>";

echo "<td><a href='horseres.php?Horseid=".urlencode($row['Horseid'])."'>".$row['Horseid']."</a></td>";

echo "<td>".$row['Trail']."</td>";

echo "<td>".$row['Draw']."</td>";

echo "<td>".$row['Driver']."</td>";

echo "<td>".$row['Dist']."</td>";

echo "<td>".$row['Time']."</td>";

echo "<td>".$row['Comment']."</td>";

echo '</tr>';

$last_race_number = $row['RaceNumber'];

}

sinista · August 7, 2010

so where it says 478, 478, 480

you would like it to say

478 - Racedate, RaceName, RaceDist

479 - Racedate1, RaceName1, RaceDist1

480 - Racedate2, RaceName2, RaceDist3

could you expand it to be

<?php
if($last_race_number != $row['RaceNumber'] && $last_race_date != $row['Racedate'] && $last_race_name != $row['RaceName'] && $last_race_dist != $row['RaceDist'])
{   
echo "<tr><td colspan='8'>".$row['RaceNumber']."- ".$row['RaceDate']$row['RaceName']$row['RaceDist']."</td></tr>";
}


....


  echo "<td>".$row['Comment']."</td>";
            echo '</tr>';
            
            $last_race_number = $row['RaceNumber'];
		$last_race_date = $row['RaceDate'];
		$last_race_name = $row['RaceName'];
		$last_race_dist = $row['RaceDist'];
      }
?>

nearenough · August 7, 2010

i added a few commas and its now

while ($row = mysql_fetch_assoc($searchResult))

{

if($last_race_number != $row['RaceNumber'] && $last_race_date != $row['Racedate'] && $last_race_name != $row['RaceName'] && $last_race_dist != $row['RaceDist'])

{

echo "<tr><td colspan='8'>".$row['RaceNumber']."- ".$row['RaceDate'],$row['RaceName'],$row['RaceDist']."</td></tr>";

}

echo '<tr>';

echo "<td>".$row['Placing']."</td>";

echo "<td><a href='horseres.php?Horseid=".urlencode($row['Horseid'])."'>".$row['Horseid']."</a></td>";

echo "<td>".$row['Trail']."</td>";

echo "<td>".$row['Draw']."</td>";

echo "<td>".$row['Driver']."</td>";

echo "<td>".$row['Dist']."</td>";

echo "<td>".$row['Time']."</td>";

echo "<td>".$row['Comment']."</td>";

echo '</tr>';

$last_race_number = $row['RaceNumber'];

$last_race_date = $row['RaceDate'];

$last_race_name = $row['RaceName'];

$last_race_dist = $row['RaceDist'];

but its not showing the header row....

same link will show what its like now!

sinista · August 7, 2010

if this is the working code from the previous post you could always do a sub query

<?php
       echo '<th colspan="8">Trotdata Results Sheet:</th>';
      echo "</tr>";
          
      $last_race_number ='';
      
      while ($row = mysql_fetch_assoc($searchResult)) 
      {
           
         if($last_race_number != $row['RaceNumber'])
         {   

            $sql = "select 
                             *
                        from 
                             table_name


            echo "<tr><td colspan='8'>".$row['RaceNumber']."</td></tr>";
         }
            echo '<tr>';
            echo "<td>".$row['Placing']."</td>";
            echo "<td><a href='horseres.php?Horseid=".urlencode($row['Horseid'])."'>".$row['Horseid']."</a></td>";
            echo "<td>".$row['Trail']."</td>";
            echo "<td>".$row['Draw']."</td>";
            echo "<td>".$row['Driver']."</td>";
            echo "<td>".$row['Dist']."</td>";
            echo "<td>".$row['Time']."</td>";
            echo "<td>".$row['Comment']."</td>";
            echo '</tr>';
            
            $last_race_number = $row['RaceNumber'];
      }
?>

sinista · August 7, 2010

if this is the working code from the previous post you could always do a sub query

<?php
       echo '<th colspan="8">Trotdata Results Sheet:</th>';
      echo "</tr>";
          
      $last_race_number ='';
      
      while ($row = mysql_fetch_assoc($searchResult)) 
      {
           
         if($last_race_number != $row['RaceNumber'])
         {   

            $sql = "select 
                             *
                        from 
                             table_name
                        where 
                             unique identifier = '".$row['RaceNumber']."'
                      "; 
               $row2 = mysql_fetch_array($sql);
               echo "<tr><td colspan='8'>".$row['RaceNumber']." - ".$row2['FIELDSNAME']."</td></tr>";
         }
            echo '<tr>';
            echo "<td>".$row['Placing']."</td>";
            echo "<td><a href='horseres.php?Horseid=".urlencode($row['Horseid'])."'>".$row['Horseid']."</a></td>";
            echo "<td>".$row['Trail']."</td>";
            echo "<td>".$row['Draw']."</td>";
            echo "<td>".$row['Driver']."</td>";
            echo "<td>".$row['Dist']."</td>";
            echo "<td>".$row['Time']."</td>";
            echo "<td>".$row['Comment']."</td>";
            echo '</tr>';
            
            $last_race_number = $row['RaceNumber'];
      }
?>

sinista · August 7, 2010

I dont know why i didn't think of this earlier

<?php

echo '<th colspan="8">Trotdata Results Sheet:</th>';
echo "</tr>";

$last_race_number ='';

while ($row = mysql_fetch_assoc($searchResult)) 
{

if($last_race_number != $row['RaceNumber']." - "$row['RaceDate'].$row['RaceName'].$row['RaceDist'])
{   
echo "<tr><td colspan='8'>".$row['RaceNumber']." - ".$row['RaceDate'].$row['RaceName'].$row['RaceDist']."</td></tr>";
}
echo '<tr>';
echo "<td>".$row['Placing']."</td>";
echo "<td><a href='horseres.php?Horseid=".urlencode($row['Horseid'])."'>".$row['Horseid']."</a></td>";
echo "<td>".$row['Trail']."</td>";
echo "<td>".$row['Draw']."</td>";
echo "<td>".$row['Driver']."</td>";
echo "<td>".$row['Dist']."</td>";
echo "<td>".$row['Time']."</td>";
echo "<td>".$row['Comment']."</td>";
echo '</tr>';

$last_race_number = $row['RaceNumber']." - "$row['RaceDate'].$row['RaceName'].$row['RaceDist'];
}


?>

nearenough · August 7, 2010

thats it now!

if($last_race_number != $row['RaceNumber'])

{

echo "<tr><td colspan='8'>".$row['RaceNumber']." - ". $row['Track']." - ". $row['RaceDate'] ." - ". $row['RaceName'] ." - ". $row['RaceDist']."</td></tr>";

}

it was the joining things with dots idea that i was trying to do at the start but i was missing out the $row bit

thanks a million for all the hard work!

Sign In

GROUP BY results in a table.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information