homepage Welcome to WebmasterWorld Guest from 54.197.110.151
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
actolearn



 
Msg#: 4544781 posted 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

<?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

WebmasterWorld Administrator jatar_k us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4544781 posted 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]

actolearn



 
Msg#: 4544781 posted 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

jatar_k

WebmasterWorld Administrator jatar_k us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4544781 posted 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.

actolearn



 
Msg#: 4544781 posted 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 ~

jatar_k

WebmasterWorld Administrator jatar_k us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4544781 posted 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

actolearn



 
Msg#: 4544781 posted 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

jatar_k

WebmasterWorld Administrator jatar_k us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4544781 posted 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

actolearn



 
Msg#: 4544781 posted 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