Msg#: 4520722 posted 1:07 pm on Nov 20, 2012 (gmt 0)
new to webmaster so i hope someone can help with my problem :)
Firstly what i'm trying to create... I want to have a php page that display multiple keywords (checkboxes) for example areas such as Narberth, Pembrokeshire, Carmarthenshire etc as well as other things.
So far i have created a way to do this and display the data separately in tables. What I'm having trouble with is showing the rows with multiple keywords associated with them first.
So if the user searched "Narberth" and "Pembrokeshire" the results would show any documents with both those keywords in first then anything with just "narberth" and just "pembrokshire" afterwords.
Hope this makes sense haha I listed my code for what I've worked out so far, hope to hear from you soon!
Msg#: 4520722 posted 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)
Msg#: 4520722 posted 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;
Msg#: 4520722 posted 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 :)