|
[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]
not exactly sure if this is on topic, but I'm having
some trouble with sorting, it's basically for a search
engine for a website that sells classified ad space to
sell homes, etc. in short, people pay more for
featured ads, so they are supposed to show up at the
top of the search results, then amongst the featured
ads, the ones where people have uploaded images show
up first. after the featured ad's have been
displayed, come the non featured ads where the images
show up first, and any non featured ad with no images
show up last. after all that sorting has taken place,
the ad's are further sorted where the newest ones show
up first. I have the primary key's set on
auto_increment, so the higher the primary key value is
the newer the ad is.
i am having trouble pulling this sort off, i know i
could do it by querying the MySQL db a few times, but
I'm sure there is a way to pull this off with one
query, any help would be appreciated, i'll paste my
query, and my sample results below...
(the i stands for image, and f is for featured,
obviously where the results are null it's either not
featured or there is no image)
mysql> select distinct homead.id as aid,homead.adname,
-> image.addid as i,featured.res as f from
homead
-> inner join globalad on homead.id=globalad.addid
-> left join image on image.addid=globalad.id
-> left join featured on featured.res=globalad.id
-> where globalad.tablename='homead'
-> order by f desc,i desc,aid desc;
+-----+-----------------------+------+------+
| aid | adname | i | f |
+-----+-----------------------+------+------+
| 24 | Twin Home | NULL | 33 |
| 23 | Suburban Ranch Home | 32 | 32 |
| 21 | sadf | 26 | 26 |
| 14 | Contemporary Home | 7 | NULL |
| 25 | wonderful | NULL | NULL |
| 22 | house | NULL | NULL |
| 20 | Home in Blue bell | NULL | NULL |
| 19 | Nice Home | NULL | NULL |
| 18 | Rock House | NULL | NULL |
| 17 | Glass House | NULL | NULL |
| 16 | Wooden House | NULL | NULL |
| 15 | Farm in NJ | NULL | NULL |
| 13 | Townhouse in E. PA | NULL | NULL |
| 12 | Nice Home in Delaware | NULL | NULL |
| 11 | Ranch Home | NULL | NULL |
| 10 | Tiny House | NULL | NULL |
| 9 | Big House | NULL | NULL |
| 8 | Nice House | NULL | NULL |
+-----+-----------------------+------+------+
18 rows in set (0.01 sec)
the main spot where this is not working is that the
first result is a featured ad with no image present,
but there are featured ad's WITH images, so the first
result should go after the featured ads not before, i
tried sorting the images before the featured ads with
similiar results, any help is appreciated.
Marc
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
______________________________________________________________________
Philadelphia Linux Users Group - http://www.phillylinux.org
Announcements-http://lists.phillylinux.org/mail/listinfo/plug-announce
General Discussion - http://lists.phillylinux.org/mail/listinfo/plug
|
|