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

Problem with CI db query with parentheses/brackets [SOLVED] - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Problem with CI db query with parentheses/brackets [SOLVED] (/showthread.php?tid=37308)

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-05-2011

[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

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-05-2011

[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.

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-05-2011

[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?
-

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-05-2011

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

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

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-05-2011

[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

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-05-2011

[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)'

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-06-2011

[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));

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 01-06-2011

[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.

Problem with CI db query with parentheses/brackets [SOLVED] - El Forum - 02-18-2011

[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 )