Jump to content

date comparison


euel

Recommended Posts

Hi, its me again!

So i have this function which gets all the events in my db according to its mall_id and a date (year-month).

I merged the $month and $year so i can compare it to the date field in my table.

I used DATE_FORMAT to modify my date field so i can compare them but it doesn't seem to work perfectly,

 

function select_all_events($mall_id, $month, $year, $public)
{
global $connection;
	$monthname = $month;
	$monthnum = date("n", strtotime("01-".$monthname."-2011 00:00:00"));
	if($monthnum < 10)
	{
		$monthnum = "0" . $monthnum;
	}
	$selected_date = $year . "-" . $monthnum;	
	$query = "SELECT *, DATE_FORMAT(date ,'%Y-%m' ) AS date ";
	$query .= "FROM table ";
	$query .= "WHERE malls_id=" . $mall_id;
	$query .=" AND date =" . $selected_date;
	if($public == true)
	{
	$query .= " AND visible = 1";	
	}
	$query .= " ORDER BY date DESC";
	$result_set = mysql_query($query, $connection);
	confirm_query($result_set);
	return $result_set;
}

 

I tried echoing $selected_date, the format is correct, the problem seems to be the comparison..

Also i tried doing it like this:

 

	$query = "SELECT * ";
	$query .= "FROM table ";
	$query .= "WHERE malls_id=" . $mall_id;
	$query .=" AND DATE_FORMAT(date ,'%Y-%m' ) =" . $selected_date;

 

but the results are mismatched, like when i choose 2012 January it display's 2011 December ...

This dates are making my head dizzy and costing me time..lol  ::)

Any ideas?

Thanks in advance!

Link to comment
Share on other sites

$query = "SELECT * ";

$query .= "FROM table ";

$query .= "WHERE malls_id=" . $mall_id;

$query .=" AND DATE_FORMAT(date ,'%Y-%m' ) =" . $selected_date;

 

the above query has to be modified as below

 

$query = "SELECT * ";

$query .= "FROM table ";

$query .= "WHERE malls_id=" . $mall_id;

$query .=" AND YEAR(date) = '".$year."' AND MONTH(date) = '".$month."'";

 

 

Link to comment
Share on other sites

Thanks dharmeshpat! I tried your code but it gave me an error so i modified it to:

 

$query = "SELECT * ";
     	$query .= "FROM events ";
      	$query .= "WHERE malls_id=" . $mall_id;
      	$query .=" AND YEAR(date) =" . $year . " AND MONTH(date) = " . $monthnum;

 

and it works!!

I was thinking to do it like that in the 1st place but i read DATE_FORMAT() so i tried it.

But in case i want to use DATE_FORMAT() will it work? how?

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.