Select all dates within one month

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

I'm trying to build a MySQL query which selects all activities within one month. So far, I've got this:

public static function allMonthlyActivities($db, $startdate, $enddate) {
        $sql = "SELECT shortdate FROM calendar WHERE date >= $startdate and <= $enddate";
        $result = $db->listing($sql);
        return $result;
}

And the PHP executing this is:

$list = Calendar::allMonthlyActivities($_DB, '2013-02-01', '2013-02-25');

But for some reason, I get this error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in...

Here is my database structure:

How do I pass dates correctly (which format) to my SQL statement so all dates from 1 month return?

EDIT - here is my listing function

public function listing($sql) {
    $result = mysql_query($sql, $this->_connection);
    while($row=mysql_fetch_array($result)) {
      $return[] = $row;
    }
    return $return;
  }

Can you post `listing` function also ? or where you use `mysql_fetch_array` — Sarfraz, Feb 25 '12 at 13:43

Sarfraz · Accepted Answer · 2012-02-25T13:56:41.093

2

I suspect you have an error in your query, change this:

$result = mysql_query($sql, $this->_connection);

To:

$result = mysql_query($sql, $this->_connection) or die(mysql_error());

Your data type for shortDate is varchar, try changing it to date to see if problem is resolved.

Also date is reserved keyword, use backtick chars eg ` for it.

edited Feb 25 '12 at 13:56

answered Feb 25 '12 at 13:50

Sarfraz

367,681
72
526
573

The content of `shortdate` are just numbers, like 20, 21 or 22... – Michiel Feb 25 '12 at 13:50
@Michiel: The `data` is reserved keyword, try wrapping it in backtick chars eg `\`date\`` in your query – Sarfraz Feb 25 '12 at 13:52
But indeed, I do have a SQL error : `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<= 2013-02-25' at line 1` – Michiel Feb 25 '12 at 13:53
@Michiel: Try this: `SELECT shortdate FROM calendar WHERE \`date\` BETWEEN $startdate AND $enddate` – Sarfraz Feb 25 '12 at 13:55
Ok, great. This worked! Thanks – Michiel Feb 25 '12 at 13:56
Any reason why the first syntax didn't work out? Is it not valid, or? – Michiel Feb 25 '12 at 14:14
@Michiel: Not using backtick characters for `date` keyword most likely. – Sarfraz Feb 25 '12 at 14:17

score 0 · Answer 2 · answered Feb 25 '12 at 13:44

0

Are you sure you have a coloumn named date because MySQL has DATE as a keyword. Most likely your query is wrong.

answered Feb 25 '12 at 13:44

check123

1,979
2
22
28

I've added a screenshot of my table structure – Michiel Feb 25 '12 at 13:46

Select all dates within one month

2 Answers2