[thelist] php/mysql question

[Kae Verens]

Dunstan Orchard wrote:
> Hi there,
> 
> I have a db with the following fields in:
> 
> postid     int(11)
> postdate   date
> posttitle  tinytext
> postbody   text
> 
> And I'd like to do the following:
> 
> [1] Select the last 30 posts
> [2] For each month that the posts fall into print a heading of that month
> [3] Then print all the posts within that month
> [4] Keep going until my 30 posts have been printed
> 
> So an example output might be:
> 
> FEBRURARY
> 
> posttitle 89
> 
> posttitle 88
> 
> posttitle 87
> 
> JANUARY
> 
> posttitle 86
> 
> DECEMBER
> 
> postitle 85
> 
> posttitle 84
> 
> etc... up to 30 posts.
> 
> Could anyone recommend the best way of achieving this in php?

/a/ way (don't know about best...):

$q=mysql_query('select postid,postdate,posttitle,postbody from theTable 
order by postdate desc');
$m='';
$mon=array('January','February','March','April',May','June',
   'July','August','September','October','November','December');
while($r=mysql_fetch_array($q)){
  // get month - assumes no gaps of over 11 months (YMMV)
  $t=preg_replace('/\d\d-(\d\d)-.*/','\\1',$r['postdate']);
  if($t!=$m){$t=$m;echo '<h2>'.$mon[$t-1].'</h2>';}
  echo htmlspecialchars($r['posttitle']).'<br />';
}

haven't checked that regexp, but it should be right
Kae

<tip type="PHP optimization">
always use ' instead of " when simply echoing a line. double quotes are 
always parsed by PHP in case they contain variables, etc. If you check 
it, you'll find that:
   echo 'test';
  is faster than:
   echo "test";

Besides;
   echo 'a test of "quotes"';
  is cleaner than:
   echo "a test of \"quotes\"";
</tip>
-- 
Kae  Verens +----------------------------------+ webworks.ie
        pay  |      http://www.webworks.ie      |  bee
    play     |  http://www.contactjuggling.org  |     boss
kae         |http://kverens.contactjuggling.org|         god