CODEWITHSUNDEEP
phpsqlcodeigniterpostgresql
Boris Kuzevanov
Boris Kuzevanov

Reputation: 1302

Postgres, error with query

I do one query and I have syntax error. But for me I do all right.

Where I have error?

Thanks!

$str = "Moscow";
$data = $ci->crud_model->query(
    'select * from "Cities" where  "defaultName" ilike  %'.$str.'%'
);

Query is : select * from "Cities" where "defaultName" ilike %Moscow%

Upvotes: 0

Views: 97

Answers (3)

IMSoP
IMSoP

Reputation: 97678

The LIKE/ILIKE operator takes two strings as its arguments. That is, the pattern has to be a quoted string, not just directly in the SQL query.

So instead of:

"defaultName" ilike %Moscow%

You need:

"defaultName" ilike '%Moscow%'

In PHP, you should be (at the very least) escaping the input to avoid SQL Injection. Probably CodeIgniter has facilities for escaping, or using parameterised queries, but at the very least you should do this:

$str = "Moscow";
$data = $ci->crud_model->query(
    'select * from "Cities" where  "defaultName" ilike  \'%'.pg_escape_string($str).'%\''
);

EDIT Per Craig Ringer's comment, the correct ways to escape or build safe queries with CodeIgniter are covered in this answer.

This is probably the simplest (note that the query parameter is automatically a string, and doesn't need extra quotes):

$str = "Moscow";
$data = $ci->crud_model->query(
    'select * from "Cities" where  "defaultName" ilike ?',
    array('%' . $str . '%')
);

Upvotes: 3

Naincy
Naincy

Reputation: 2943

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard. Make it LIKE in place of ilike

$str = "Moscow";
$data = $ci->crud_model->query('select * from Cities where defaultName LIKE  %$str%');

Then you Query will be 

select * from Cities where defaultName LIKE '%Moscow%';

For ILIKE: Example -

SELECT first_name,last_name FROM customer WHERE first_name ILIKE 'BAR%';

Here it will return row as per this condition:

The BAR% pattern matches any string that begins with BAR, Bar, BaR, etc. If you use the LIKE operator instead, the query will not return any row. Reference: http://www.postgresqltutorial.com/postgresql-like/

Upvotes: -1

Neville Kuyt
Neville Kuyt

Reputation: 29619

You need to surround %moscow% with quotes:

select * from "Cities" where "defaultName" ilike' %Moscow%'

Upvotes: 1

Related Questions