homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

php 2 where clauses and 2 orders

 2:19 am on Feb 12, 2013 (gmt 0)

Below works but when an item sells and I update it to "sold" in my DB table, I would like to:
(a) include sold items below current items
(b) sold items to be listed in "date of sale" DESC order

$items = mysql_query ("SELECT * FROM tablename WHERE status='current' AND category1='apples' OR
category2='oranges' ORDER BY prod_number DESC") or die(mysql_error());
//extract every item
while($item = mysql_fetch_array($items))

Any help would be appreciated.



 3:04 am on Feb 12, 2013 (gmt 0)

well you could look at 2 things, one being using IN [dev.mysql.com] for the multiple values for category1 and also you can use various forms of ORDER BY [dev.mysql.com]


 5:20 am on Feb 12, 2013 (gmt 0)

Thank you for the links. After looking at 2 pages worth of code, I really couldn't tell what might apply. I didn't understand most of it.

I've simplified what I need help with as shown below. Would appreciate someone showing me how to combine those two WHERE clauses. Thank you.

SELECT * FROM tablename
WHERE status='current' ORDER BY prod_number DESC
WHERE status='sold' ORDER BY date_of_sale DESC


 3:31 am on Feb 13, 2013 (gmt 0)

I think you can put them together I am just not 100% sure it would make sense

easy enough to grab the 2 values for status

where status in ('current', 'sold')

you can then add an order by with the two column listed in order

ORDER BY prod_number DESC, date_of_sale DESC

I am not sure that will look like you think, maybe add a third order by for status or a group by, hard to be absolutely sure with out knowing exactly what the data is

one option that might actually make more sense is to issue the 2 queries individually and then load each into a php array and display them how you like.


 4:48 pm on Feb 13, 2013 (gmt 0)

Thank you for your response and I see what you mean. I'm not getting exactly what I want but at least I now have both the active and sold showing in my gallery.

I didn't know to do this:
WHERE status in ('active', 'sold')

I used status= but got incorrect results because of my other conditions so "status in..." really helped.

I'll research more re third "order by" and "grouping".

Thx again ~


 5:00 pm on Feb 13, 2013 (gmt 0)

SELECT * FROM tablename
WHERE status in ('active', 'sold')
ORDER BY prod_number DESC, date_of_sale DESC

should work just like that


 5:22 pm on Feb 13, 2013 (gmt 0)

Yes, it works but sold is mixed in with the active items instead of in a group by itself at bottom of gallery.

Group 1 = active = order by prod_number DESC
Group 2 = sold = order by date_of_sale DESC ..... and GROUP together below the active group.

Is what I want not possible? Thx - AC


 5:45 am on Feb 14, 2013 (gmt 0)

I guess you could add status to the order

SELECT * FROM tablename
WHERE status in ('active', 'sold')
ORDER BY status desc, prod_number DESC, date_of_sale DESC


 7:28 pm on Feb 14, 2013 (gmt 0)

ORDER BY status DESC, prod_number DESC, date_of_sale DESC

result: SOLD at top/active at bottom/ALL in prod_number DESC

Tried changing the order of code - kept getting the sold at top or mixed in with the active.

FINALLY tried below. Didn't make sense to me but actually came closest to what I want.

ORDER BY date_of_sale ASC, status DESC, prod_number DESC

result: active at top of page/prod_number DESC/ALL sold at bottom in date_of_sale ASC. I'll use this one as it's the closest to what I want and much better than it was. If I change the date_of_sale to DESC it moves all sold back to top of gallery.

Thanks so much! I learned some new coding I didn't know about and eventually (maybe) I'll figure out why I can't get sold items to be in date_of_sale DESC order, but for now this is great.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved