Variable in Query?

[eluser]JimmyJ[/eluser]
Hi, i'm trying to pass an id variable to an sql query, but gives me the error:

Error Number: 1054

Unknown column '$id' in 'where clause'

SELECT * FROM pages WHERE pageID=$id

The code is:

Code:

<?php

class Page extends Controller {

    function Page()
    {
        parent::Controller();    
    }
    
    function view()
    {
        
    /* Get Template */
    $data['doctypes'] = $this->load->view('template/doctypes', '', true);
    $data['head'] = $this->load->view('template/head', '', true);
    $data['foot'] = $this->load->view('template/foot', '', true);
    
    /* Get DB Content */
    $id = $this->uri->segment(2);
    $query = $this->db->query('SELECT * FROM pages WHERE pageID=$id');
    $row = $query->row();  
    $data['metaTitle'] = $row->metaTitle;
    $data['pageTitle'] = $row->pageTitle;
        
    $this->load->view('page', $data);
    
    }
}
?>

What am I doing wrong? I've looked through the database documentation but the examples are a little limited. It works fine in my head

Any ideas?

[eluser]JamesD[/eluser]
[quote author="JimmyJ" date="1197764942"]Hi, i'm trying to pass an id variable to an sql query, but gives me the error:

Error Number: 1054

Unknown column '$id' in 'where clause'

SELECT * FROM pages WHERE pageID=$id

The code is:

Code:

<?php

class Page extends Controller {

    function Page()
    {
        parent::Controller();    
    }
    
    function view()
    {
        
    /* Get Template */
    $data['doctypes'] = $this->load->view('template/doctypes', '', true);
    $data['head'] = $this->load->view('template/head', '', true);
    $data['foot'] = $this->load->view('template/foot', '', true);
    
    /* Get DB Content */
    $id = $this->uri->segment(2);
    $query = $this->db->query('SELECT * FROM pages WHERE pageID=$id');
    $row = $query->row();  
    $data['metaTitle'] = $row->metaTitle;
    $data['pageTitle'] = $row->pageTitle;
        
    $this->load->view('page', $data);
    
    }
}
?>

What am I doing wrong? I've looked through the database documentation but the examples are a little limited. It works fine in my head

Any ideas?[/quote]

Hello JimmyJ,

The variable is enclosed with single quotes.

Here are a couple ways to correct it, for example...
Instead of:

Code:

$query = $this->db->query('SELECT * FROM pages WHERE pageID=$id');

Try using

Code:

$query = $this->db->query('SELECT * FROM pages WHERE pageID=' . $id);

OR

Code:

$query = $this->db->query("SELECT * FROM pages WHERE pageID=$id");

-JamesD

[eluser]darkera13[/eluser]
[quote author="JimmyJ" date="1197764942"]
$query = $this->db->query('SELECT * FROM pages WHERE pageID=$id');
[/quote]

Must be

Code:

$query = $this->db->query("SELECT * FROM pages WHERE pageID=$id");

Must put a variable in double quote ", not in single quote '.

[eluser]JimmyJ[/eluser]
Phew that worked. Thanks Folks! Funny how it's always something simple

[eluser]tonanbarbarian[/eluser]
for best speed and security use this

Code:

$query = $this->db->query('SELECT * FROM pages WHERE pageID='.(int)$id);

using single quotes is faster in php because if you use double quotes PHP must search the string for variables to replace which is slower than just concatenating a variable onto the end of the string.
Also casting the variable as an int is the safest thing to do to stop any sort of cross site scripting (i.e. xss)
so even though $this->uri->segment() may do an xss_clean (manual is unclear if this is done or not and I havent checked the code yet) if you cast the variable any malicious value entered will be converted to a number and will not cause any harm

Just my thoughts on best practice

[eluser]ejangi[/eluser]
You can also use query-bindings, which takes care of escaping as well - though in this instance (because we're casting) it's probably overkill:

Code:

$query = $this->db->query('SELECT * FROM pages WHERE pageID = ?', array((int) $id));