Search results ordered by update_date

[peter_johnson]

Hi,
Is it possible to classify the search results according the update date ?
Best regards
Peter

[vinyl-junkie]

First of all, welcome to the phpDig forum, Peter. We're glad to have you here.

This is untested, but please find the following code in search_function.php:

PHP Code:

$query = "SELECT sites.site_url, sites.port, spider.path,spider.file,spider.first_words,
sites.site_id,spider.spider_id,spider.last_modified,spider.md5 "
."FROM ".PHPDIG_DB_PREFIX."spider AS 
spider, ".PHPDIG_DB_PREFIX."sites AS sites "
."WHERE spider.spider_id=$spider_id AND sites.site_id = spider.site_id"; 


and change it like so:

PHP Code:

$query = "SELECT sites.site_url, sites.port, spider.path,spider.file,spider.first_words,
sites.site_id,spider.spider_id,spider.last_modified,spider.md5 "
                      ."FROM ".PHPDIG_DB_PREFIX."spider AS spider, ".PHPDIG_DB_PREFIX."sites AS sites "
                      ."WHERE spider.spider_id=$spider_id AND sites.site_id = spider.site_id ORDER BY spider.last_modified"; 


Be sure to remove any word wrapping in the above code.

Let us know if this worked, too. Thanks!

[nimbus]

I seem to have a problem.
I am trying to have the results appear in date order (latest update first). I tried your ORDER BY idea but unfortunatly, it does not change anything.

I am truly stuck. Is there another way?

Thanks in advance

[synnalagma]

This don't work because ordering is done on the php code and not on the database.

Try the following :
Install the mod I've submitted : Optimized Search
In queryparser.php around line 100 change

PHP Code:

$usetspider=false; 


to

PHP Code:

$usetspider=true; 


In queryparser.php around line 126

PHP Code:

$this->sql="SELECT $tengine.spider_id, SUM($tengine.weight) AS w $operatorquery FROM ".($usetspider?"$tspider, ":"")." $tengine WHERE $tengine.key_id IN (".implode(',',$this->wordsid).") $spiderquery GROUP BY $tengine.spider_id ".($operatorhaving!=""?"HAVING $operatorhaving ":"")."ORDER BY w DESC"; 


and change it to :

PHP Code:

$this->sql="SELECT $tengine.spider_id, SUM($tengine.weight) AS w $operatorquery FROM ".($usetspider?"$tspider, ":"")." $tengine WHERE $tengine.key_id IN (".implode(',',$this->wordsid).") $spiderquery GROUP BY $tengine.spider_id ".($operatorhaving!=""?"HAVING $operatorhaving ":"")."ORDER BY $tspider.last_modified, w DESC"; 


Also change around line 140

PHP Code:

$this->sql.=" END)) AS w $operatorquery FROM ".($usetspider?"$tspider, ":"")."$tengine WHERE $tengine.key_id IN (".implode(',',$this->wordsid).")  $spiderquery GROUP BY $tengine.spider_id HAVING w>0".($operatorhaving!=""?" AND $operatorhaving ":"")." ORDER BY w DESC"; 


to

PHP Code:

$this->sql.=" END)) AS w $operatorquery FROM ".($usetspider?"$tspider, ":"")."$tengine WHERE $tengine.key_id IN (".implode(',',$this->wordsid).")  $spiderquery GROUP BY $tengine.spider_id HAVING w>0".($operatorhaving!=""?" AND $operatorhaving ":"")." ORDER BY $tspider.last_modified, w DESC"; 


This should sort results by last modified and by results weight if two results have the same date.

Note I didn't tested it but it should work

[nimbus]

Thanks synnalagma,

Followed your way and I have a better result. I do not have a chronological sort yet but I believe the weight plays a bit. Still trying and will keep you posted.

By the way, your Optimised search is truly optimised.
Thanks again,

Nimbus

[halley]

Hello

I have this error do u have a idea ?
SELECT SQL_BIG_RESULT engine.spider_id, SUM((CASE WHEN engine.key_id IN (1320) THEN engine.weight *1.0 WHEN engine.key_id IN (14001,170283,49543,334503,30322,337511,298133,32081,161324,164303,10818,35 0144,341169,286488,44820,400835,64352,76436) THEN engine.weight *0.8 END)) AS w FROM engine WHERE engine.key_id IN (1320,14001,170283,49543,334503,30322,337511,298133,32081,161324,164303,108 18,350144,341169,286488,44820,400835,64352,76436) GROUP BY engine.spider_id HAVING w>0 ORDER BY spider.last_modified, w DESC Table inconnue 'spider' dans order clause
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\phpdig\libs\other_search_function.php on line 163

Thks

[synnalagma]

Verify that PHPDIG_DB_PREFIX is defined in config.php if not set it to the correct value (usually 'phpdig_').

If it's set try including config.php in class.queryparser.php by adding on top

include(PATH_TO_PHP_DIG/config.php);
(check to have correct values).

Should work