This content has been marked as final. Show 6 replies
You could read thru the first query and add the contents to a temp query assigning a sort by field and then read thru the second query and add it to the same temp query with a sort by field. Then do a query of that temp query.
I think a union query is the way to go. Use the sort by key mentioned by LogicBlast, but simply select it as a constant in your query. Then you don't to do any looping after you run your query.
UNION all the way, with extra column for sorting:
<cfquery datasource="db" name="premium_listing_only" >
(select *, 1 AS sortcol
where advert_type = 1)
(select *, 2 AS sortcol
ORDER BY sortcol ASC
Thanks guys, I really appreciate your help.
Azadi your post did the trick so that you very much indeed.
I'm not stuck on another problem which I hadn't forseen until implementing the new query..
now the search results look 'silly' when there is a recently added premium listing as that listing appears twice on the same page of results (once at the top and then again right next to it underneath)
i know this is what i asked for but i now realise i need to find some way of making it so the listing only appears twice *if the 2 listings won't appear on the same page as each other*
how i go about doing that im not sure, probably something to do with currentrow (must be at least 10 rows between them in order to display the 2nd listing or something [there are 10 listings per page of search results])
--my head hurts now!--
if anyone has any suggestions on this little teaser i'd be v grateful indeed
i can think of crude ways to do it but they'll gobble up the cpu im sure so im hoping to come up with a nice elegant (ideally sql) solution but alas my sql knowlege is very limited [eg this is the first time ive done a UNION]
TIA once again!
Assuming your id's are numeric and are all positive integers.
Create a list of 10 zeros. Let's call this TheList
Create a new query with the same fields and datatypes as the one you ran.
Loop through your first query. If the id field does not appear in TheList, append this row to your new query. At the end of the cfif block, delete the first element of TheList and append the current id value.
Dan you're a genius (as always!) thanks mate that's a nice little solution - just implemented it and it works a treat - thanks very much indeed.
thanks to everyone for their help - really appreciate