Making two queries become one

middleman666

hey people,

I came from the general help forum as I was told you'd be able to help me with this.

Basically I have my code which is a monthly archive, it first reads out the month and year and then it reads out all the entries from that month and year below it and then repeats.

The code works fine and does the job, but I'm using two queries and was wondering how I would go about combining them?

heres my code:

<?php 
include(dbconnect.php');
$TableName = 'Table';
$today = date('Y-m-d');
?>

<table border="0" cellpadding="0" cellspacing="10">
<tr>	  

<?php
// Build SQL Query  

$query = "select *, DATE_FORMAT(date,'%M %y') as monthyear from $TableName 
where date <= \"%$today%\" group by monthyear order by date DESC";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results
if ($numrows == 0) {
 echo 'There are currently no entries.';
}

//start count
$i = 1;

// get results
$result = mysql_query($query) or die("Couldn't execute query");

// now display the results returned
while ($row= mysql_fetch_array($result)) {

  // Workout Month Duration  

  $thismonth = substr($row['date'],0,7);
  $id_low = $thismonth.'-01'; 
  $id_high = $thismonth.'-31'; 

  echo '<td valign="top">
        <a href="index.php?menu=home&month='.substr($row['date'],0,7).'" target="_parent">
		<b>'.$row['monthyear'].'</b></a>';

  // Build SQL Query to list entries of month  

  $query2 = "select *, DATE_FORMAT(date,'%M %y') as monthyear from $TableName 
  where date >= \"%$id_low%\" AND date <= \"%$id_high%\" order by date DESC";

  $numresults2=mysql_query($query2);
  $numrows2=mysql_num_rows($numresults2);

  // get results
  $result2 = mysql_query($query2) or die("Couldn't execute query");

  // now display the results returned
  while ($row2= mysql_fetch_array($result2)) {

echo '<br><b>'.$i.'.</b> <a href="index.php?menu=home&month='.substr($row2['date'],0,7).'&id='.$row2['id'].'" target="_parent">
      <span class="archiveArtistName">'.$row2['artist'].'</span> - 
      <span class="archiveTrackName">'.$row2['track'].'</span></a>
     ';

$i++;
  }

  // If we have no more results
  if ($numrows2 >= $numrows2) {
    $i = 1;
  }

}
?>
</td>  

</tr>
</table>

Any help is welcome, Thanks.

Roger_Ramjet

<?php
include('dbconnect.php');
$TableName = 'Table';
$today = date('Y-m-d');
?>

<table border="0" cellpadding="0" cellspacing="10">
<tr>      

<?php
// Build SQL Query  

$query = "select *, DATE_FORMAT(date,'%M %y') as monthyear from $TableName
where date <= \"%$today%\" group by monthyear order by date DESC";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results
if ($numrows == 0) {
echo 'There are currently no entries.';
}

//start count
$i = 1;

// get results
$result = mysql_query($query) or die("Couldn't execute query");

// now display the results returned
while ($row= mysql_fetch_array($result)) {

  // Workout Month Duration  

  $thismonth = substr($row['date'],0,7);
  $id_low = $thismonth.'-01';
  $id_high = $thismonth.'-31';

  echo '<td valign="top">
        <a href="index.php?menu=home&month='.substr($row['date'],0,7).'" target="_parent">
        <b>'.$row['monthyear'].'</b></a>';

  // Build SQL Query to list entries of month  

  $query2 = "select *, DATE_FORMAT(date,'%M %y') as monthyear from $TableName
  where date >= "%$id_low%\" AND date <= \"%$id_high%\" order by date DESC";

  $numresults2=mysql_query($query2);
  $numrows2=mysql_num_rows($numresults2);

  // get results
  $result2 = mysql_query($query2) or die("Couldn't execute query");

  // now display the results returned
  while ($row2= mysql_fetch_array($result2)) {

echo '<br><b>'.$i.'.</b> <a href="index.php?menu=home&month='.substr($row2['date'],0,7).'&id='.$row2['id'].'" target="_parent">
      <span class="archiveArtistName">'.$row2['artist'].'</span> -
      <span class="archiveTrackName">'.$row2['track'].'</span></a>
     ';

$i++;
  }

  // If we have no more results
  if ($numrows2 >= $numrows2) {
    $i = 1;
  }

}
?>
</td>  

</tr>
</table>

Roger_Ramjet

Not sure that I'm following you? You want to read out ALL the entries but group them by month and year in your display. Is that it?

Anyway I'm assuming that it is. All you need to do is to select them all in date order and use loop control to segment the display when the month changes. 1 query in total


$sql = "SELECT *, YEAR(date_col) AS year, MONTH(date_col) AS month FROM table
           ORDER BY date_col DESC";
$result = mysql_query($sql);

// set up vars to track which year and month you the table is displaying
$curyear = 0;
$curmonth = 0;

// start table and set first heading
$row = mysql_fetch_array($result);
echo '<table><th>Year = ' . $row['year'] . ' Month = ' . $row['month'] . '</th>';
echo '<tr><td>' . $row['data'] . '</td><tr>' ; // etc for display

$curyear = $row['year'];
$curmonth = $row['month'];  

while ($row = mysql_fetch_array($result)) {
   // track month and year and change output when they change
    if ($row['year'] != $curyear OR $row['month'] != $curmonth ) {
       // new month or year so new <th>
       echo '<th>Year = ' . $row['year'] . ' Month = ' . $row['month'] . '</th>';
       // track new year and/or month
       $curyear = $row['year'];
       $curmonth = $row['month'];
     }
    // now continue with data display whether month has changed or not
    echo '<tr><td>' . $row['data'] . '</td></tr>;
}

// now close the table
echo '</table>';