Re: Rating results after relevance. Difficult problem

wow! Sven's idea works just great! Didn't try it on the 2000 user db though. But I looks much clearer than creating temp tables and doing all the math on 1000 users in php. So I was just wondering if there is a better way to get rid of the 0 results than I have found. First I did try to add where results != 0 but the db does not know about the results row Then I did put the whole if statement ( if (news.text_short like '%test%', 50, 0) + if (user.name > like '%woodoo%', 50, 0) into the where clause and this works. Just not that elegant. Minor prob, but maybe someone knows a better way. Thanx for the great help! Andy "Sven Herrmann" <woodoo@woodoomagic.de> schrieb im Newsbeitrag news:20020508235052.38886.qmail@pb1.pair.com... > maybe it would speed up the whole thing if the DB itself would do all the > calculations... > > here an example how this can be done.. (with mysql)... > > select news.id, if (news.text_short like '%test%', 50, 0) + if (user.name > like '%woodoo%', 50, 0) as rating from news, user where user.id = > news.creator order by rating DESC; > > explanation: > > if (news.text_short like '%test%', 50, 0) > returns 50 when in the field text_short the string "test" is found... else 0 > is returned > > + if (user.name like '%woodoo%', 50, 0) > the same... just for the name.... the result of this is simply be added to > the first result > > the result for this query looks like this: > > +----+--------+ > | id | rating | > +----+--------+ > | 1 | 100 | > | 8 | 100 | > | 9 | 100 | > | 10 | 100 | > | 11 | 100 | > | 2 | 50 | > +----+--------+ > > > so simply use PHP to build the query... and let the DB do the rest... so > you've just to do 1 query and you can directly output the result.. ;-) > > Best regards, > Sven > >