date/time

[karimali831]

Hey,

 

I need help with date, I have stored dates in my table in this format:

i.e. 22.10.2011 but now use timestamp to store dates.

 

Problem is with this query the WHERE clause will find dates in the old format

and I want to change that to the new format.

 

	for($i = $count; $i > 0; $i--)
	{
		$day = date("d.m.Y", mktime(0, 0, 0, date("m"), date("d") - $i, date("Y")));
		$tmp = mysql_fetch_array(safe_query("SELECT count FROM ".PREFIX."counter_stats WHERE dates LIKE '%".$day."'"));
		$array[] = $tmp['count'] ? $tmp['count'] : 0;
	}

 

Am I making sense ?

[Psycho]

You should be storing dates as date types into your database. Don't' make up a format or store them as PHP timestamp values. It will make yuur life more difficult int he long run. What type of field are you using now?

[karimali831]

You should be storing dates as date types into your database. Don't' make up a format or store them as PHP timestamp values. It will make yuur life more difficult int he long run. What type of field are you using now?

 

I changed data type to int(15) and it now stores timestamp but now need to change the WHERE clause to reflect to timestamp?

[Psycho]

how about changing it to a MySQL date/time type instead of an int? You won't be able to use any of the MySQL date functions by storing a PHP timestamp.

[litebearer]

If you insist on not following Psycho's sage advice, then you need to adjust your query to compare one timestamp against another. ie where timestamp_today is >, = or < table_timestamp_field; depending upon what you want.

[karimali831]

It would be too much bother if I did not use timestamp as the code is now adapted to fetch timestamps from database and can use date() to get the correct date format which works fine.

[Psycho]

It would be too much bother if I did not use timestamp as the code is now adapted to fetch timestamps from database and can use date() to get the correct date format which works fine.

 

No problem, that's your decision. Good luck.