[RESOLVED] problem with dates - continued

OniLink

Hi,

My initial issue has been resolved... but I have come accross another.
Using this code:

$sql = 'SELECT COUNT(*), FROM_UNIXTIME(start_date, "%Y") AS year, FROM_UNIXTIME(start_date, "%c") AS month
FROM ibf_topics
WHERE (forum_id = "59" OR forum_id = "3" OR forum_id = "61") AND pinned = "0"
GROUP BY year, month ORDER BY year DESC, month DESC';
$result = $db->query($sql);
while ($row = $result->fetch_assoc()) {
// format dates
$year = $row['year'];
$month = $row['month'];
$strmonth = mktime(0,0,0,$month+1,0,0);
if (strlen($month) < 2) {
$month = '0'.$month;
}
echo '» <a href="index.php?page=month_archive&date='.$year.'-'.$month.'-00">';
echo date("M", $strmonth ).', '.$year.'</a></b><br />'."\n";

}

I get this list:

» Sep, 2006
» Aug, 2006
» Jul, 2006
» Jun, 2006
» May, 2006
» Apr, 2006
» Mar, 2006

But, there are records within the query paramaters that fall into the month of Oct, Nov, Dec... but for some reason these months aren't listed.

Does anyone have a clue why?

Thanks,
~Oni.

liquorvicar

Are you sure the query is picking up records for those months? have you tried echo-ing the query your code is creating and running it directly against the database to check?

OniLink

Yeah it is because I use the same WHERE clause parameters to list the latest news on the news page. It's strange 🙁

roscor

I used the standard mysql date, NOW() to submit to my database. I wanted to get the date from the format YYYY-MM-DD, so i used the following code to convert to unix fomat DD-MM-YYYY. This may be of some help to you.

$query="SELECT * FROM whatever WHERE id='$id' ORDER by date DESC";
$date = $row["date"];
//$date = '2006-09-11';  // Assume this came from your database
$ts = mktime(0,0,0,substr($date,5,2),substr($date,8,2),substr($date,0,4)); 
// Convert to unix format
$formated = date('d m Y',$ts);

Then <? echo $formated; ?>

This now outputs in date order DESC.

20-09-2006
21-06-2006
28-02-2005
26-01-2005
12-01-2004

liquorvicar

Have you tried stripping the query down and working out which bit is filtering the records you need?
I'd first remove your SELECT conditions and look at all the values retrieved (I'd use PHPMyAdmin or similar). Then try stripping each bit of the WHERE clause.

MitchEvans

In your WHERE clause try this:

forum_id="59"||id="2"||another_id="3"

Use|| instead of OR

liquorvicar

|| and OR are identical, swopping them will have no effect. If you really want to tidy the query up, use

WHERE forum_id IN ("59","3","61")

and you can even remove the double-quotes if it's a numeric field.

OniLink

liquorvicar wrote:
Have you tried stripping the query down and working out which bit is filtering the records you need?
I'd first remove your SELECT conditions and look at all the values retrieved (I'd use PHPMyAdmin or similar). Then try stripping each bit of the WHERE clause.

Thanks, I just tried doing that then and when stripping it down so the FROM_UNIXTIME() functions aren't used, it returns the correct articles???

liquorvicar

Have you tried using

DATE_FORMAT(start_date,'%Y%c') as yearmonth

in your SELECT clause? Not sure if it will make a difference but there is a note in the manual about UNIX timestamps being lossy in certain situations.

OniLink

SELECT COUNT(*), DATE_FORMAT(start_date,"%Y") AS year, DATE_FORMAT(start_date,"%c") AS month FROM ibf_topics
WHERE forum_id IN ("59","3","61") AND pinned = "0"
GROUP BY year, month ORDER BY year DESC, month DESC

Thanks, I tried that in phpmyadmin and it onyl displayed one record showing COUNT with the value 96, year and month with the value NULL.

liquorvicar

That's not quite what I wrote - my example had the year and month combined (it's fairly trivial to separate them in PHP).

Can you post a subset of your data so we can can test the query against it?

OniLink

I think I've found the problem. It seems to be treating year and month as strings and therefore isn't ordering it correctly. How do I go about making them INT?

Cheers again for all your help.

liquorvicar

Have you tried using %m as the month format? That should return a 2-digit number (i.e. left padded with a zero) and hence should sort OK.

OniLink

Absolutely perfect! You are a credit to this site.

By the way, condolences on the Ashes thrashing ;]

liquorvicar

OniLink wrote:
Absolutely perfect! You are a credit to this site.

No worries. I've lost count of how many times people have helped me, it's nice to give something back.

OniLink wrote:
By the way, condolences on the Ashes thrashing ;]

Grrr, I see you waited til after I helped you to bring that up! No disgrace in losing to the best side in the world and one of the best sides in the history of the game. There is disgrace in rolling over so easily and not making a decent fist of it.

OniLink

Hehehehe, I wasn't game enough to say it before hand. Your right though mate. I really thought you guys were going to put up a good fight. Especially after the Adelaide test.