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%
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 . '%')
);
You need to surround %moscow% with quotes:
select * from "Cities" where "defaultName" ilike' %Moscow%'
$data = $ci->crud_model->query("select * from \"Cities\" where \"defaultName\" ilike' %$str%'");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/
ilike? Is it a more personified version of the SQLlike?