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 +0000, 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
