Freelancer.com

Go Back   Warrior Forum - The #1 Internet Marketing Forum & Marketplace > Warrior Support Forums > Programming
Register Blogs Social Groups Advertise with usHelp Desk

Reply
LinkBack Thread Tools
Unread 17th February 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,959
Thanks: 605
Thanked 994 Times in 438 Posts
Default Help with simple MySQL Query

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 February 2013, 02:16 PM   #2
White Hat Marketer
War Room Member
 
Dan Grossman's Avatar
 
Join Date: 2007
Location: Montgomeryville, PA
Posts: 1,543
Thanks: 79
Thanked 748 Times in 262 Posts
Default Re: Help with simple MySQL Query

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

[SIGPIC][/SIGPIC]
Dan Grossman is offline   Reply With Quote
Unread 17th February 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,959
Thanks: 605
Thanked 994 Times in 438 Posts
Default Re: Help with simple MySQL Query

That works, Dan. Thanks a lot.

Bruce Wedding is offline   Reply With Quote
Unread 17th February 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 260 Times in 216 Posts
Default Re: Help with simple MySQL Query

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 February 2013, 06:24 AM   #5
Senior Warrior Member
War Room Member
 
Join Date: 2006
Location: , , USA.
Posts: 2,964
Thanks: 57
Thanked 246 Times in 228 Posts
Default Re: Help with simple MySQL Query

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 February 2013, 11:03 AM   #6
White Hat Marketer
War Room Member
 
Dan Grossman's Avatar
 
Join Date: 2007
Location: Montgomeryville, PA
Posts: 1,543
Thanks: 79
Thanked 748 Times in 262 Posts
Default Re: Help with simple MySQL Query

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

[SIGPIC][/SIGPIC]
Dan Grossman is offline   Reply With Quote
Reply

  Warrior Forum - The #1 Internet Marketing Forum & Marketplace > Warrior Support Forums > Programming

Bookmarks

Tags
mysql, query, simple

Thread Tools

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 02:54 PM.