Member Avatar for bob on whidbey

The MYSQL db has a Location field with entries like:
CAN
CAN ON
CAN ON, CAN BC
CAN AB, CAN BC
NY
OH CANFIELD, OH TOLEDO
WA, OR, CA

Thus the location field can have multiple entries, all separated by a comma, but still held in the single Location field.

If the user enters CAN in the lookup field, I want the first 4 records in my example DB returned
If the user enters CAN ON in the lookup field, I want 2 records returned

The relevant part of the SELECT statement, that's built in PHP, reads:

if ($Location<>'')  { $SQL = $SQL." and Locations REGEXP '^".$Location."$'";}

With CAN entered, only 1 record is returned...the 1st one. Using "LIKE" can solve this problem, but would incorrectgly return a record with location=CANFIELD

How can I fix this?

  • 2 Contributors
  • 2 Replies
  • 127 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by bob on whidbey
Member Avatar for debasisdas

Replace the comma with space in the string and then use LIKE search.

Member Avatar for bob on whidbey

Thank you debasisdas. This code seems to be correct.

if ($Location<>'')  { $SQL = $SQL.' and Locations like "%'.$Location.' %"  or Locations like "%'.$Location.'," or Locations like "%'.$Location.'"';}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.