Searching database by string exploded into array

Question

To be clear, I need to search the table in database, specifically field called tags which is defined as text. Into the database, I am entering those 'tags' as a string and then I am exploding / dividing this string of 'tags' by explode() method into array in php. But for search of multiple 'tags' in form I need to enter multiple tags divided by SPACE and then by #.

Problem is that I do not know how to tell database to ignore order of entered tags and display all pictures which contains entered tags in search form ignoring the order of entered tags. Until now I was able to search only pictures where tags were in order.

I have tried to do it this way but it did not work

$search_string = $_POST['search'];
$exploded_search_string = explode(" ", $search_string);    
$sql_search = "SELECT * FROM pictures 
               WHERE tags LIKE '%".$exploded_search_string."%' 
               ORDER BY ID_picture DESC";

The way you are constructing your SQL query is open to SQL injection, please use Prepared statements, check this [answer](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496) — Spoody, Mar 03 '18 at 19:07
It sound like you want to change your query into something like 'WHERE tags LIKE '%$tag1%' AND tags LIKE '%tag2%'' etc. this still seems like a flawed approach. If say one tag name was contained in another. — Progrock, Mar 03 '18 at 19:32

score 0 · Answer 1 · answered Mar 03 '18 at 19:08

0

You don't say what database you're using. Assuming you're using MySQL you could take a look at full-text searching.

Also, you should consider using PDO so that you can use prepared statements to help prevent SQL injection attacks.

answered Mar 03 '18 at 19:08

Phil

274
2
5

score 0 · Accepted Answer · answered Mar 03 '18 at 19:32

I think you could do something like this

WHERE tags LIKE ’$exploded_search_string[0]%' OR tags LIKE '%$exploded_search_string[1]%'

OR

WHERE tags REGEXP '$exploded_search_string[0]| $exploded_search_string[1]'

You can loop the $exploded_search_string array to create the SQL statement like above and your select query should find all the tags found in DB.

Once you be able to get the results using the above statement, you could check FULL TEXT search for an optimized solution.

score 0 · Answer 3 · answered Mar 03 '18 at 20:25

0

if your tags are identical to the ones in the db, use IN instead of LIKE

  $sql_search = "SELECT * FROM pictures 
           WHERE tags IN ($exploded_search_string) 
           ORDER BY ID_picture DESC";

answered Mar 03 '18 at 20:25

nitrex

522
6
16

Searching database by string exploded into array

3 Answers3