Database problems using model in CodeIgniter [duplicate]

Question

Again a question about CodeIgniter. I'm trying to get information out of my database, which is not that dificult. I get all the data but want to add an condition, the author_id has to be the same as $id. My code:

<?php
class BookModel extends CI_Model {

    public function get_book($id){
        $this->load->database();
        $query = $this->db->query('SELECT book_id, book_title, book_publisher, book_summary FROM books WHERE author_id = $id');
            return $query->result();
        }
}
?>

If I echo $id it shows my ID. But in the SQL function it is failing. Also, when i hardcode a number like

WHERE author_id = 1

It load proberly.

This is the error i get:

Error Number: 1054

Unknown column '$id' in 'where clause'

SELECT book_id, book_title, book_publisher, book_summary FROM books WHERE author_id = $id

What am I doing wrong?

Just a suggestion: Instead of using $this->load->database(); why not autoload so you dont have to place this $this->load->database(); every where $autoload['libraries'] = array('database'); — user4419336
– user4419336, Commented May 1, 2017 at 9:29

Mitya · Accepted Answer · 2017-05-01 09:36:59Z

1

You have two problems.

Firstly, MySQL uses single = for both assignment and comparison, not ==.

Secondly, variables in PHP are parsed only in double-quoted strings, not single.

So '...$id' will be parsed literally as $id, not the value of the variable $id. Convert to double quotes.

Or, use CI binding - it's better for security, and you don't have to worry about escaping, quotes etc.

$sql = 'SELECT ... FROM books WHERE author_id = ?';
$query = $this->db->query($sql, array($id));

edited May 1, 2017 at 9:36

answered May 1, 2017 at 9:28

Mitya

34.7k15 gold badges70 silver badges125 bronze badges

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

2 Comments

J Tempelman Over a year ago

Fixed that, but it still don't work. The new error: Error Number: 1054 Unknown column '$id' in 'where clause' SELECT book_id, book_title, book_publisher, book_summary FROM books WHERE author_id = $id

Mitya Over a year ago

Updated. You need to brush up on some basics, it seems. You have some elementary errors currently.

Sujan Poudel · Accepted Answer · 2017-05-01 09:56:24Z

1

since you are using codeigniter,use the ci database class functions,they will make your task simpler and you have not to worry for sql injection .You can do it in simpler way like this

    public function get_book($id)
      {
        $this->load->database();
        $this->db->select('book_id, book_title, book_publisher, book_summary');
        $this->db->from('books');
        $this->db->where('auther_id',$id);
        $data=$this->db->get();
        return $data->result();
       }

answered May 1, 2017 at 9:56

Sujan Poudel

8637 silver badges19 bronze badges

2 Comments

J Tempelman Over a year ago

Thanx this was very helpful too! Do you know how it works with updating information. I'm really stuck in that :(

Sujan Poudel Over a year ago

Here is the CI documentation for database calss function check them out codeigniter.com/user_guide/database/helpers.html codeigniter.com/user_guide/database/index.html spend some time reading theme,i hope it will be helpful

Hothi Jimit · Accepted Answer · 2017-05-01 09:33:35Z

0

change this

$query = $this->db->query("SELECT book_id, book_title, book_publisher, book_summary FROM books WHERE author_id =".$id);

answered May 1, 2017 at 9:33

Hothi Jimit

3657 silver badges21 bronze badges

Collectives™ on Stack Overflow

Database problems using model in CodeIgniter [duplicate]

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Related