CodeIgniter Query Bindings

Hightower · 08-20-2009, 02:18 PM

Ok, so I should maybe post this on CI forums, but I like this place too much.

I have the following in my CI script:

PHP Code:


			
$sql = "SELECT articles.body, articles.user_id, users.username FROM articles, users WHERE ? = 1 AND articles.user_id = users.user_id ORDER BY ?";



$query = $this->db->query($sql, array('item1', 'item2'));

echo $sql;

The output from the echo however is:

Code:

SELECT articles.body, articles.user_id, users.username FROM articles, users WHERE ? = 1 AND articles.user_id = users.user_id ORDER BY ?

It's not replacing the question marks. I've checked the code, and it looks ok.

Anybody got any ideas?

Salathe · 08-20-2009, 02:47 PM

Why would you expect the value in $sql to change? It has been many years since I even touched CI code but the replacements should be done internally. Does $query contain what you expect it to contain?

Hightower · 08-20-2009, 02:53 PM

Query Bindings:

http://codeigniter.com/user_guide/database/queries.html

Bottom of this page

ioan1k · 08-20-2009, 03:14 PM

The ? will be replaced once the query is executed automatically, it also may be that CodeIgnitor does not bind field names only values even though the same has been provided, as the documentation shows values being replaced.

sketchMedia · 08-20-2009, 04:02 PM

does the $this->db->query method take in the sql variable by reference? I'm guessing not.
I don't know, never used CI.

Hightower · 08-21-2009, 07:45 AM

Quote:

Originally Posted by sketchMedia

does the $this->db->query method take in the sql variable by reference? I'm guessing not.
I don't know, never used CI.

I'm not exactly sure - all I know is my code is the exact syntax as their example.

sketchMedia · 08-21-2009, 10:37 AM

It doesn't pass byRef just looked through the source, do you understand what I'm getting at?

Take this example:

PHP Code:


			
$sql = "SELECT username FROM users WHERE password = '?'"; 

function query($sql, $repl)
{
    $sql = str_replace('?', $repl, $sql);
}

function queryref(&$sql, $repl)
{
    $sql = str_replace('?', $repl, $sql);
}


query($sql, 'cheese');
echo $sql . '<br />'. '<br />'. '<br />';


queryref($sql, 'cheese');
echo $sql;

Results:

Code:

SELECT username FROM users WHERE password = '?'


SELECT username FROM users WHERE password = 'cheese'

Basically the $sql variable in your example is in a different namespace to $this->db->query's $sql variable (as method/function params are passed byVal by default) therefore when you echo $sql; out it will still equal the value you set.

To replicate the behaviour you want/need, you could do a call-time-pass-by-reference call:

PHP Code:


			
$sql = "SELECT articles.body, articles.user_id, users.username FROM articles, users WHERE ? = 1 AND articles.user_id = users.user_id ORDER BY ?"; 

$query = $this->db->query(&$sql, array('item1', 'item2')); 
echo $sql;

This is apparently deprecated behavior however and may result in a warning.

Having not tested CI i can't be 100% thats why it isnt working, but im pretty sure (after a quick skim read of the code)

Hightower · 08-21-2009, 11:30 AM

Posted on CI in the end, was a complete school boy error.

Was echoing $sql which doesn't change. Just needed to echo $this->db->last_query() which worked spot on.

Thanks for all the assistance