How to query a database table using PHP variables as values with CodeIgniter

Question

How can I get values between two dates in Codeigniter query function? Here is my model and sample code.

function get_promo() {
    $today = date('Y-m-d');
    $query = $this->db->query('SELECT FROM tbl_event WHERE event_id = $id AND event_startdate <= $today
    AND event_enddate >= $today');
    return $query;
}

But it doesn't work, here is the error I got

A Database Error Occurred

Error Number: 1064

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 'FROM tbl_event WHERE event_id = 36 AND event_startdate <= 2011-06-09 ' at line 1

SELECT FROM tbl_event WHERE event_id = 36 AND event_startdate <= 2011-06-09 AND event_enddate >= 2011-06-09

Filename: C:\xampp\htdocs\hotel\system\database\DB_driver.php

Line Number: 330

Reading your handwritten SQL from left to right, I see that you are missing the *between SELECT and FROM. This explains the error in your question. The next problem that you'll have is about quoting your values. You should not be injecting PHP variables into your SQL string this way -- it is insecure/unstable. — mickmackusa
– mickmackusa ♦, Commented Jul 4 at 0:41
Topically relevant Construct a CodeIgniter WHERE condition with the value BETWEEN two column values and Convert raw SQL with CONCAT(), JOINs, and BETWEEN to CodeIgniter active record syntax — mickmackusa
– mickmackusa ♦, Commented Jul 4 at 6:59

danneth · Accepted Answer · 2011-06-09 08:43:23Z

7

I think you need qoutes around your date (i.e. '2011-06-08'). try this

function get_promo() {
    $today = date('Y-m-d');  
    $query = $this->db->query(
        "SELECT FROM tbl_event WHERE event_id = {$id} AND event_startdate <= '{$today}'
        AND event_enddate >= '{$today}'");
    return $query;
}

If your columns event_startdate and event_enddate are DATETIME type but you are only interested in the date part you can do `DATE(event_enddate) to extract the date part

answered Jun 9, 2011 at 8:43

danneth

2,7911 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manfred Radlwimmer · Accepted Answer · 2017-05-27 18:50:15Z

3

$this->db->where('date_start <=',date('Y-m-d'));
$this->db->where('date_end >=',date('Y-m-d'));
$query = $this->db->get('table');

edited May 27, 2017 at 18:50

Manfred Radlwimmer

13.4k13 gold badges55 silver badges65 bronze badges

answered May 27, 2017 at 18:26

CMDR

312 bronze badges

Comments

Aman · Accepted Answer · 2011-06-09 05:51:44Z

1

I think u need to user date_format(), more information in this link http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format.

Try this code:

$today = date('Y-m-d');
$query = $this->db->query("SELECT FROM tbl_event WHERE event_id = $id AND DATE_FORMAT(event_startdate ,'%Y-%m-%d') >= DATE_FORMAT($today ,'%Y-%m-%d') AND DATE_FORMAT(event_enddate ,'%Y-%m-%d') <= DATE_FORMAT($today ,'%Y-%m-%d')");

answered Jun 9, 2011 at 5:51

Aman

1,6523 gold badges15 silver badges26 bronze badges

1 Comment

idontknowhow Over a year ago

Thanks for the answer I tried it but I didn't get result.. . I do have a event_start 2011-06-09 and even_enddate 2011-06-30 but it doesn't show

Madan Sapkota · Accepted Answer · 2022-12-22 18:25:02Z

There are issues with your queries because they are not correctly escaped. To fix this, try adding single or double quotes. You can also consider using Query Binding or Active Record, both simple and secure methods for handling queries. @danneth answer may be helpful in this regard. Here are some examples of how to use binding and Active Record based on your code.

Query Binding Example

    $today = date('Y-m-d');
    $sql = 'SELECT 
  * 
FROM
  tbl_event 
WHERE event_id = ? 
  AND event_startdate <= ? 
  AND event_enddate >= ?';
    $query = $this->db->query($sql, array($id, $today, $today));
return $query;

Active Record Example

$query = $this->db
        ->select('*')->from('tbl_event')
        ->where(array(
            'event_id' => $id,
            'event_startdate <= ' => $today,
            'event_enddate >= ' => $today
        ))
        ->get();
return $query;

Append ->result() or ->result_array() etc to get the result in an object or array. Look into the Codeigniter Database User Guide for more.

Femi · Accepted Answer · 2011-06-09 05:44:40Z

0

You need to use the right format. Try this:

$todaystart = date('Y-m-d 00:00:00');  
$todayend = date('Y-m-d 23:59:59');  
       $query = $this->db->query('SELECT FROM tbl_event WHERE event_id = $id AND event_startdate <= ? AND event_enddate >= ?', array($todaystart, $todayend));

EDIT: your query is wrong. Do this:

$todaystart = date('Y-m-d 00:00:00');  
$todayend = date('Y-m-d 23:59:59');  
       $query = $this->db->query('SELECT * FROM tbl_event WHERE event_id = $id AND event_startdate <= ? AND event_enddate >= ?', array($todaystart, $todayend));

edited Jun 9, 2011 at 5:44

answered Jun 9, 2011 at 5:04

Femi

64.8k8 gold badges120 silver badges150 bronze badges

1 Comment

idontknowhow Over a year ago

It's now working but I didn't get any query i do have on my database event_start 2011-06-09 and even_enddate 2011-06-30 but it doesn't show

mickmackusa · Accepted Answer · 2025-07-05 21:49:08Z

Because databases can be used to access the current date, you only really should to pass in the PHP variable $id as a parameter. In truth, after you add the missing * between SELECT and FROM and leverage CURRENT_DATE twice, if $id in integer, your query won't break because integer values don't need quoting.

public function getPromo(int $id): array
{
    return $this->db->query('
        SELECT *
        FROM tbl_event
        WHERE
            event_id = ?
            AND event_startdate <= CURRENT_DATE
            AND event_enddate >= CURRENT_DATE
    ', [$id])
    ->result();
}

Or with query builder methods, you can freely use variables because identifiers and values will be automatically quoted/escaped (unless you turn auto-quoting off with false):

public function getPromo(int $id): array
{
    return $this->db
        ->where('event_id', $id)
        ->where('event_startdate <=', 'CURRENT_DATE', false)
        ->where('event_enddate >=', 'CURRENT_DATE', false)
        ->get('tbl_event')
        ->result();
}

Collectives™ on Stack Overflow

How to query a database table using PHP variables as values with CodeIgniter

6 Answers 6

Comments

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related