Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    281
    Rep Power
    11

    Question Order queries using php (?)


    I'm using SQL in about 10 queries to echo in PHP some scores and names in a list like this:

    Highest Scores
    1) Jerry 92%
    2) Lisa 99%
    3) Tom 65%
    4) Bob 78%
    etc

    Each line on the list is a query that spits out name and score, but as you can see they are not in order.

    How can I get the page to order the results so that they will order themselves automatically?

    Would it be better to somehow order them in a larger SQL query and then post results that way?
    Last edited by Piston2; September 26th, 2011 at 01:03 PM.
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,183
    Rep Power
    9398
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    281
    Rep Power
    11
    Originally Posted by requinix
    You mean apart from using an ORDER BY?
    yeah. I can user order by in a single query, but I want to order all of the query results. I think I have to make another set of queries to designate one query as #1, then the second as #2, etc. Then I can post the new variables into the code
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Could it not be one query (or several UNIONed subqueries with an ORDER BY on the superquery?

    Or maybe look at php's array_sort() function.
    Last edited by cafelatte; September 26th, 2011 at 02:02 PM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    281
    Rep Power
    11
    Originally Posted by cafelatte
    Could it not be one query (or several UNIONed subqueries with an ORDER BY on the superquery?

    Or maybe look at php's array_sort() function.
    I'm pretty bad at coding so any of those would probably work. I'm going to instead put results into a js grid that will sort them. it'll look nicer anyway
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    You can probably do this in one query instead of 5. Show us what your tables look like. Show only the table names and relevant field names, we don't want to slog through all 45 tables in your database.

    -Dan
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    281
    Rep Power
    11
    one table

    name society donation
    John coastal $100
    Jane urban $250
    Fred city $225
    Jack coastal $175

    I'm trying to create a list of the average donation per society with the highest at the top.

    queries look like this:
    Code:
    $query = "SELECT ROUND(AVG(donation))
    from table1
    where donation != 0 
    and society LIKE '%coastal%'";
    I have a query for each society and the result is echoed like this in php:

    echo "Coastal: $row->donation%";
    echo "Urban: $row->donation%";
    echo "City: $row->donation%";
    Last edited by Piston2; September 26th, 2011 at 04:07 PM.
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    PHP Code:
    $query "SELECT society, ROUND(AVG(donation)) donations
    from table1
    where donation != 0 
    group by society 
    order by ROUND(AVG(donation)) desc"

    -Dan
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,953
    Rep Power
    4033
    Code:
    $query = "
    SELECT society, ROUND(AVG(donation)) as donation
    from table1
    where donation != 0 
    ORDER BY
       donation
    "
    
    //...
    while ($row=...){
       echo ucfirst($row->socity).": ".$row->donation;
    }

    Just do a query without the limit on the society, and echo the society for each row along with the donation.

    If you want to do something different besides just echo them like that, you can store them in an array with the society name as the key, and the donation as the value.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    281
    Rep Power
    11
    I wish it were that easy but some people are in multiple societies and I need to use the LIKE operator to get them out one by one. The multiple society values look like this: city|*|urban
  20. #11
  21. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,953
    Rep Power
    4033
    Originally Posted by Piston2
    I wish it were that easy but some people are in multiple societies and I need to use the LIKE operator to get them out one by one. The multiple society values look like this: city|*|urban
    You said you just needed average donations per society. That is what the query gives you.

    If you need to factor in the people too, that can be done as well, but you need to state what your actually trying to accomplish so we can accurately help you.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    281
    Rep Power
    11
    Originally Posted by kicken
    You said you just needed average donations per society. That is what the query gives you.

    If you need to factor in the people too, that can be done as well, but you need to state what your actually trying to accomplish so we can accurately help you.
    that query doesn't separate out the societies. It would give me all of the different values in the field which are numerous. Some people have multiple societies, but there are not multiple rows - instead the field keeps those options in the field itself. So there may be 100 different values in the table for "society" but there are only 10 societies. I think I will try something like you proposed but use DISTINCT and some WHERE "or" variables.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    281
    Rep Power
    11
    this seems to almost work.

    Code:
    SELECT DISTINCT cb_society, ROUND( AVG( donation ) ) AS donation
    FROM jos_comprofiler
    WHERE donation !=0
    AND cb_society LIKE  '%Richmond%'
    OR cb_society LIKE  '%Northern VA%'
    OR cb_society LIKE  '%Battlefield%'
    ORDER BY donation
    These society fields can have only one society in them or many like this: [Richmond|*|Southside|*|Battlefield] so I need to make sure that I average the amount for each specific society and not just for each society entry.
  26. #14
  27. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,953
    Rep Power
    4033
    Originally Posted by Piston2
    that query doesn't separate out the societies. It would give me all of the different values in the field which are numerous. Some people have multiple societies, but there are not multiple rows - instead the field keeps those options in the field itself. So there may be 100 different values in the table for "society" but there are only 10 societies. I think I will try something like you proposed but use DISTINCT and some WHERE "or" variables.
    Sorry, I forgot the GROUP BY in mine. ManiacDan's should be correct though to get you the average per society.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    >I wish it were that easy

    First normalise your data!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo