Having trouble with MyQSL Queries

[DTeCH]

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 ([e-mail])

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:

strSQL = "SELECT * FROM p_search WHERE sTITLE LIKE '" & sQuery0 & "' ORDER BY tid DESC LIMIT 100;"
 

sQuery0 is the search term passed in by the user, & it is fixed like this (VB.NET):

sQuery0 = "%" & sQuery0.Replace(" ", "%") & "%"
sQuery0 = sQuery0.Replace("'", "")
 

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:

strSQL = "SELECT * FROM p_search WHERE sTITLE REGEXP '" & sQuery0 & "' ORDER BY tid DESC LIMIT 100;"
 

sQuery0 is the search term passed in by the user, & it is now fixed like this (VB.NET):

sQuery0 = sQuery0.Replace(" ", "*")
sQuery0 = sQuery0.Replace("'", "")
 

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.

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