[SOLVED] CodeIgniter active record query to SELECT rows where a column value is the original or sanitized version of the search term

CodeIgniter active record query to SELECT rows where a column value is the original or sanitized version of the search term

I have the following details in my database table field as 'company_name'=>'Test Company'.

I want to return true after comparing the input data(either 'Test Company' or 'TestCompany') , because of the existence of that string (test company).

Please suggest a query for finding the existence or non-existence. I have used the following, but it didn't work in all the scenarios.

$companyname = "TestCompany";

$this->db->select("*"); 
$companynames = str_replace(' ', '', $companyname); 
$where = "company in ('$companyname','$companynames')" ;
// $where = "'company' = $companyname OR 'company' = $companynames'";
$this->db->where($where);
$query = $this->db->get('users');
$val = $query->result_array();
if (empty($val)) { 
    echo '1'; //non-existence   of 'TestCompany' and 'Test Company'
} else {
    echo '2'; //existence of 'TestCompany' or 'Test Company'
}

The above code always prints '1' even if there is 'Test Company' exists in the database.

Note:The issue is when I select with 'TestCompany', the query will be like this:

$where = "'company' = TestCompany OR 'company' = TestCompany'";

but the database contains 'Test Company' and the query returns no results. I want to solve this condition.

Solution

$companyNameTmp =  str_replace(' ', '', $companyname);
$query = mysql_query("
    SELECT * FROM `items` WHERE  REPLACE(`companyname`,' ','')='$companyNameTmp'
    ");
if(mysql_num_rows($query) >0)
{
    echo 'hi';
}

REPLACE doesn't work with active record so you have to do it in a normal mysql query. Rather than doing the OR Sudhir's solution of just removing the spaces from the incoming variable makes more sense. Then compare that to the database field with the spaces removed.