[SOLVED] Strpos and a MySQL Query

[suttercain]

Hi guys,

 

Right now I am collecting data. The end user can enter up to 5 cites. I then take these cities and import them into MySQL as such:

 

Los Angeles, Corona, Pasadena, Rosemead, Temple City

 

Now let's say there are 100 rows in the column, each with varied cities. Now within 5 of thoses rows Pasadena is listed as a city. How would I query to find all listing that have Pasadena listed as a city?

 

I am cuurently using the get method. So If I was on page 1 and click the link Pasadena, I would be taken to page 2 and a list of all Users who have Pasadena as one of their cities would be echoed.

 

if (isset($_GET['city'])) {
    $city = mysql_real_escape_string($_GET['city']);
    $sql = mysql_query("SELECT * FROM states WHERE city = '" .$city. "' ORDER BY city");
  }
  

while ($row = mysql_fetch_array($sql)) {
echo results
}

 

Currently I don't get any results because pasadena is within a string in the database as Los Angeles, Corona, Pasadena, Rosemead, Temple City. How do I break it down so it only finds Pasadena and not Los Angeles, Corona, Pasadena, Rosemead, Temple City?

 

strpos, explode?

 

Thanks guys!

 

SC

[Psycho]

Use LIKE:

 

$sql = mysql_query("SELECT * FROM states WHERE city LIKE '%" .$city. "%' ORDER BY city");

 

[kiss-o-matic]

What does your DB look like?  Something like:

User,City1,City2,City3,City4,City5

 

Or is city one column?  If it's the former, you have to check each one.  For the latter, a REGEXP.

 

"SELECT * FROM states WHERE city REGEXP '.*Pasadena.*' ";

[suttercain]

awesome, works so far. Thanks!