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:
- Performing a search on first and last name columns with a single search string that has more than 2 words
- Searching full name or first or last name in MySQL database with first and last name in separate columns
- Search with full name instead of just first name or last name
- Live search on first, middle and last name
- Search Database by First and Last Name
- MySQL Search For Names - Separated by Space
~~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
