mysql select query problem

Question

i have a form that has a multiple select drop down. a user can select more than one options in the select. the name of the select is array[]; using php I call implode(",",$array) in mysql db, it stores the field as a text in this format "places"= "new york, toronto, london" when i want to display these fields i explode the commas.

I am trying to run a report to display the places. here is my select:

"select * from mytable where db.places .. userSelectedPlaces"

how can i check toronto in lists of "places" that user selected? note "places" in the db might be either just "toronto" or it might be comma separated lists of places like "ny, toronto, london, paris, etc".

Answer 1

If I understand you correctly, your database design is just wrong. Try reading about it more. Generally, in good design you should not have lists of values as one field in database and you should introduce new table for it.

But if you want to do it this way, you can use strcmp function.

Answer 2

If it is possible, you would be much better off using another table to hold the places that the user has selected. Call it SelectedPlaces with columns:

• mytable_id - To join back to the table in your query
• place - EG: "Toronto"

Then you can run a simple query to figure out if Toronto has been selected:

SELECT * 
  FROM mytable m
 INNER JOIN SelectedPlaces sp ON sp.mytable_id = m.id
 WHERE sp.place = 'Toronto'

Answer 3

If i understood correctly, this should work:

WHERE DB.PLACES LIKE '%TORONTO%' 

but as other users said, its not a nice thing to have denormalized tables.

Answer 4

To directly answer your question, your query needs to look something like this

SELECT *
  FROM mytable
 WHERE places LIKE( '%toronto%' )

But, be aware, that LIKE() is slow.

To indirectly answer your question, your database schema is all wrong. That is not the right way to do a M:N (many-to-many) relationship.

Imagine instead you had this

mytable         place                    mytable_place            
+------------+  +----------+----------+  +------------+----------+ 
| mytable_id |  | place_id | name     |  | mytable_id | place_id | 
+------------+  +----------+----------+  +------------+----------+ 
| 1          |  | 1        | new york |  | 1          | 1        | 
| 2          |  | 2        | toronto  |  | 1          | 2        | 
| 3          |  | 3        | london   |  | 1          | 3        | 
+------------+  +----------+----------+  | 2          | 2        | 
                                         | 3          | 1        | 
                                         | 3          | 3        | 
                                         +------------+----------+ 

The table mytable_places is what's called a lookup table (or, xref/cross-reference table, or correlation table). Its only job is to keep track of which mytable records have which place records, and vice versa.

From this example we can see that The 1st mytable record has all 3 places, the 2nd has only toronto, and the 3rd has new york and london.

This opens you up too all sorts of queries that would be difficult, expensive, or impossible with your current design.

Want to know how many mytable records have toronto? No problem

SELECT COUNT(*)
  FROM mytable_place x
  LEFT JOIN place p
    ON p.place_id = x.place_id
 WHERE p.name = 'toronto';

How about the number of mytable records per place, sorted?

SELECT p.name
     , COUNT(*) as `count`
  FROM mytable_place x
  LEFT JOIN place p
    ON p.place_id = x.place_id
 GROUP BY p.place_id
 ORDER BY `count` DESC, p.name ASC

And these are going to be much faster than any query using LIKE since they can use indexes on columns such as place.name.