php 2 where clauses and 2 orders

[actolearn]

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

<?php
$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.

[jatar_k]

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]

[actolearn]

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

[jatar_k]

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.

[actolearn]

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 ~

[jatar_k]

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

should work just like that

[actolearn]

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

[jatar_k]

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

[actolearn]

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.