phranque
msg:4520759 | 2:36 pm on Nov 20, 2012 (gmt 0) |
welcome to WebmasterWorld, yugonomex!
you either have to use an OR in your WHERE clause and do the sort in PHP,
or make 3 queries in order, using an AND clause in your WHERE clause for the first query.
|
swa66
msg:4520813 | 5:26 pm on Nov 20, 2012 (gmt 0) |
You can also use a union to let mysql combine the 3 selects into one result set.
Ref: [dev.mysql.com...]
|
phranque
msg:4520815 | 5:31 pm on Nov 20, 2012 (gmt 0) |
i'm not sure how you could ORDER BY (both and then one and then the other) as specified using a UNION.
|
swa66
msg:4520817 | 5:42 pm on Nov 20, 2012 (gmt 0) |
In pseudo code:
( select * from whatever where "Narberth" and "Pembrokeshire" order by whatever limit howmany)
union all
( select * from whatever where "narberth" and not "Pembrokeshire" order by whatever limit howmany)
union all
( select * from whatever where "pembrokshire" and not "narberth" order by whatever limit howmany)
AFAIK the order the results come in are the same as the order of the union - although I admit this does not seem to be documented as such.
You can order and limit the individual results and the complete set by using parentheses.
Note that I'm myself avoiding duplicates so union all can be used and that might be the trick to keep the order. (Not tested it)
|
phranque
msg:4520818 | 5:50 pm on Nov 20, 2012 (gmt 0) |
i had never assumed the order would be maintained nor had i tested it.
let us know how it works, yugonomex.
|
swa66
msg:4520823 | 6:08 pm on Nov 20, 2012 (gmt 0) |
Regardless you could always include an additional column you add the select it came from and then sort on that, SQL is a fully featured language. Most you can imagine to do can be done in one (complex) query.
Actually the docs state the approach by adding a column to indicate what select the row comes from
To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY following the last SELECT:
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
|
|
Quoted from [dev.mysql.com...]
|
phranque
msg:4520918 | 11:51 pm on Nov 20, 2012 (gmt 0) |
good one - i often forget that trick!
|
vincevincevince
msg:4520949 | 2:47 am on Nov 21, 2012 (gmt 0) |
You should be able to include a sorting column something like:
select .... rest of the query here, for both keywords ... ORDER BY (**kw-query1**)+(**kw-query2**) DESC
Or; use MATCH AGAINST as that automatically does this kind of scoring for you.
|
yugonomex
msg:4521586 | 2:03 pm on Nov 22, 2012 (gmt 0) |
select t.*
from
tbl_index t inner join
(
select
r.file_id,
count(*) as rank
from
tbl_filekeywords r
where
r.keyword_id in ( $keywords )
group by
r.file_id
) r2
on r2.file_id = t.file_id
order by
r2.rank
Hey guys thanks again for the info provided, the solution in the end is shown above. I used ranks to place the most appropriate rows at the top of the table. All the help was really appreciated and you'll most likely be hearing from me again soon haha :)
Ad
|
|
