Joscplan Joscplan - 1 year ago 117
MySQL Question

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

Answer Source

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('*').
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download