0

MySQL

+----+-------------------+----------+--------------+
| id | address           | city     | state        |      
+----+-------------------+----------+--------------+
| 1  | 13000 highway 244 | keystone | south dakota |
+----+-------------------+----------+--------------+

(btw, I left out some columns to make it easier)

PHP

$string = mysql_real_escape_string('13000 highway 244 south dakota');

$a = mysql_query("

    SELECT * FROM `table` WHERE

    CONCAT_WS(' ', `address`, `city`, `state`) LIKE '%$string%'

");

The above search query doesn't return any results, only the following values would work:

  • 13000 highway 244 keystone south dakota
  • 13000 highway 244 keystone
  • keystone south dakota

However, I also want to get the following values to work:

  • 13000 highway 244 south dakota
  • 13000 highway keystone
  • south dakota highway keystone
  • etc.

Is that even possible without relying on full-text search?

  • The solution to this would be a rather intensive keyword search that doesn't rely on finding the existence of a string within another string. Your best two solutions are to either use more than one input to search on a per-field basis, or to use a textarea and use newlines (`\n`) as a separator for each chunk of info, eg. `$string = explode(PHP_EOL, $textarea)`; Why? because `CONCAT_WS` isn't appropriate for what should be a simple query. – leemeichin Feb 18 '12 at 00:24

2 Answers2

1

Why not do it code more explicitly like this?

$address = mysql_real_escape_string('13000 highway 244');
$city = mysql_real_escape_string('keystone');
$state = mysql_real_escape_string('south dakota');

$a = mysql_query( '

    SELECT * 
      FROM `table` 
     WHERE (   ( address = ' . $address . ' )
           AND ( city    = ' . $city    . ' )
           AND ( state   = ' . $state   . ' )
           )
        OR ( state   = ' . $state   . ' )
             .. probably more alternatives ...
');
SteAp
  • 11,853
  • 10
  • 53
  • 88
  • Well, I have just one input field, and I wouldn't be able to tell whether the user typed in the address, city, or state. And I also don't want to save them all in just one field in the db. But thanks anyways! –  Feb 18 '12 at 00:02
  • Yes, I'd rather just see one input field than three or more. Same goes for Google Maps, for example. I can't imagine looking for a place on Google Maps, and having to type in the address, city, state in separate input fields. But thanks for your advice :) I guess I'll just have to use full-text search then. –  Feb 18 '12 at 00:21
0

before passing the string to query replace the spaces with wildcard so even "130 dakoda" would work

$string = str_replace( array('*', '?', ' '), array('%', '_', '%'), $string); //Change norm wildcards to mysql format and replace spaces with wildcards

vieras
  • 1