Help with simple MySQL Query

by 5 replies
6
I have very little SQL experience and have a problem with a query that seems relatively easy. It's returning a null set and SQL Explain says it's an impossible where.

Here's the query:
SELECT 'password' FROM `album` WHERE `uid`=124 AND instr('title', '21.01')

Here's the DB structure:


Here's the actual record with the matching uid and title:
#programming #mysql #query #simple
  • INSTR('title', '21.01') looks for the string '21.01' in the string 'title'. That substring does not appear, so the query can never match any rows so none are even checked.

    What you meant to write was INSTR(title, '21.01') which looks for the string '21.01' in the column named title.

    I've never seen someone use INSTR though. Most people would write
    Code:
    WHERE uid = 124 AND title LIKE '%21.01%'
    • [ 1 ] Thanks
    • [2] replies
    • That works, Dan. Thanks a lot.
    • Wildcards are the way I have always done a search like this as well. Is there some advantage to using INSTR?
  • For that query, no. In fact it is wrong.
    instr returns the position of the substring in the main string.
    • [1] reply
    • It's not in fact wrong at all. MySQL needs a boolean to evaluate the WHERE clause of the query, and coercing the integer return value of INSTR will have the desired result. INSTR returns 0 if the substring doesn't appear, which is coerced to boolean false. If the substring does appear, its index would be a positive integer, and any positive integer is coerced to boolean true.
      • [ 1 ] Thanks
Join the discussion

Next Topics on Trending Feed