PDA

View Full Version : Search results ordered by update_date


peter_johnson
04-09-2004, 09:04 AM
Hi,
Is it possible to classify the search results according the update date ?
Best regards
Peter

vinyl-junkie
04-09-2004, 05:27 PM
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:$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:$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
06-09-2004, 01:03 AM
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:confused:

synnalagma
06-09-2004, 01:51 AM
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
$usetspider=false;
to
$usetspider=true;

In queryparser.php around line 126

$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 :

$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

$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

$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
06-09-2004, 02:43 AM
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
09-13-2004, 07:33 AM
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
09-14-2004, 02:56 AM
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