Making queries without knowing what the user input is (SQL, PHP)

Question

I need some help in a project I'm working on.

Here is what I'm trying to do:

I'm working with MySQL and PHP on a webpage. There is an input field where the user will input a location (Country, city, state), date, race length or race name. The thing is that they can input any combination of these items (just like you can in google).

After, I've to query the database to show the results. This is the query that I'm using right now:

LIKE '$str%' 
OR city LIKE '$str%' 
OR state LIKE '$str%' 
OR country LIKE '$str%' 
OR race_distancia LIKE '$str%'

$str is a php variable holding the input text.

I'm using '$str%' since it will use the index I've created, though this is not working correctly since the regular expression is added at the end. So if I type for example: 'Miami 5k' it won't work. Since no race is called miami 5k, nor a location... But there are 5ks in Miami hehe.

I was thinking about exploding the inputted text on the spaces and the running a query for each exploded item, and then delete the repeated results. This will work but I'm trying to find the most efficient solution and I'm not exactly sure how to avoid repeated items.

Thanks in advanced!

First of all use something like PDO (PHP Database Object) to prepare queries to prevent SQL Injections. — Julius F, Mar 29 '12 at 20:22
these seem rather defined search terms, i would have a box for country, then a box for city .... may be possible to have a list for some of them and free text for others — , Mar 29 '12 at 20:22
Exploding on spaces won't work if there's a two word city. Also, if someone searches a city that is also a state name, IE "New York, New York" your query won't work as intended. — Norse, Mar 29 '12 at 20:40

score 0 · Answer 1 · answered Mar 29 '12 at 20:23

0

Full Text Search may help you

answered Mar 29 '12 at 20:23

Tufan Barış Yıldırım

3,952
26
36

score 0 · Answer 2 · edited May 23 '17 at 12:20

You could break down the input to words using:

$searchWords = explode(" ", $input);

If someone types in Los Angeles, it will search for 'Los' and 'Angeles', but i think it will work good enough. You could make a more advanced search with more input fields, if there are to many results.

Edit: i saw you managed to get this far. To filter double words check out: PHP : Find repeated words with and without space in text

Edit2: As for the Los Angeles issue: maybe using "Los Angeles" as searchword is acceptable. To get then in an array see this very good post about that: PHP explode the string, but treat words in quotes as a single word

Making queries without knowing what the user input is (SQL, PHP)

2 Answers2