Go Back   WarriorForum - Internet Marketing Forums > Warrior Support Forums > Programming Talk
Register Blogs FAQ Social Groups CalendarHelp Desk

Reply
 
Share
LinkBack Thread Tools
Old 02-17-2013, 12:17 PM   #1
The Reality Check
War Room Member
 
Bruce Wedding's Avatar
 
Join Date: Apr 2006
Location: Cancun, Quintana Roo, MX
Posts: 3,958
Thanks: 605
Thanked 982 Times in 437 Posts
Social Networking View Member's FaceBook Profile  View Member's YouTube Profile
Contact Info
Send a message via Yahoo to Bruce Wedding
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
Old 02-17-2013, 01:16 PM   #2
White Hat Marketer
War Room Member
 
Dan Grossman's Avatar
 
Join Date: Jan 2007
Location: Montgomeryville, PA
Posts: 1,466
Thanks: 78
Thanked 704 Times in 241 Posts
Social Networking View Member's Twitter Profile 
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: Campaign tracking & click fraud detection. Try the Live Demo

Dan Grossman is offline   Reply With Quote
Old 02-17-2013, 03:01 PM   #3
The Reality Check
War Room Member
 
Bruce Wedding's Avatar
 
Join Date: Apr 2006
Location: Cancun, Quintana Roo, MX
Posts: 3,958
Thanks: 605
Thanked 982 Times in 437 Posts
Social Networking View Member's FaceBook Profile  View Member's YouTube Profile
Contact Info
Send a message via Yahoo to Bruce Wedding
Default Re: Help with simple MySQL Query

That works, Dan. Thanks a lot.

Bruce Wedding is offline   Reply With Quote
Old 02-17-2013, 06:11 PM   #4
Social Apps Made Easy
War Room Member
 
FirstSocialApps's Avatar
 
Join Date: Aug 2012
Location: Mooresville, NC
Posts: 918
Thanks: 224
Thanked 253 Times in 210 Posts
Social Networking View Member's FaceBook Profile 
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 online now   Reply With Quote
Old 02-19-2013, 05:24 AM   #5
Senior Warrior Member
War Room Member
 
Join Date: Apr 2006
Location: , , USA.
Posts: 2,913
Thanks: 53
Thanked 235 Times in 217 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
Old 02-19-2013, 10:03 AM   #6
White Hat Marketer
War Room Member
 
Dan Grossman's Avatar
 
Join Date: Jan 2007
Location: Montgomeryville, PA
Posts: 1,466
Thanks: 78
Thanked 704 Times in 241 Posts
Social Networking View Member's Twitter Profile 
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: Campaign tracking & click fraud detection. Try the Live Demo

Dan Grossman is offline   Reply With Quote
Reply

  WarriorForum - Internet Marketing Forums > Warrior Support Forums > Programming Talk

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 01:20 PM.