date comparison

[euel]

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!

[dharmeshpat]

$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."'";

 

 

[euel]

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?