6 {{ upvoteCount | shortNum }}
6 {{ upvoteCount | shortNum }}

Help with simple MySQL Query

by Bruce Wedding
5 replies
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:
#mysql #query #simple
  • Profile picture of the author Dan Grossman
    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%'
    Signature
    Improvely: Built to track, test and optimize your marketing.

    {{ DiscussionBoard.errors[7751098].message }}
  • Profile picture of the author KirkMcD
    For that query, no. In fact it is wrong.
    instr returns the position of the substring in the main string.
    {{ DiscussionBoard.errors[7758103].message }}
    • Profile picture of the author Dan Grossman
      Originally Posted by KirkMcD View Post

      For that query, no. In fact it is wrong.
      instr returns the position of the substring in the main string.
      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.
      Signature
      Improvely: Built to track, test and optimize your marketing.

      {{ DiscussionBoard.errors[7759135].message }}

Trending Topics