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.

mysql 用like模糊查询 怎么查包含了%号的字符串

lnkToKing

3个回复

mysql 用like模糊查询怎么查包含了%号的字符串