CodeIgniter active record missing FROM clause

Question

I have the following:

/** Load necessary stuff **/
$this->load->helper('date');
    
$this->db->get('site_requests');
//echo mdate('%Y-%m-%d %H:%i:%s', now());
//die;
$this->db->where("(created_for <= " . "'2019-04-24 18:47:03'" . ")");
$this->db->get();
print_r($this->db->last_query());

But I receive the following:

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 'WHERE (created_for <= '2019-04-24 18:47:03')' at line 2

SELECT * WHERE (`created_for` <= '2019-04-24 18:47:03')

Filename: modules/sound/models/Sound_request_model.php

Line Number: 35

What am I doing wrong?

Enoch · Accepted Answer · 2019-04-25 08:18:50Z

1

You can put it into one statement

    $this->db->get_where('site_requests', array('created_for <=', '2019-04-24 18:47:03'));
    print_r($this->db->last_query());

You will need to chain ->result() or result_array() or any outputting function to get the data return.

Hopefully this helps

answered Apr 25, 2019 at 8:18

Enoch

1,0617 silver badges16 bronze badges

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

Comments

whisky · Accepted Answer · 2019-04-24 23:47:50Z

0

$this->db->where("(created_for <= " . "'2019-04-24 18:47:03'" . ")");

should be after

$this->db->get('site_requests');

answered Apr 24, 2019 at 23:47

whisky

5332 gold badges6 silver badges14 bronze badges

Comments

Juan Carlos Ramirez · Accepted Answer · 2019-04-25 00:55:12Z

0

Try using the key-value form:

$this->db->where("created_for <=", "2019-04-24 18:47:03");

answered Apr 25, 2019 at 0:55

Juan Carlos Ramirez

2,1551 gold badge10 silver badges22 bronze badges

Comments

Alex · Accepted Answer · 2019-04-25 01:08:03Z

0

You are missing defining the FROM part of query. I think you just need to change your code:

    $query = $this->db->from('site_requests')
       ->where("(created_for <= " . "'2019-04-24 18:47:03'" . ")")
       ->get();
    $result = $query->result();
    print_r($result);

answered Apr 25, 2019 at 1:08

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Comments

mickmackusa · Accepted Answer · 2024-08-31 11:33:31Z

Your first call of get(), which includes the table name, executes a SELECT query. Because there are no other query builder methods called before it, the rendered SQL is SELECT * FROM site_requests.

In the CI_DB_query_builder class, the get() method resets the built SELECT query after executing the query by calling protected method _reset_select().

Your subsequent calls of where() then get() (without a table value) effectively executes

SELECT * WHERE (`created_for` <= '2019-04-24 18:47:03')

because the default SELECT clause is applied, but the query builder never received a table declaration for the new SELECT query.

Ultimately, the most elegant/concise why to build the query, execute it, and return the array of zero or more objects would be:

return $this->db
    ->get_where('site_requests', ['created_for <=' => '2019-04-24 18:47:03'])
    ->result();

The rendered SQL will resemble:

SELECT *
FROM `site_requests`
WHERE `created_for` <= '2019-04-24 18:47:03'

If you actually want to use the SQL function NOW(), then you will need to separately call where() and get() so that quoting can be turned off for the function call.

return $this->db
    ->where('created_for <=', 'NOW()', false)
    ->get('site_requests')
    ->result();

Collectives™ on Stack Overflow

CodeIgniter active record missing FROM clause

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related