Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    q1h
    q1h is offline
    New Coder
    Join Date
    May 2011
    Posts
    34
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Order primary query by secondary query?

    Hi, I have the following query set up:

    PHP Code:
      $query1 mysql_query("SELECT category FROM table GROUP BY category ORDER BY date ASC");

      while ( 
    $data1 mysql_fetch_array$query1 ) ) {

        
    $category $data1['category'];

        echo 
    '<p>' $category '</p>';

        
    $query2 mysql_query("SELECT * FROM table WHERE category = '$category' ORDER BY date ASC");

        while( 
    $data2 mysql_fetch_array$query2 ) ) {

          echo 
    '<p>' $data2['data'] . '</p>';

        } 

      } 
    I'm grouping my data into categories and then sorting by a date (soonest first, using strtotime). My issue is that the sub-query sorts correctly, but I want the primary query to show the category of whichever date is earliest first. Right now, it's sorting the categories alphabetically. Do I need some kind of date average? Thanks ...

  • #2
    q1h
    q1h is offline
    New Coder
    Join Date
    May 2011
    Posts
    34
    Thanks
    7
    Thanked 0 Times in 0 Posts
    What?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Well, first of all, you shouldn't be using two queries.

    Try to never never never do one query inside a loop on another query. You are killing your performance, possibly by an order of magnitude or more.

    So...

    Code:
    SELECT SQ.category, SQ.firstDate, T.list, T.of, T.fields 
    FROM ( SELECT category, MIN(`date`) AS firstDate 
           FROM table 
           GROUP BY category ) AS SQ,
           table AS T
    WHERE T.category = SQ.category
    ORDER BY SQ.firstDate ASC, T.`date` ASC
    (1) Never use SELECT *, especially when working with multiple tables (even when one is a pseudo-table created by a subquery, as here). It's not really a good idea to ever user SELECT *, but it's especially bad when using multiple tables.

    (2) date is a builtin function in MySQL (and in many other DBs). To ensure you don't confuse your field named date with the function, put back ticks (`...` -- the character that shares a key witn ~ ) around the name.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    q1h (01-09-2012)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Oh, and I don't code in PHP, but you would then use that single query something like this:
    Code:
    $sql = "SELECT SQ.category, SQ.firstDate, T.list, T.of, T.fields 
            FROM ( SELECT category, MIN(`date`) AS firstDate 
                   FROM table 
                   GROUP BY category ) AS SQ,
                   table AS T
            WHERE T.category = SQ.category
            ORDER BY SQ.firstDate ASC, T.`date` ASC";
    
    $query = mysql_query( $sql ) or die( mysql_error );
    
    $categpry = "";
    while ( $data = mysql_fetch_array( $query ) ) 
    {
        $cur_category = $data["category"];
        if ( $cur_category != $category )
        {
             echo "<h3>" . $cur_category . "</h3>\n";
             $category = $cur_category
        }
        echo "<p>" . $data["...whatever field(s)..."] . "</p>";
    }
    As I said, I don't use PHP, so use that only as a guideline. The "trick" is to monitor the category field and output a new category only when it changes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    q1h (01-09-2012)

  • #5
    q1h
    q1h is offline
    New Coder
    Join Date
    May 2011
    Posts
    34
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks, Old Pedant, that worked!

    One thing, though - I was trying to keep the category dependencies together and simply show the block of whichever one contained the earliest date first. I tried a couple variations of your code, but I can't seem to get the dependencies together ...

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Sorry, no idea what you mean by "category dependencies."

    The only dependencies I saw in your original code was that you wanted all whatever-they-are that are in the same category grouped together.

    If you mean you have subcategories, then probably all you need to do is put them into your ORDER BY clause in place of using `date`.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    q1h
    q1h is offline
    New Coder
    Join Date
    May 2011
    Posts
    34
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I mean that I'm trying to get all the data from one category to display together under the category heading, with the category headings sorted by date, and the data itself sorted by date. Thanks ...

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Well, so far as I can tell--without having your database in front of me--that query *should* be doing exactly that.

    Here is some sample data:
    Code:
    +----------+---------------------+--------------------------+
    | category | date                | data                     |
    +----------+---------------------+--------------------------+
    | animals  | 2011-01-01 00:00:00 | aardvarks come first     |
    | people   | 2010-12-01 00:00:00 | adam came first          |
    | animals  | 2011-01-15 00:00:00 | koalas are in the middle |
    | people   | 2011-06-01 00:00:00 | old pedant is still here |
    | animals  | 2011-02-02 00:00:00 | zebras come last         |
    +----------+---------------------+--------------------------+
    and this is what I get from that query:
    Code:
    +----------+---------------------+---------------------+--------------------------+
    | category | firstDate           | date                | data  |
    +----------+---------------------+---------------------+--------------------------+
    | people   | 2010-12-01 00:00:00 | 2010-12-01 00:00:00 | adam came first  |
    | people   | 2010-12-01 00:00:00 | 2011-06-01 00:00:00 | old pedant is still here |
    | animals  | 2011-01-01 00:00:00 | 2011-01-01 00:00:00 | aardvarks come first  |
    | animals  | 2011-01-01 00:00:00 | 2011-01-15 00:00:00 | koalas are in the middle |
    | animals  | 2011-01-01 00:00:00 | 2011-02-02 00:00:00 | zebras come last  |
    +----------+---------------------+---------------------+--------------------------+
    which sure seems to me to be what you asked for.

    What am I missing?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    q1h (01-12-2012)

  • #9
    q1h
    q1h is offline
    New Coder
    Join Date
    May 2011
    Posts
    34
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I think it must have been my PHP that messed up the results - the raw mysql data looks like what you have. Thanks for all your help!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •