CODEWITHSUNDEEP
phpmysqlsql
Jason Fel
Jason Fel

Reputation: 991

How can I ignore spaces in mysql search query with LIKE?

I am working on a program where a user searches for a "course" by course code or name. I am trying to make it so if the user searches "CODE1002", CODE 1002 EL from the database will still return (there is a space). As indicated by other posts I have tried to use the REPLACE function with no success.

Here is my current code (however it does not ignore spaces):

$records = $conn->prepare('SELECT (SELECT COUNT(*) FROM courses WHERE (course_code LIKE :searchText OR course_name LIKE :searchText)) AS course_count, course_id, course_code, course_name FROM courses WHERE (course_code LIKE :searchText OR course_name LIKE :searchText) ORDER BY course_code LIMIT 6');

$final_search_term = "%".$search_term."%";
$records->bindParam(':searchText', $final_search_term);

Here is my attempt at adding REPLACE to ignore spaces on the course_code with no success:

$records = $conn->prepare('SELECT (SELECT COUNT(*) FROM courses WHERE (REPLACE(course_code,' ','') LIKE :searchText OR course_name LIKE :searchText)) AS course_count, course_id, course_code, course_name FROM courses WHERE (REPLACE(course_code,' ','') LIKE :searchText OR course_name LIKE :searchText) ORDER BY course_code LIMIT 6');

$final_search_term = "%".$search_term."%";
$records->bindParam(':searchText', $final_search_term);

The first code works perfectly aside from not ignoring spaces.

Upvotes: 1

Views: 2043

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You are putting the replace on the wrong side. Just to be safe, let's do it for both operands:

REPLACE(course_code, ' ', '') LIKE REPLACE(:searchText, ' ', '')

Upvotes: 4

Related Questions