SELECT with date conversion does not work

Hey,

I am trying to Select from a database where the date is in the format of “March 2010”…

I have the following code:

Function


    public function selectAllArchiveEventsByDate($date){

        $sql = "SELECT * FROM tbl_events 
        WHERE deleted = 0 AND 
        date_format('date', '%F-%Y') = '$date'";
        $result = mysql_query($sql);
        return $result;
    }

Then on the page itself i do this:


      	<?
      		$event = Event::selectAllArchiveEventsByDate($_GET['archive']);
      		$i = 0;
      		
      		while($row = mysql_fetch_array($event)){
	                ?>
					<h4><?=$row['title']?></h4>  
					<p><em><?=$date?></em></p> 
					<p><?=$row['body']?></p>  
	                <?  
	        }

The $_GET[‘archive’] is in the querystring, a typical example is shown below:

http://www.glofamily.com/inner/events/?archive=March%202010

You will see i don’t get any errors, so i don’t understand why it does not work. In my database the ‘date’ column has dates like this: 2010-03-26 10:30:00

Any ideas why the code does not work?

Thanks

A couple issues:

  1. There is no %F format

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

  1. You are formatting the literal word “date” not the column because you put single quotes around it. Don’t put quotes around column names

  2. ‘%F-%Y’, if it worked the way you thought, would’ve been “March-2010” which is not equal to “March 2010”.

Try DATE_FORMAT(date, ‘%M %Y’)

  1. This is going to be a very inefficient query, too inefficient to run if the site becomes popular or the database grows to more than a couple hundred rows. You want to be able to compare the dates directly as comparing the result of a function applied to the column requires scanning every row to evaluate the function. Instead transform the date in PHP so that you can produce a query that just compares it to the date, with greater than and less than.

Hey,

Thanks, well i will get this working for the time being as i need to set the site live, and will change it to the way you have suggested very soon :wink:

First things first, i have changed the code and i get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /domains/glofamily.com/http/inner/events/index.php on line 25

Referring to this line:


while($row = mysql_fetch_array($event)){

I tried to output the sql ‘or die($sql)’ and i got this:

SELECT * FROM tbl_events WHERE deleted = 0 AND DATE_FORMAT(date, ‘%M %Y’) = March 2010

Any ideas?

The “March 2010” part is a literal string and needs to be enclosed in single quotes

Hey,

Thanks, that worked perfectly. I will change it soon.

Thanks again.

to be efficient, you need to change it as follows (example for march 2010):

WHERE `date` &gt;= '2010-03-01'
  AND `date`  &lt; '2010-04-01'

note “less than” on the upper bound, which is the first day of the following month

that way you don’t have to worry about february 29th and all that stuff :wink: