p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Grouping MySQL Data by Date.


Message #1 by "Weldon, Ciaran" <Ciaran.Weldon@b...> on Tue, 7 May 2002 09:46:10 +0100
I've a simple script (http://www.prevexxed.f2s.com/playlist.php). What I
want to do is group the results by each date ie.

8/4/02
blah
blah
blah

rather than
8/4/2
blah
8/4/2
blah
8/4/2
blah.


Here is the part of the script for the sql bit...any anyone gimme me some
help?


$sql="SELECT date_format(week, '%d-%m-%y'), band, title,label format FROM
$dbtable order BY week ";

$getTotalRows = mysql_query($sql, $con);
$totalRowsNum = mysql_num_rows($getTotalRows);

$sql.=" Limit $thisOffset,$recordLimit";

$query = mysql_query($sql, $con);
while ($result=mysql_fetch_array($query)){
		
$lineIndex++;

	//**Example:
	$week=$result["date_format(week, '%d-%m-%y')"];
	$band=$result['band'];
	$title=$result['title'];
	$format=$result['format'];
	$label=$result['label'];
	echo "<table>
 <tr>
    <td><b>$week</b></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>$band</td>
    <td>$title</td>
	<td>$format</td>
    <td>$label</td>
  </tr>

</table>";
 }


Thanks



ciaran


Message #2 by "Nikolai Devereaux" <yomama@u...> on Tue, 7 May 2002 11:33:40 -0700
Well, since you're already ordering the results by the date, what I would
suggest doing is store the date in a temporary variable.  Each time through
the loop, test to see if the date in the query result row is different than
your temp var.  If it's different, it's a new date, so output it.  If it's
the same, then you've already output it, so you can skip it.

I'll insert some code into yours to illustrate this.

Also, I don't know why you're creating a new table for every result.  I'm
moving the <table> and </table> tags outside of the loop.


$sql="SELECT date_format(week, '%d-%m-%y'), band, title,label format FROM
$dbtable order BY week ";

$getTotalRows = mysql_query($sql, $con);
$totalRowsNum = mysql_num_rows($getTotalRows);

$sql.=" Limit $thisOffset,$recordLimit";

$query = mysql_query($sql, $con);

$lastdate = 0;
echo "<table>\n";

while ($result=mysql_fetch_array($query))
{
   $lineIndex++;
   //**Example:
   $week=$result["date_format(week, '%d-%m-%y')"];
   $band=$result['band'];
   $title=$result['title'];
   $format=$result['format'];
   $label=$result['label'];

   if($week != $lastdate)
   {
       echo <<<EOT
  <tr>
     <td><b>$week</b></td>
     <td>&nbsp;</td>
     <td>&nbsp;</td>
     <td>&nbsp;</td>
   </tr>

EOT;
        $lastdate = $week;
    }

    echo <<<EOT
   <tr>
     <td>$band</td>
     <td>$title</td>
 	<td>$format</td>
     <td>$label</td>
   </tr>

EOT;
>  }

   echo "</table>\n";



nik


  Return to Index