CodeIgniter Forums
Selecting between a date - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Selecting between a date (/thread-9494.html)

Pages: 1 2


Selecting between a date - El Forum - 06-27-2008

[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


Selecting between a date - El Forum - 06-27-2008

[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?


Selecting between a date - El Forum - 06-27-2008

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


Selecting between a date - El Forum - 06-27-2008

[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. Wink


Selecting between a date - El Forum - 06-27-2008

[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/comparison-operators.html#operator_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?


Selecting between a date - El Forum - 06-27-2008

[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
Quote: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.


Selecting between a date - El Forum - 06-27-2008

[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 ?


Selecting between a date - El Forum - 06-27-2008

[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



Selecting between a date - El Forum - 06-27-2008

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


Selecting between a date - El Forum - 06-27-2008

[eluser]SeanJA[/eluser]
My bad?

All fixed