Pfthb

I am running a simple query where comparing a TEXT column using NOT LIKE but the results are not coming correct. Tried a lot but no luck. Here is the query :

SELECT *
FROM `restaurant_session_log`
WHERE `restId` = '176'
OR branchId = '203'
OR `multi_vendorId` LIKE '%,176%' 
OR `multi_vendorId` LIKE '%,176,%' 
OR `multi_vendorId` LIKE '%176,%' 
OR `multi_vendorId` LIKE '%[176]%' 
AND (`excluded_branch_id` NOT LIKE '%,203%' OR `excluded_branch_id` NOT LIKE '%,203,%' OR `excluded_branch_id` NOT LIKE '%203,%' OR `excluded_branch_id` NOT LIKE '%[203]%' )

And here is the result:

Now the correct result would only include 2nd row, with id = 27707 because I have mentioned in the query to bring result where excluded_branch_id != %203% but I don't understand why it's giving the row with 203 in excluded_branch_id column.

Please help!

You can use REGEXP to simplify the matching. The following matches 203 if it has word boundaries on both sides of it:

(excluded_branch_id IS NULL OR excluded_branch_id NOT REGEXP '[[:<:]]203[[:>:]]')

E.g:

SELECT
 '203,111' REGEXP '[[:<:]]203[[:>:]]', -- 1
 '111,203' REGEXP '[[:<:]]203[[:>:]]', -- 1
 '1,203,1' REGEXP '[[:<:]]203[[:>:]]', -- 1
 '1120311' REGEXP '[[:<:]]203[[:>:]]' -- 0

Don't store multiple values in a single string! Don't put numeric values in a string! This is the root cause of your problems.

Sometimes, you cannot change someone's really, really, really bad data modeling decision. Your obvious problem is parentheses.

But, the logic would be simplified if you used find_in_set(). I think you intend:

WHERE (`restId` = '176' OR branchId = '203' OR
 find_in_set(176, `multi_vendorId`)
 ) AND
 (find_in_set(203, `excluded_branch_id`)

$query = "SELECT * FROM movies WHERE year_released NOT LIKE '200_';"

$result = db_query($query, array(':movies' => $value));