Jump to content

GROUP BY results in a table.


nearenough

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/209931-group-by-results-in-a-table/
Share on other sites

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

 

 

 

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]

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

?>

 

 

 

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

      }

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

 

 

 

 

 

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!

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

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

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'];
}


?>

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!

 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.