Re: [PHP] finding news stories in a selection of months

[Don Read]

On 02-Feb-2004 Michael Hill wrote:

 Hi everyone,
snip
 heres the code i have:
  
  $strSQL = SELECT date from .$tbl_prefix.news  where live=1 order
 by
 date asc limit 0,1;
  $result = mysql_query($strSQL,$dbconn);
  $firstresult = mysql_fetch_array($result);
  $firstdate = $firstresult['date'];
  $firstmonth = substr($firstdate, 4, 6);
  $firstyear = substr($firstdate, 0, 4);
  
  
  
  
  $strSQL = SELECT date from .$tbl_prefix.news  where live=1 order
 by
 date desc limit 0,1;

snip

$qry=SELECT MIN(date) AS first, MAX(date) as last FROM ...;
$res=mysql_query($qry);

if (mysql_numrows($res) ) {
   $row=mysql_fetch_array($res);
   list($fy, $fm, $fd) = explode('-', $row['first']);
   list($ly, $lm, $ld) = explode('-', $row['last']);
} else {
   echo 'Not found', 'P';
}

Regards,
-- 
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] finding news stories in a selection of months

[Michael Hill]

Hi everyone,
I have made written some code (really bad code,I'm sure), that finds the
earliest month that has a news story, and the latest month that has a
news story. the code is below. I now need to loop through somehow, to
build an array, that gives me the month (JAN,FEB,MAR) and the year it
belongs to, along with the number of news stories each month has. I'm a
bit stuck as i'm really new to this stuff,
 
thanks for any help, you guys can give,
heres the code i have:
 
 $strSQL = SELECT date from .$tbl_prefix.news  where live=1 order by
date asc limit 0,1;
 $result = mysql_query($strSQL,$dbconn);
 $firstresult = mysql_fetch_array($result);
 $firstdate = $firstresult['date'];
 $firstmonth = substr($firstdate, 4, 6);
 $firstyear = substr($firstdate, 0, 4);
 
 
 
 
 $strSQL = SELECT date from .$tbl_prefix.news  where live=1 order by
date desc limit 0,1;
 $result = mysql_query($strSQL,$dbconn);
 $lastresult = mysql_fetch_array($result);
 $lastdate = $lastresult['date'];
 $lastmonth = substr($lastdate, 4, 6);
 $lastyear = substr($lastdate, 0, 4);
 
 
 
Michael Hill
Senior Developer
Holler 

02076891942
www.holler.co.uk


This message is confidential, you may use and apply the information only
for the intended purpose. Internet communications are not secure and
therefore Holler does not accept legal responsibility for the
contents of this message. Any views or opinions expressed are solely
those of the author and may not represent those of Holler.
If you are not the intended recipient, any disclosure, copying,
distribution
or use of it, or any part of it, in any form whatsoever, and any actions
taken or omitted to be taken in reliance on it, is prohibited and may
be unlawful. 

 

Re: [PHP] finding news stories in a selection of months

[Miles Thompson]

Michael,

Let the database do the work. Check your MySQL docs (assuming you are using 
MySQL, otherwise check what you have  ) for the month() function. Use this 
in your SELECT, aliasing it so that you do not use the term month, say 
pub_month. Your SQL would then look like this 

 $strSQL = SELECT date, MONTH(date) as pub_month from 
.$tbl_prefix.news  where live=1 order by date asc limit 0,1;
(You can do the same thing with the YEAR() function)

Next step is to GROUP your results by the month ...

 $strSQL = SELECT date, month(date) as pub_month from 
.$tbl_prefix.news  where live=1 GROUP BY pub_month order by date asc 
limit 0,1;

As an aside, why are you limiting results to one record with limit 0,1? For 
testing, why don't you drop that? Also, while testing, echo your $strSQL so 
see what it looks like. I KNOW we always write correct code every time and 
don't need to bother with such an elementary step. Yet doing this seems to 
save a lot of time g.

If you can, rename your date column to pub_date; date is a reserved word 
for many database systems or a function name in many languages. Good to 
avoid it and then you don't have to always enclose it in quotes, or square 
brackets, etc.

Hope this is helpful - Miles

At 03:10 PM 2/2/2004 +, Michael Hill wrote:
Hi everyone,
I have made written some code (really bad code,I'm sure), that finds the
earliest month that has a news story, and the latest month that has a
news story. the code is below. I now need to loop through somehow, to
build an array, that gives me the month (JAN,FEB,MAR) and the year it
belongs to, along with the number of news stories each month has. I'm a
bit stuck as i'm really new to this stuff,
thanks for any help, you guys can give,
heres the code i have:
 $strSQL = SELECT date from .$tbl_prefix.news  where live=1 order by
date asc limit 0,1;
 $result = mysql_query($strSQL,$dbconn);
 $firstresult = mysql_fetch_array($result);
 $firstdate = $firstresult['date'];
 $firstmonth = substr($firstdate, 4, 6);
 $firstyear = substr($firstdate, 0, 4);



 $strSQL = SELECT date from .$tbl_prefix.news  where live=1 order by
date desc limit 0,1;
 $result = mysql_query($strSQL,$dbconn);
 $lastresult = mysql_fetch_array($result);
 $lastdate = $lastresult['date'];
 $lastmonth = substr($lastdate, 4, 6);
 $lastyear = substr($lastdate, 0, 4);


Michael Hill
Senior Developer
Holler
02076891942
www.holler.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php