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:
#programming #mysql #query #simple
Avatar of Unregistered
  • 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 }}
Avatar of Unregistered

Trending Topics

  • 7 {{ upvoteCount | shortNum }}
  • 5 {{ upvoteCount | shortNum }}

    What's the best digital marketing certificate out there?

    I want to be an internet marketing specialist. You know like for social media and SEO I am not talking about those who you have to complete a whole program ... [read more]

  • 9 {{ upvoteCount | shortNum }}

    Im newbie.. i want a good strategy about getting money from net .. thx all

    Im newbie.. i want a good strategy about getting money from net . so if u know something about that just message me and i'll be so thankful .. sry ... [read more]

  • 32 {{ upvoteCount | shortNum }}

    How I get more click or traffic on particular keyword?

    Nikhil Bansal in SEO

    I have some keyword that has a great ranking on Google. but they don't have click and traffic on that keyword so I want to know how I get more ... [read more]

  • 4 {{ upvoteCount | shortNum }}

    Best strategy for the registration of a PBN's domains (to avoid being penalized)

    Herb Nazhe in SEO

    Hi I am about to create a Private Blog Network, and I need some advice about the best strategy to register its domain names. In particular, I need to know: ... [read more]