Trouble putting together mysql query

I'm using the following mysql query to return some data from my database. Currently only the cities table has anything in it, and I have noticed that this query will only return the first matching entry it finds, not all matching entries it finds. Is there a way I can modify this query so that it will return all matching results from all tables?

The idea is that if I went $query = 'h' then the query should return all events, venues and cities that start with h.

SELECT name, 'Events' as source 
FROM events 
WHERE name LIKE '".$query."%' 
UNION ALL    
SELECT name, 'Venues' as source 
FROM venues 
WHERE name LIKE '".$query."%' 
UNION ALL
SELECT name, 'Cities' as source 
FROM cities 
WHERE name LIKE '".$query."%' 
OR  FIND_IN_SET('".$query."%', Alternate_name)

Please explain how I can fix it, rather than just posting a solution. I'm still learning and want to understand.

Edit:

The query after the substitutio开发者_如何学运维n (with the $query variable containing the letter h):

SELECT name, id, 'Events' as source FROM events_basic WHERE name LIKE 'h%' UNION ALL
SELECT name, fsq_id, 'Venues' as source FROM venues_cache WHERE name LIKE 'h%' UNION ALL 
SELECT name, geo_id, 'Cities' as source FROM static_cities WHERE name LIKE 'h%' OR FIND_IN_SET('h%', alternate_names)

The php code I used is as follows: `

$query = 'h';

$query = $this->db->query("SELECT name, 'Events' as source FROM events WHERE name LIKE ".$query."%' UNION ALL 
SELECT name, 'Venues' as source FROM venues WHERE name LIKE '".$query."%' UNION ALL
SELECT name, 'Cities' as source FROM cities WHERE name LIKE '".$query."%' OR  IND_IN_SET('".$query."%', Alternate_name)");
    if($query->num_rows > 0)
    {
        $results = $query->row_array();
        print_r($results);
    }`

The idea is that the variable $query holds the name, or partial name of an event, city or venue. The query should then return the names of the events, venues and cities that match the given name. I hope ive clarified enough, if not, I'll try to clarify more.

---

Could you please post the SQL query after the substitution is made? It looks to me like you're placing the second single-quote-character in the wrong place in the concatenation, producing the phrase (for instance) WHERE name LIKE 'h'% rather than WHERE name LIKE 'h%' (note the difference of position of the second quote in the second version).

This would cause the situation you're seeing if you have a city with a name of H (just H) or an alternate in the list of names that is just "H". Is that true?

Also, since you asked for explanation:

• Your technique of placing the alternate city names in a single comma-separate field violates the principals of database normalization. The "proper" structure is to create a new table with two columns "name" and "alternate". A city with alternate names gets one entry in that alternate table for each alternate name. Among other things, this will allow your search against the alternate names to use an index which is not possible with the city search in your current version.

• You must make very, very certain that you've "sanitized" the contents of the variable $query to ensure that someone is not launching an SQL injection attack on your site (in which they write actual SQL commands into the query field). Better yet, look and and use parameterized queries which have the server perform this substitution for you.

---

What is the expected result of FIND_IN_SET in your WHERE clause? From the information you've provided, you should be able to omit this.

If you're looking to match alternate city names from the table, consider this:

WHERE name LIKE '".$query."%' OR alternate_name LIKE '".$query."%';

Please ensure you're sanitizing your input. Someone might make $query = '1;DROP TABLE venues;'