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?

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

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.