found some other posts, but none of them really helped me (maybe I am too stupid for searching). Even the Similar Questions block while typing this did not help.
Here is my question: I got a mysql database with 4 columns: id, first-name, last-name, city
On a php application I got an input field that queries via AJAX the php script to search the database. This field works if only 1 search term is given, like first-name. As soon I try 2 or 3 search terms I have no idea how to query the db.
Please help me how to query my database (got like 20,000 rows). Some search examples: Thomas Boston Michael Smith New York Doe, Jane Orlando, Michael Expected result are all rows that contain any of those search terms. Best would be to have it ordered by similarity (most terms found first). Using DIFFERENCE() would be nice, too, but my script is in German....
I have read stuff about full text search, but have no idea how to do this.
Here is my current query code (for 1 search term):
$key = $_GET['key'];
//Here will be something to filter all non numbers/letters and change them to a space for $key
$keys = explode(" ",$key); //$keys is not in use yet
$prep_stmt = "
SELECT
id, last-name, first-name, city
FROM
customers
WHERE
last-name LIKE concat('%', ? ,'%') OR
first-name LIKE concat('%', ? ,'%') OR
city LIKE concat('%', ? ,'%')
ORDER BY
last-name ASC";
$stmt = $mysqli->prepare($prep_stmt);
$stmt->bind_param('sss', $key, $key, $key);
About security: This code runs in an internal environment. So security is not the biggest priority.
PS: I am not native English, but I tried my best.
Stuff I found, but didn't help:
~~EDIT~~
Stuff I came up with, but didn't work:
$key = trim(preg_replace( '/\s+/', ' ', preg_replace("/[^[:alnum:][:space:]]/u", ' ', $key)));
SELECT
id, last-name, first-name, city
FROM
customers
WHERE
last-name LIKE concat('%', REPLACE( ? ,' ','%') ,'%') OR
first-name LIKE concat('%', REPLACE( ? ,' ','%') ,'%') OR
city LIKE concat('%', REPLACE( ? ,' ','%') ,'%')
ORDER BY
name ASC
Thanks to Rogier Bruggeman I got an idea how to solve my problem. This is how I solved it:
$key = $_GET['key'];
$key = trim(preg_replace( '/\s+/', ' ', preg_replace("/[^[:alnum:][:space:]]/u", ' ', $key)));
$keys = explode(" ",$key);
$key_num = count($keys);
if(NOT RELEVANT == RELEVANT){
NOT RELEVANT
} else {
//prepare statement
$prep_stmt = "SELECT id FROM customers WHERE ";
if($key_num == 1){
$prep_stmt .= "
lastname LIKE ? OR
firstame LIKE ? OR
zip LIKE ? OR
city LIKE ?
ORDER BY
lastname ASC";
$stmt = $mysqli->prepare($prep_stmt);
$key = '%' . $key . '%';
$stmt->bind_param('ssss', $key, $key, $key, $key);
} else {
$p = 0;
foreach($keys as $v){
if($p <> 0){
$prep_stmt .= " AND ";
}
$prep_stmt .= "(lastname LIKE ? OR firstname LIKE ? OR zip LIKE ? OR city LIKE ? )";
$p++;
}
$prep_stmt .= " ORDER BY name ASC";
$stmt = $mysqli->prepare($prep_stmt);
switch($key_num){
case 2:
$keys[0] = '%' . $keys[0] . '%';
$keys[1] = '%' . $keys[1] . '%';
$stmt->bind_param('ssssssss', $keys[0], $keys[0], $keys[0], $keys[0], $keys[1], $keys[1], $keys[1], $keys[1]);
break;
case 3:
$keys[0] = '%' . $keys[0] . '%';
$keys[1] = '%' . $keys[1] . '%';
$keys[2] = '%' . $keys[2] . '%';
$stmt->bind_param('ssssssssssss', $keys[0], $keys[0], $keys[0], $keys[0], $keys[1], $keys[1], $keys[1], $keys[1], $keys[2], $keys[2], $keys[2], $keys[2]);
break;
case 4:
$keys[0] = '%' . $keys[0] . '%';
$keys[1] = '%' . $keys[1] . '%';
$keys[2] = '%' . $keys[2] . '%';
$keys[3] = '%' . $keys[3] . '%';
$stmt->bind_param('ssssssssssssssss', $keys[0], $keys[0], $keys[0], $keys[0], $keys[1], $keys[1], $keys[1], $keys[1], $keys[2], $keys[2], $keys[2], $keys[2], $keys[3], $keys[3], $keys[3], $keys[3]);
break;
case 5:
$keys[0] = '%' . $keys[0] . '%';
$keys[1] = '%' . $keys[1] . '%';
$keys[2] = '%' . $keys[2] . '%';
$keys[3] = '%' . $keys[3] . '%';
$keys[4] = '%' . $keys[4] . '%';
$stmt->bind_param('ssssssssssssssssssss', $keys[0], $keys[0], $keys[0], $keys[0], $keys[1], $keys[1], $keys[1], $keys[1], $keys[2], $keys[2], $keys[2], $keys[2], $keys[3], $keys[3], $keys[3], $keys[3], $keys[4], $keys[4], $keys[4], $keys[4]);
break;
case 6:
$keys[0] = '%' . $keys[0] . '%';
$keys[1] = '%' . $keys[1] . '%';
$keys[2] = '%' . $keys[2] . '%';
$keys[3] = '%' . $keys[3] . '%';
$keys[4] = '%' . $keys[4] . '%';
$keys[5] = '%' . $keys[5] . '%';
$stmt->bind_param('ssssssssssssssssssssssss', $keys[0], $keys[0], $keys[0], $keys[0], $keys[1], $keys[1], $keys[1], $keys[1], $keys[2], $keys[2], $keys[2], $keys[2], $keys[3], $keys[3], $keys[3], $keys[3], $keys[4], $keys[4], $keys[4], $keys[4], $keys[5], $keys[5], $keys[5], $keys[5]);
break;
default:
$stmt = "SELECT id FROM customer WHERE 1 LIKE 2";
$stmt = $mysqli->prepare($prep_stmt);
}
}
It is kind of dirty, but it works. Really dirty is my default in the switch
You need to loop the $keys to extend your query
$prep_stmt = "
SELECT
id, last-name, first-name, city
FROM
customers
WHERE ";
foreach($key as $v)
{
$prep_stmt .= " OR last-name LIKE concat('%'%') OR
first-name LIKE concat('%', ? ,'%') OR
city LIKE concat('%', ? ,'%') OR " ;
}
$prep_stmt .= " 1
ORDER BY
last-name ASC
";
Then bind each param separately, once for each ?
$p = 1;
foreach($key as $v)
{
$stmt->bindParam($p++,$v);
$stmt->bindParam($p++,$v);
$stmt->bindParam($p++,$v);
}