By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Having trouble with MyQSL Queries

DTeCH
P: 23
I have a heavy database with file information... this info includes file names, age of files, posters, & what have you.

My problems come from returning results with the "LIKE", or "REGEXP" functions.

Here is an example of records:

Title
Size
Date Posted
Poster

Way.Of.The.Panda.2010.DVDRip.XviD-HVK
768.96 MB
Fri, 26 Mar 2010 23:29:02 +0000
vivalaboobs

Despicable.Me.2010.SLOSiNH.DVDRip.XviD-PANDA
756.95 MB
Thu, 10 Feb 2011 10:47:03 +0000
dudek@localhost (dudek)

Kung.Fu.Panda.2.2011.TS.V2.XViD-EP1C
1.6 GB
Tue, 14 Jun 2011 03:19:19 +0000
knowinservers.com (fbi@thedoor.brb)

Chop.Kick.Panda.2011.NTSC.DVDR-STOCK
1.92 GB
Tue, 05 Jul 2011 06:38:44 +0000
HickDead

Secret.Origin.The.Story.of.DC.Comics.2010.NTSC.DVD R-SADPANDA
3.97 GB
Sat, 04 Jun 2011 11:45:40 +0000
HickDead


I have tried the LIKE statement first:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM p_search WHERE sTITLE LIKE '" & sQuery0 & "' ORDER BY tid DESC LIMIT 100;"
  2.  

sQuery0 is the search term passed in by the user, & it is fixed like this (VB.NET):
Expand|Select|Wrap|Line Numbers
  1. sQuery0 = "%" & sQuery0.Replace(" ", "%") & "%"
  2. sQuery0 = sQuery0.Replace("'", "")
  3.  
sQuery0 has all of it's Space characters replaced with the % symbol to represent any number of characters, & it's single quote characters stripped away. So... Secret Origin' Story DC Comics would become %Secret%Origin%Story%DC%Comics%, & this would be the final query term from the user.


This works (Partially)...

If i search for Kung Fu Panda, nothing is returned. (%Kung%Fu%Panda%)

If I try Kung Panda, Nothing is returned. (%Kung%Panda%)

But if I try ung Panda or ung Fu Panda, I get results. (%ung%Panda%) or (%ung%Fu%Panda%)... removing the K from Kung.

I understand that the % character means MORE than 0 characters, so it cannot be used, because Cars 2 will not be found by (%Cars%2%) query becuse it is looking for more than Cars like XCars 2 DVDRip, so the query will have to be altered to something like (%ars%2%). This cannot be used because it will return a lot of un-wanted results as well.


I then tried REGEXP like this:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM p_search WHERE sTITLE REGEXP '" & sQuery0 & "' ORDER BY tid DESC LIMIT 100;"
  2.  

sQuery0 is the search term passed in by the user, & it is now fixed like this (VB.NET):
Expand|Select|Wrap|Line Numbers
  1. sQuery0 = sQuery0.Replace(" ", "*")
  2. sQuery0 = sQuery0.Replace("'", "")
  3.  
It no longer places a character at the start, & end of the query (*), it now just replaces the space characters with the * character to represent any number of characters, but still strips away the single quote character. So... Secret Origin' Story DC Comics would become Secret*Origin*Story*DC*Comics, & this would be the final query term from the user.


This does not work at all.

If i search for Kung Fu Panda, nothing is returned. (Kung*Fu*Panda)

If I try Kung Panda, Nothing is returned. (Kung*Panda)

But if I try Panda, I get results. (Panda)... anything containing the word Panda is returned. maybe because there are no * characters in the final query?

I am at a loss. I am in no way a guru, or a regex master... I'm not even a noob. I'm 10 feet below that.

Can someone please give me an example of the proper way to get the results of ALL records that match the user's entry, taking into account that users will not always enter single word queries like Panda?

Thank you guys.
Sep 9 '11 #1

✓ answered by js1987

If you are searching for Kung Fu Panda, then this query would work:

strSQL = "SELECT * FROM p_search WHERE sTITLE LIKE '%Kung%' AND
sTITLE LIKE '%Fu%' AND
sTITLE LIKE '%Panda%'
ORDER BY tid DESC LIMIT 100;"

So you need to replace your spaces in the query by AND <tablename> LIKE '%<word>%'

Share this Question
Share on Google+
1 Reply


P: 2
If you are searching for Kung Fu Panda, then this query would work:

strSQL = "SELECT * FROM p_search WHERE sTITLE LIKE '%Kung%' AND
sTITLE LIKE '%Fu%' AND
sTITLE LIKE '%Panda%'
ORDER BY tid DESC LIMIT 100;"

So you need to replace your spaces in the query by AND <tablename> LIKE '%<word>%'
Sep 12 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.