Reply
LinkBack Thread Tools Search this Thread
Unread 17th Feb 2013, 01:17 PM   #1
The Reality Check
War Room Member
 
Bruce Wedding's Avatar
 
Join Date: 2006
Location: Cancun, Quintana Roo, MX
Posts: 3,934
Thanks: 615
Thanked 999 Times in 441 Posts
Default
Help with simple MySQL Query
Share on: 
fb share twitter share gplus share more share

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:

Bruce Wedding is offline   Reply With Quote
Unread 17th Feb 2013, 02:16 PM   #2
White Hat Marketer
War Room Member
 
Dan Grossman's Avatar
 
Join Date: 2007
Location: Montgomeryville, PA
Posts: 1,548
Thanks: 79
Thanked 775 Times in 267 Posts
Default
Re: Help with simple MySQL Query
Share on: 
fb share twitter share gplus share more share

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%'

Improvely: Conversion tracking & click fraud detection. Try the Live Demo

Dan Grossman is offline   Reply With Quote
Unread 17th Feb 2013, 04:01 PM   #3
The Reality Check
War Room Member
 
Bruce Wedding's Avatar
 
Join Date: 2006
Location: Cancun, Quintana Roo, MX
Posts: 3,934
Thanks: 615
Thanked 999 Times in 441 Posts
Default
Re: Help with simple MySQL Query
Share on: 
fb share twitter share gplus share more share

That works, Dan. Thanks a lot.

Bruce Wedding is offline   Reply With Quote
Unread 17th Feb 2013, 07:11 PM   #4
Social Apps Made Easy
War Room Member
 
FirstSocialApps's Avatar
 
Join Date: 2012
Location: Mooresville, NC
Posts: 914
Thanks: 234
Thanked 263 Times in 217 Posts
Default
Re: Help with simple MySQL Query
Share on: 
fb share twitter share gplus share more share

Quote:
Originally Posted by Dan Grossman View Post
I've never seen someone use INSTR though. Most people would write [code]WHERE uid = 124 AND title LIKE '%21.01%'
Wildcards are the way I have always done a search like this as well. Is there some advantage to using INSTR?

FirstSocialApps is offline   Reply With Quote
Unread 19th Feb 2013, 06:24 AM   #5
Senior Warrior Member
War Room Member
 
Join Date: 2006
Location: , , USA.
Posts: 3,074
Thanks: 63
Thanked 285 Times in 259 Posts
Default
Re: Help with simple MySQL Query
Share on: 
fb share twitter share gplus share more share

For that query, no. In fact it is wrong.
instr returns the position of the substring in the main string.
KirkMcD is offline   Reply With Quote
Unread 19th Feb 2013, 11:03 AM   #6
White Hat Marketer
War Room Member
 
Dan Grossman's Avatar
 
Join Date: 2007
Location: Montgomeryville, PA
Posts: 1,548
Thanks: 79
Thanked 775 Times in 267 Posts
Default
Re: Help with simple MySQL Query
Share on: 
fb share twitter share gplus share more share

Quote:
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.

Improvely: Conversion tracking & click fraud detection. Try the Live Demo

Dan Grossman is offline   Reply With Quote
Reply

  Warrior Forum - The #1 Internet Marketing Forum & Marketplace Programming

Bookmarks

Tags
mysql, query, simple

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off




All times are GMT -6. The time now is 09:21 PM.