Matching X with field in DB Table that has mult. entries (||)

[austingecko]

I'm using mysql and if I have:
$location = "New York-New York";
$sql = "SELECT * from ads WHERE (ad_location = '$location') AND (status = 'active')";

I know a record with an ad_location of "New York-New York" will display here (the location I want to match is "New York-New York"). But, if I have a record in DB with "New York-New York||Texas-Dallas" as an ad_location, the record will not display (I guess unless I'm searching for New York-New York||Texas-Dallas, but that's not going to happen). And I want all records in the ad_location field containing $location to display.

Do I need to read the entire database and then expand all the records (with '||') to find matches to "New York-New York" with multiples? I hope not.

Any suggestions would be really helpful. Thanks.

I guess I could not have multiple entries in ad_location. Instead, I could insert a new row which is a duplicate row, except for data in the ad_location field. This doesn't sound efficient though.

[juschillinnow]

use one of the followin:

if(strcmp("New York-New York", $row['ad_location'])!=0)
{
echo "yes";
}

or

if(preg_match("/New York-New York/", $row['ad_location']))
{
echo "yes";
}

[austingecko]

Hmm... I do that after I selected all the rows from the table? Maybe I'm wrong?

Hoping I could just select matching rows, I tried:
WHERE (ad_location LIKE '%$location%')
but fields including things like "New York-New York||Texas-Dallas" still do not match. That doesn't make since... because "New York-New York" is still apart of the name in that record. 😕

So, I tried:
WHERE ad_location IN ('$location') AND (status = 'active')
But, that doesn't work either (the record with only "New York-New York" continues to show up...).

[austingecko]

ok, here is the solution:

$sql = "SELECT * from ads WHERE (ad_location LIKE '%%$location%%') AND (status = 'active')";

I went back to LIKE and remembered '||' is, well, two characters and, instead, used %% -- it works!

Record A: New York-New York
Record B: New York-New York||Texas-Dallas

When selecting "New York-New York" records A & B match. When selecting "Texas-Dallas" record B matches. 🙂