0

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
3
Views
6 Years
Discussion Span
Last Post by bob on whidbey
0

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.'"';}
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.