How do you ignore dashes and commas in a php search bar

Question

Currently, in my search bar, I'm able to find the name of a city if I type it in exactly the way it has been put into the database. For example: Nuits-Saint-Georges or Durham, NC.

I would like for my users to be able to type in Nuits Saint Georges or Durham NC.

The Cities are located in "location".

function Wo_GetAllJobs($filter_data = array()) {
global $wo, $sqlConnect;
$data = array();
$query_one = " SELECT * FROM " . T_JOB . " WHERE status = '1'";
if (!empty($filter_data['c_id'])) {
$category = $filter_data['c_id'];
$query_one .= " AND `category` = '{$category}'";
}
if (!empty($filter_data['after_id'])) {
if (is_numeric($filter_data['after_id'])) {
$after_id = Wo_Secure($filter_data['after_id']);
$query_one .= " AND `id` < '{$after_id}' AND `id` <> $after_id";
}
}
if (!empty($filter_data['keyword'])) {
$keyword = Wo_Secure($filter_data['keyword']);
$query_one .= " AND (`title` LIKE '%{$keyword}%' OR `location` LIKE 
'%{$keyword}%') ";
}
if (!empty($filter_data['user_id'])) {
$user_id = Wo_Secure($filter_data['user_id']);
$query_one .= " AND `user_id` = '{$user_id}'";
}
if (!empty($filter_data['type'])) {
$type = Wo_Secure($filter_data['type']);
$query_one .= " AND `job_type` = '{$type}'";
}

if (!empty($filter_data['length'])) {
$user_lat = $wo['user']['lat'];
$user_lng = $wo['user']['lng'];
$unit = 6371;
$query_one = " AND status = '1'";
$distance = Wo_Secure($filter_data['length']);
if (!empty($filter_data['c_id'])) {
$category = $filter_data['c_id'];
$query_one .= " AND `category` = '{$category}'";
}
if (!empty($filter_data['after_id'])) {
if (is_numeric($filter_data['after_id'])) {
$after_id = Wo_Secure($filter_data['after_id']);
$query_one .= " AND `id` < '{$after_id}' AND `id` <> $after_id";
}
}
if (!empty($filter_data['keyword'])) {
$keyword = Wo_Secure($filter_data['keyword']);
$query_one .= " AND (`title` LIKE '%{$keyword}%' OR `location` LIKE 
'%{$keyword}%') ";
}
if (!empty($filter_data['user_id'])) {
$user_id = Wo_Secure($filter_data['user_id']);
$query_one .= " AND `user_id` = '{$user_id}'";
}

if (!empty($filter_data['type'])) {
$type = Wo_Secure($filter_data['type']);
$query_one .= " AND `job_type` = '{$type}'";
}

I don't think your entire function is listed here. – dazed-and-confused Sep 12 '19 at 02:10 — dazed-and-confused, Sep 12 '19 at 02:10
Tag your question with your database , is it MySQL ? – Accountant م Sep 12 '19 at 02:28 — Accountant م, Sep 12 '19 at 02:28

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Split your keyword by spaces then implode them by %

//TODO query_one
//$query_one .= " AND (`title` LIKE ? OR `location` LIKE ?) ";
$stmt = $mysqli->prepare($query_one);
$keyWord = "%" . implode("%", explode(" ", $keyword)) . "%";
$stmt->bind_param("s", $keyWord));
$stmt->bind_param("s", $keyWord));
$stmt->execute();

Remember that prepared statements is a MUST and easy to switch to

note:

using % at the beginning of the keyword will make the optimizer not be able to use the index on that column, if any exists, so if your data size is big and you started to encounter performance issues, you can go to Full Text Search (FTS) check this

Don't worry about case sensitivity comparisons on MySQL

And Regarding the case of the comparison that MySQL will perform, I will quote from the manual "Case Sensitivity in String Searches"

For nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands. For binary strings (BINARY, VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons will be case-sensitive.

..

The default character set and collation are utf8mb4 and utf8mb4_0900_ai_ci, so nonbinary string comparisons are case insensitive by default.

score 0 · Accepted Answer · answered Sep 19 '19 at 16:28

This is how I ended up solving this issue.

if (!empty($filter_data['keyword'])) {
    $keyword = $filter_data['keyword'];
    $keywordSearch = Wo_Secure(str_replace(',','',str_replace('-','',str_replace('.','',str_replace(' ', "", $keyword)))));
    $keyword = Wo_Secure($keyword);
    $query_one .= " AND (`title` LIKE '%{$keyword}%' OR REPLACE(REPLACE(REPLACE(REPLACE(`location`,' ',''),'-',''),'.',''),',','')LIKE '%{$keywordSearch}%') ";
}

How do you ignore dashes and commas in a php search bar

2 Answers2

note:

Don't worry about case sensitivity comparisons on MySQL