Take a very simple advanced search, with a single field to search in the links for. This is a text field. I enter 'I.' (without single quotation marks), and the system does the below:
`id`,`title`,`url`,`description`,`rating`,`votes`,`validated`,`catid`,`sumofvotes`,`email`,`time`,`hits`,`numcomments`,`hide`,`ownerid`,`voterips`,`voterids`,`lastedit`,`type`,`notify`,`suspect`,`pendingedit`,`funds`,`suspended`,`alias`,`expire`,`ip`,`inalbum`,`typeorder`,`recipurl`,`hitsin`,`recipwith`,`hitsinips`,`hitsoutips`,`lastcomment`,`related`,`inhidden`,`viewers`,`threadviewers`,`hitsintemp`,`hitsouttemp`,`origtype`,`importance`,`parentids`,`timesdead`,`timesemailed`,`threadclosed`,`threadposters`,`lastposterid`,`lastpostername`,`ownername`,`deleted`,`deletionreason`,`movedto`,`deletedby`,`timevalidated`,`filefield`,`message`,`sticky`,`downloads`,`pollid`,`posticon`,`savedby`,`validatedemail`,`unpaid`,`recipverified`,`effectivetime`,`sugcatid`,`pagerank`,`wysihtml`,`movedid`,`unrevised`,`feedurl`,`feedcache`,`feedcachetime`,`tags`,`xmlsource`,`ordercomments`,`lastpadupdate`,`padfile`,`lastmonthlycheck`,`invisibleto`,`profileurl`,`lastprofileurl`,`timesrenewed`,`timedeleted`,`helyseg`,`ar`,`varos`,`epitoanyag`,`alapterulet`,`kertmeret`,`szobaszam`,`felszobaszam`,`erkely`,`futes`,`kilatas`,`tajolas`,`komfort`,`lift`,`emeleten`,`parkolas`,`lakasallapot`,`extrak`,`butorozott`,`szintek`,`kiadoar`,`helysegenglish`,`epitoanyagenglish`,`erkelyenglish`,`futesenglish`,`kilatasenglish`,`tajolasenglish`,`komfortenglish`,`liftenglish`,`parkolasenglish`,`lakasallapotenglish`,`extrakenglish`,`butorozottenglish`,`descriptionenglish`,`kerulet`,`megye`,`eladokiado`,`gepesitett`,`gepesitettenglish`,`cim`,`hazszam`,`adminnotes`,`telek`,`sponsorend`,`address`,`city`,`state`,`country`,`latitude`,`longitude`,`zip`,`kiadoareuro` FROM wsnlinks_links WHERE (validated=1 AND invisibleto NOT LIKE '%|3|%' AND hide=0 AND deleted=0 AND alias=0 AND inalbum=0) AND ((effectivetime < 1220281754) AND ((kerulet LIKE '% I. %'))) ORDER BY typeorder ASC,time DESC LIMIT 0,60
Load time so far: 0.09 seconds.
Load time so far: 0.09 seconds.
Query 10: SELECT COUNT(id) FROM wsnlinks_links WHERE (validated=1 AND invisibleto NOT LIKE '%|3|%' AND hide=0 AND deleted=0 AND alias=0 AND inalbum=0) AND ((effectivetime < 1220281754) AND ((kerulet LIKE '% I. %')))
What is interesting is the LIKE '% I. %'. Why is there a space around my 'I.'? If there wouldn't be, the search would find what I was looking for (I think, I hope).
`id`,`title`,`url`,`description`,`rating`,`votes`,`validated`,`catid`,`sumofvotes`,`email`,`time`,`hits`,`numcomments`,`hide`,`ownerid`,`voterips`,`voterids`,`lastedit`,`type`,`notify`,`suspect`,`pendingedit`,`funds`,`suspended`,`alias`,`expire`,`ip`,`inalbum`,`typeorder`,`recipurl`,`hitsin`,`recipwith`,`hitsinips`,`hitsoutips`,`lastcomment`,`related`,`inhidden`,`viewers`,`threadviewers`,`hitsintemp`,`hitsouttemp`,`origtype`,`importance`,`parentids`,`timesdead`,`timesemailed`,`threadclosed`,`threadposters`,`lastposterid`,`lastpostername`,`ownername`,`deleted`,`deletionreason`,`movedto`,`deletedby`,`timevalidated`,`filefield`,`message`,`sticky`,`downloads`,`pollid`,`posticon`,`savedby`,`validatedemail`,`unpaid`,`recipverified`,`effectivetime`,`sugcatid`,`pagerank`,`wysihtml`,`movedid`,`unrevised`,`feedurl`,`feedcache`,`feedcachetime`,`tags`,`xmlsource`,`ordercomments`,`lastpadupdate`,`padfile`,`lastmonthlycheck`,`invisibleto`,`profileurl`,`lastprofileurl`,`timesrenewed`,`timedeleted`,`helyseg`,`ar`,`varos`,`epitoanyag`,`alapterulet`,`kertmeret`,`szobaszam`,`felszobaszam`,`erkely`,`futes`,`kilatas`,`tajolas`,`komfort`,`lift`,`emeleten`,`parkolas`,`lakasallapot`,`extrak`,`butorozott`,`szintek`,`kiadoar`,`helysegenglish`,`epitoanyagenglish`,`erkelyenglish`,`futesenglish`,`kilatasenglish`,`tajolasenglish`,`komfortenglish`,`liftenglish`,`parkolasenglish`,`lakasallapotenglish`,`extrakenglish`,`butorozottenglish`,`descriptionenglish`,`kerulet`,`megye`,`eladokiado`,`gepesitett`,`gepesitettenglish`,`cim`,`hazszam`,`adminnotes`,`telek`,`sponsorend`,`address`,`city`,`state`,`country`,`latitude`,`longitude`,`zip`,`kiadoareuro` FROM wsnlinks_links WHERE (validated=1 AND invisibleto NOT LIKE '%|3|%' AND hide=0 AND deleted=0 AND inalbum=0) AND ((effectivetime < 1220283618) AND ((catid = 63))) ORDER BY typeorder ASC,time DESC LIMIT 0,60
Granted I changed the condition to OR, instead of LIKE, but I still don't understand.
Just ran the original QUERY that gets generated by the advanced search form, but erased those spaces in % I. % and it works good. So which PHP files are responsible for generating these search queries and putting those spaces around the search terms?
You're doing an "any of the words" search. A word is loosely defined as something with spaces around it. If you don't want spaces around the search term, do a "contains" search.
LIKE search works, if you submit a single thing to search for. But if you want to search for multiple values, the LIKE cannot work. So how is the OR search supposed to function? I mean, if I have a text input and enter two words, the OR type search should search for those 2 words. What if I don't have spaces around those 2 words? I won't find them? That would beat the purpose of the OR search, wouldn't it?
So the question is, how can we make the OR/AND type search actually search for the submitted words?
Any/all words searches will fail to match around punctuation and the like if you have the faster setting selected in your search settings... if you have the slower selection, it'll use a regular expression to catch more possibilities.
Well, that didn't work. I changed those settings in the admin panel and changed the search type to OR, and I still get those spaces around the searchterms.
Works perfectly. Presumably you don't understand the query you're reading. I'll look like this:
SELECT `id`,`title`,`url`,`description`,`rating`,`votes`,`validated`,`catid`,`sumofvotes`,`email`,`time`,`hits`,`numcomments`,`hide`,`ownerid`,`voterips`,`voterids`,`lastedit`,`type`,`notify`,`suspect`,`pendingedit`,`funds`,`suspended`,`alias`,`expire`,`ip`,`inalbum`,`typeorder`,`recipurl`,`hitsin`,`recipwith`,`hitsinips`,`hitsoutips`,`lastcomment`,`related`,`inhidden`,`viewers`,`threadviewers`,`hitsintemp`,`hitsouttemp`,`origtype`,`importance`,`parentids`,`timesdead`,`timesemailed`,`threadclosed`,`threadposters`,`lastposterid`,`lastpostername`,`ownername`,`deleted`,`deletionreason`,`movedto`,`deletedby`,`timevalidated`,`filefield`,`message`,`sticky`,`downloads`,`pollid`,`posticon`,`savedby`,`validatedemail`,`unpaid`,`recipverified`,`effectivetime`,`sugcatid`,`pagerank`,`wysihtml`,`movedid`,`unrevised`,`feedurl`,`feedcache`,`feedcachetime`,`tags`,`xmlsource`,`ordercomments`,`lastpadupdate`,`padfile`,`lastmonthlycheck`,`invisibleto`,`profileurl`,`lastprofileurl`,`timesrenewed`,`timedeleted`,`sponsorend`,`address`,`city`,`state`,`country`,`latitude`,`longitude`,`zip` FROM wsnlinks_links WHERE (validated=1 AND invisibleto NOT LIKE '%|3|%' AND hide=0 AND deleted=0 AND alias=0 AND inalbum=0) AND ((effectivetime < 1220502999) AND (((title LIKE '% a %' OR title LIKE '% a' OR title LIKE 'a %' OR title = 'a' OR title REGEXP ("[ ]+a[ ]+"))))) ORDER BY hits DESC LIMIT 0,42
So how can I get your query above, to be something like this:
SELECT `id`,`title`,`url`,`description`,`rating`,`votes`,`validated`,`catid`,`sumofvotes`,`email`,`time`,`hits`,`numcomments`,`hide`,`ownerid`,`voterips`,`voterids`,`lastedit`,`type`,`notify`,`suspect`,`pendingedit`,`funds`,`suspended`,`alias`,`expire`,`ip`,`inalbum`,`typeorder`,`recipurl`,`hitsin`,`recipwith`,`hitsinips`,`hitsoutips`,`lastcomment`,`related`,`inhidden`,`viewers`,`threadviewers`,`hitsintemp`,`hitsouttemp`,`origtype`,`importance`,`parentids`,`timesdead`,`timesemailed`,`threadclosed`,`threadposters`,`lastposterid`,`lastpostername`,`ownername`,`deleted`,`deletionreason`,`movedto`,`deletedby`,`timevalidated`,`filefield`,`message`,`sticky`,`downloads`,`pollid`,`posticon`,`savedby`,`validatedemail`,`unpaid`,`recipverified`,`effectivetime`,`sugcatid`,`pagerank`,`wysihtml`,`movedid`,`unrevised`,`feedurl`,`feedcache`,`feedcachetime`,`tags`,`xmlsource`,`ordercomments`,`lastpadupdate`,`padfile`,`lastmonthlycheck`,`invisibleto`,`profileurl`,`lastprofileurl`,`timesrenewed`,`timedeleted`,`sponsorend`,`address`,`city`,`state`,`country`,`latitude`,`longitude`,`zip` FROM wsnlinks_links WHERE (validated=1 AND invisibleto NOT LIKE '%|3|%' AND hide=0 AND deleted=0 AND alias=0 AND inalbum=0) AND ((effectivetime < 1220502999) AND (((title LIKE '%a%' OR title LIKE '% a %' OR title LIKE '% a' OR title LIKE 'a %' OR title = 'a' OR title REGEXP ("[ ]+a[ ]+"))))) ORDER BY hits DESC LIMIT 0,42
I added: title LIKE '%a%'
Any way to add such/replace this query to do that?
Please don't hate me, but I literally changed 2 things in searchlogic.php. I changed these: '% $searchitem %' into this: '%$searchitem%'. That's the only way for the OR type search to find values I submit for a single field.
Comments on Advanced search bug? or...
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
Hi Paul,
I don't know if this is a bug, or something else.
Take a very simple advanced search, with a single field to search in the links for. This is a text field. I enter 'I.' (without single quotation marks), and the system does the below:
What is interesting is the LIKE '% I. %'. Why is there a space around my 'I.'? If there wouldn't be, the search would find what I was looking for (I think, I hope).
Thanks
David
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
Interesting. I changed the input fields to search for catids like this
and it didn't do the same
Granted I changed the condition to OR, instead of LIKE, but I still don't understand.
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
Just ran the original QUERY that gets generated by the advanced search form, but erased those spaces in % I. % and it works good. So which PHP files are responsible for generating these search queries and putting those spaces around the search terms?
Thanks
David
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
You're doing an "any of the words" search. A word is loosely defined as something with spaces around it. If you don't want spaces around the search term, do a "contains" search.
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
LIKE search works, if you submit a single thing to search for. But if you want to search for multiple values, the LIKE cannot work. So how is the OR search supposed to function? I mean, if I have a text input and enter two words, the OR type search should search for those 2 words. What if I don't have spaces around those 2 words? I won't find them? That would beat the purpose of the OR search, wouldn't it?
So the question is, how can we make the OR/AND type search actually search for the submitted words?
Thanks
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
Any/all words searches will fail to match around punctuation and the like if you have the faster setting selected in your search settings... if you have the slower selection, it'll use a regular expression to catch more possibilities.
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
Well, that didn't work. I changed those settings in the admin panel and changed the search type to OR, and I still get those spaces around the searchterms.
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
Works perfectly. Presumably you don't understand the query you're reading. I'll look like this:
SELECT `id`,`title`,`url`,`description`,`rating`,`votes`,`validated`,`catid`,`sumofvotes`,`email`,`time`,`hits`,`numcomments`,`hide`,`ownerid`,`voterips`,`voterids`,`lastedit`,`type`,`notify`,`suspect`,`pendingedit`,`funds`,`suspended`,`alias`,`expire`,`ip`,`inalbum`,`typeorder`,`recipurl`,`hitsin`,`recipwith`,`hitsinips`,`hitsoutips`,`lastcomment`,`related`,`inhidden`,`viewers`,`threadviewers`,`hitsintemp`,`hitsouttemp`,`origtype`,`importance`,`parentids`,`timesdead`,`timesemailed`,`threadclosed`,`threadposters`,`lastposterid`,`lastpostername`,`ownername`,`deleted`,`deletionreason`,`movedto`,`deletedby`,`timevalidated`,`filefield`,`message`,`sticky`,`downloads`,`pollid`,`posticon`,`savedby`,`validatedemail`,`unpaid`,`recipverified`,`effectivetime`,`sugcatid`,`pagerank`,`wysihtml`,`movedid`,`unrevised`,`feedurl`,`feedcache`,`feedcachetime`,`tags`,`xmlsource`,`ordercomments`,`lastpadupdate`,`padfile`,`lastmonthlycheck`,`invisibleto`,`profileurl`,`lastprofileurl`,`timesrenewed`,`timedeleted`,`sponsorend`,`address`,`city`,`state`,`country`,`latitude`,`longitude`,`zip` FROM wsnlinks_links WHERE (validated=1 AND invisibleto NOT LIKE '%|3|%' AND hide=0 AND deleted=0 AND alias=0 AND inalbum=0) AND ((effectivetime < 1220502999) AND (((title LIKE '% a %' OR title LIKE '% a' OR title LIKE 'a %' OR title = 'a' OR title REGEXP ("[]+a[
]+"))))) ORDER BY hits DESC LIMIT 0,42
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
Well, that's not the query that I get... oh well, I literally put spaces in around the values in the DB, and that seemed to do the trick.
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
nope, it didn't work. that's not a solution...
So how can I get your query above, to be something like this:
I added: title LIKE '%a%'
Any way to add such/replace this query to do that?
Thanks
David
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
Thanks
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
You don't do anything to get the query except leave things as they normally are.
Forum Regular
Usergroup: Customer
Joined: Jun 22, 2005
Total Topics: 91
Total Comments: 305
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
That's just going to break every upgrade for you, but hey, you're the one doing the extra work.