Search words by similarity in MySQL query

Question

So I have this MySQL query:

$search = $_POST['search'];
$SQL = "SELECT * FROM `data` WHERE name='$search';
$rs = mysqli_query($dbconfig, $SQL);

I use this code to search data into the database, with the word that has been submitted by the form. The problem is, the query only shows up results if the search word matches the data in the database.

For example, if I have a file named advertisement.png and I search for this file, it will only show up if I search for the exact same word.

If I search for advertisement.png it will show advertisement.png, but if I search for advertisement it will not show up. What I want, is to search data that are similar to the key word provided in the form.

So if I search"advert" it shows up results that are similar to this word.

Thanks in advance.

If the answers below don't solve your need, you might want to look into full text search. — Tim Biegeleisen, Oct 23 '16 at 08:18
And don't forget, ALWAYS use `mysqli_real_escape_string` with any input in SQL. No answer mention that... — Brain Foo Long, Oct 23 '16 at 08:21

score 1 · Accepted Answer · answered Oct 23 '16 at 08:16

1

Use LIKE operator with % for both end as follows

$SQL = "SELECT * FROM `data` WHERE name LIKE '%$search%';

answered Oct 23 '16 at 08:16

Chandana Kumara

2,485
1
22
25

score 0 · Answer 2 · answered Oct 23 '16 at 08:13

0

Use LIKE operator with % at end

$search = $_POST['search'];
$SQL = "SELECT * FROM `data` WHERE name LIKE '$search%';
$rs = mysqli_query($dbconfig, $SQL);

answered Oct 23 '16 at 08:13

Rajesh Patel

1,946
16
20

score 0 · Answer 3 · answered Oct 23 '16 at 08:16

0

You should use Like Operator. The Code will be

$search = $_POST['search'];
$SQL = "SELECT * FROM `data` WHERE name LIKE '%$search%';
$rs = mysqli_query($dbconfig, $SQL);

answered Oct 23 '16 at 08:16

Jahid Mahmud

1,136
1
12
32

score 0 · Answer 4 · edited Oct 23 '16 at 08:20

0

You have use % on start and end of your secrch value.

SELECT * FROM Customers WHERE Country LIKE '%land%';

edited Oct 23 '16 at 08:20

Dave

3,073
7
20
33

answered Oct 23 '16 at 08:18

Igor Duda

1
2

score 0 · Answer 5 · answered Oct 23 '16 at 08:22

0

Ther is also some SQL functions for comparing string you could use like STRCMP(str1, str2), returns 0 if both strings are equal

answered Oct 23 '16 at 08:22

lno

1
1

score 0 · Answer 6 · answered Oct 23 '16 at 08:22

0

$sql = "select * from table_name name like '%search_key%' ";

Here % means all keyword you type from left and right. Any keyword matches it will give you the result

answered Oct 23 '16 at 08:22

Shaon

2,496
26
27

Search words by similarity in MySQL query

6 Answers6