Problem with CI db query with parentheses/brackets [SOLVED]

[eluser]coldfire82[/eluser]
Hi,

I dont know what causing this problem. But apparently, it is produced as,

SQL Query:

Code:

SELECT id FROM cars Where name = 'BMW (series 5)'

On simply running this query on phpmyadmin/mysql, it returns the right result and gives the 'id'.

But, from my model, when I query the same as,

Code:

$sql="SELECT id FROM cars Where name = $car_name";
$query = $this->db->query($sql);

Above, the car name is db->escaped so it will be like this, "SELECT id FROM cars Where name = 'BMW (series 5)'"

The above query is correct but it returns 0 results, which is wrong. On removing the parentheses around 'series 5', it works fine. Why?

Is there anything I am missing regarding CI db query not entertaining the parentheses/brackets?

Thanks

[eluser]Cristian Gilè[/eluser]
Hi coldfire82,

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

[eluser]coldfire82[/eluser]
[quote author="Cristian Gilè" date="1294271056"]Hi coldfire82,

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.[/quote]

Code:

$this->db->where('name', $name,FALSE);
$query = $this->db->get('cars');

Trying the above, its still not working for where clause having parentheses.

does that work for you? can someone confirm if this works?

One more thing, how can I echo the last sql query executed?
-

[eluser]Victor Michnowicz[/eluser]
I think something like this should work for you:

Code:

$sql = "SELECT id FROM cars WHERE name = '$car_name'";

[eluser]coldfire82[/eluser]
[quote author="elvicmic" date="1294282097"]I think something like this should work for you:

Code:

$sql = "SELECT id FROM cars WHERE name = '$car_name'";

[/quote]

As I mentioned earlier, query is not the issue.

SELECT id FROM cars WHERE name = 'BMW (series 1)' works OK as standalone.

But, it doesnt work using CI's db->query

[eluser]lennierb5[/eluser]
I just tried the code below and the query returns the correct row id. I am using CI 2.0 though so not sure if that is part of it:

Code:

function test() {
        $car_name = 'BMW (series 5)';
        $sql="SELECT id FROM cars Where name = '$car_name'";
        $query = $this->db->query($sql);
        $row = $query->row();
        echo $row->id;
    }

The query created by CI is:

Code:

SELECT id FROM cars Where name = 'BMW (series 5)'

This code also produced working results:

Code:

function test() {
        $car_name = 'BMW (series 5)';
        $this->db->select('id');
        $this->db->where('name', $car_name);
        $query = $this->db->get('cars');
        $row = $query->row();
        echo $row->id;
    }

Query created by CI:

Code:

SELECT `id`
FROM (`cars`)
WHERE `name` = 'BMW (series 5)'

[eluser]Atharva[/eluser]
[quote author="coldfire82" date="1294274701"][quote author="Cristian Gilè" date="1294271056"]

One more thing, how can I echo the last sql query executed?
-[/quote]

Code:

echo $this->db->last_query();

will give you the last query executed.

Regarding your problem, you can try following code

Code:

$sql="SELECT id FROM cars Where name = ?";
$query = $this->db->query($sql,array($car_name));

[eluser]coldfire82[/eluser]
[quote author="Atharva" date="1294319466"][quote author="coldfire82" date="1294274701"][quote author="Cristian Gilè" date="1294271056"]

One more thing, how can I echo the last sql query executed?
-[/quote]

Code:

echo $this->db->last_query();

will give you the last query executed.

Regarding your problem, you can try following code

Code:

$sql="SELECT id FROM cars Where name = ?";
$query = $this->db->query($sql,array($car_name));

[/quote]

Thanks atharva. Your suggestion works.

[eluser]waykay[/eluser]
I have just had the same problem. I was passing a get string that had brackets/parentheses around a word e.g. Julie(001). I solved it by doing a replace on the string to make the brackets real brackets

this worked:

$fname = This contained my GET string;
$fname2 = str_replace("(","(", "$fname"); // replace (
$fname2 = str_replace(")",")", "$fname2"); // replace )