CODEWITHSUNDEEP
phpmysqlcodeigniter
Joscplan
Joscplan

Reputation: 1034

Getting extra spaces in CodeIgniter MySQL query

The code for the query I'm trying to run is

$query = $this->db->select("*")->from('items')->like("replace(name, '=', ' ')", "foo bar", 'both')->get();

$items = $query->num_rows();

Mysql query string is

SELECT replace(name, ' = ', ' ') FROM `items`

What the code is doing is adding a blank space before and after "=" when compiling the query resulting in " = " which returns no results as there are no items with " = " in their names, only "=".

foo=bar, replace(name, '=', ' ') returns 1 result.
foo = bar, replace(name, ' = ', ' ') returns 0 results.

The version of CodeIgniter that I'm using is: 3.0.6

Upvotes: 6

Views: 2732

Answers (4)

Imran Qamer
Imran Qamer

Reputation: 2265

I used

$this->db->query()

instead of 

$this->db->select();
$this->db->order_by();
$this->db->get();

and it worked for me.

My query was:

$records = $this->db->select(
"id,( select count(*) from 
( select 
        unnest(string_to_array(mycol,',')) v
        from mytable ) t where v::int > 0) as total",
FALSE)->order_by("col1")->get("mytable1")->result_array();

Issue was, it was adding an extra space at string_to_array(mycol,', ') which was giving error.

Upvotes: 0

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

I cant find an method call replace() in associated with codeigniter or php.

Try str_replace(find,replace,string)

Example

$one = 'as = bb = cc=dd ';
$var = str_replace('=', ' ', $one);
echo $var;

phpfiddle Preview

And don't refactor your code inside your query. Make it clean and pass it to query. Its easy to track errors too.

Upvotes: 2

user2560539
user2560539

Reputation:

Your syntax 

$query = $this->db->select("*")->from('items')->like("replace(name, '=', ' ')", "foo bar", 'both')->get();

as shown here in fact is producing this query

SELECT * FROM `items` WHERE replace(name, '=', ' ') LIKE '%foo bar%';

If there is by accident one more space between foo and bar it fails.

Try this alternative query

SELECT * FROM `items` WHERE replace(name, '=', ' ') LIKE '%foo% bar%';

CodeIgniter

$query = $this->db->select('*')->from('items')->like("replace(name, '=', ' ')", "foo% bar", 'both')->get();

OR this query even better

SELECT * FROM `items` WHERE CONCAT(TRIM(SUBSTRING_INDEX(name, '=', 1)),' ',TRIM(SUBSTRING_INDEX(name, '=', -1))) LIKE '%foo bar%';

Don't use this

SELECT * FROM `items` WHERE replace(name, '=', ' ') LIKE '%foo%bar%';

The results of those queries are shown here

Either you could have a query that removes leading/trailing space (Example)

Upvotes: 2

Platinum Fire
Platinum Fire

Reputation: 512

I have tested this on my copy of CodeIgniter and when formatted like this it works just fine:

$items = $this->db->like("replace(name, '=', ' ')", "foo bar", 'both')->get('items')->num_rows();

A few notes:

  1. You don't need from(), you can just slip the table into the get() method.
  2. You can chain your methods so you only need to assign one variable.
  3. Omitting the select() method implies select('*').

Upvotes: 7

Related Questions