How do I combine two columns and then do a quasi fulltext search?

Question

MySQL

Table: cities

+----+----------+
| id |   city   |
+----+----------+
| 1  | New York |
+----+----------+

Table: stations

+----+--------------+---------+
| id |     name     | city_id |
+----+--------------+---------+
| 1  |   Woodlawn   |    1    |
+----+--------------+---------+
| 2  | Mosholu Pkwy |    1    |
+----+--------------+---------+

PHP

$string = mysql_real_escape_string('woodlawn new york');

$a = mysql_query("

    SELECT s.id FROM cities AS c, stations AS s 

    WHERE CONCAT_WS(' ', s.name, c.city) LIKE '%$string%' AND c.id = s.city_id

");

while($b = mysql_fetch_assoc($a))
{
   echo $b['id'];
}

Problem

The example above works fine, however, when I try to search for:

 $string = mysql_real_escape_string('mosholu new york');

I get 0 results, unless I explicitly search for mosholu pkwy new york.

Possible Options?

Obviously I cannot use FULLTEXT search in combination with CONCAT_WS. And I would also really like to keep the structure of the tables as they are, having one table for the cities and one for the stations.

So, is the only option to add a fourth column to the stations table that includes the station name and city name and then do a FULLTEXT search on that?

But it would just be a pain in the butt, if for example I had to update the city name and then change it for all the rows in the stations table as well.

Answer 1

Just replace whitespace in $string with % wildcards—you can do this in either PHP or MySQL as you prefer; for example, in MySQL:

SELECT s.id
FROM   cities AS c JOIN stations AS s ON c.id = s.city_id
WHERE  CONCAT_WS(' ', s.name, c.city) LIKE REPLACE('%$string%', ' ', '%')