Selecting between a date

[eluser]E303[/eluser]
I was able to run this query in phpMySQLAdmin but when it comes to getting CI to run the query nothing seems to work.

Code:

$sql = 'SELECT * FROM `exhibitions` WHERE \'2008-06-27\' BETWEEN `date` AND `endDate`';

Where you see 2008-06-27 I want to place todays date...

eg

Code:

$today = date("Y-m-d");

I get errors either there is no column called '2008-06-27' or like at the moment I don't get any results back.

Thank in advance

[eluser]xwero[/eluser]
It looks like an odd query because the BETWEEN is used for: look in the field for a value BETWEEN some value AND another value.
So the error seems to be correct. What is the query outputting in phpMySQLAdmin?

[eluser]E303[/eluser]
The query in phpMySQLAdmin is output the desired result. That is why I thought it would work.

[eluser]SeanJA[/eluser]
If you can't get that way to work, why not try doing it this way?

Code:

'SELECT * FROM `exhibitions` WHERE '$date'  >= `date` AND '$date' <= `endDate`';

I am not actually familliar with the BETWEEN keyword, I shall have to look it up.

[eluser]SeanJA[/eluser]
Ummm... according to the MySQL reference manual it looks like between returns a boolean answer...


http://dev.mysql.com/doc/refman/5.0/en/c...or_between

Code:

mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0

Is that what you are looking for?

[eluser]E303[/eluser]
Thanks SeanJa

I put the code in as follows

Code:

$date = date("Y-m-d");
                        
            $this->db->query("SELECT * FROM exhibitions WHERE '$date'  >= date AND '$date' <= endDate");
        
            $whats>db->get();
            return $whatsOn_data->result_array();

Now i get this error

An Error Was Encountered

Error Number: 1096

No tables used

SELECT *

The idea is the exibition is set to run between 1-jun-08 and the 1-jul-08 for example. So while the date is between that date range then the exhbition is displayed.

[eluser]SeanJA[/eluser]

Code:

$date = date("Y-m-d");

$this->db->query("SELECT * FROM exhibitions WHERE '$date'  >= date AND '$date' <= endDate");

$whats>db->get();

return $whatsOn_data->result_array();

Shouldn't $whatsOn be $whats ?

[eluser]SeanJA[/eluser]
Actually, I think this might work better...

Code:

$query = $this->db->query("SELECT * FROM exhibitions WHERE startDate <= '$date' AND endDate >= '$date'");
$result = $query->result();
return $result; //return an array of the exibitions that are between startDate and endDate

[eluser]louis w[/eluser]
Why are you treating your column names as php variables?

[eluser]SeanJA[/eluser]
My bad?

All fixed