problem combining DESC and DISTINCT (or GROUP BY) - Page 2
Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19

Thread: problem combining DESC and DISTINCT (or GROUP BY)

  1. #16
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    Quote Originally Posted by Square1 View Post
    Trying the following to show schema. What am I doing incorreectly?

    PHP Code:
        $query "SHOW CREATE TABLE FROM Events"
    The MySQL manual page for SHOW CREATE TABLE should help with that.

    Quote Originally Posted by Square1 View Post
    Wrapping MAX around ID caused it not to display.
    Did you also change the code that displays the information since you're no longer SELECT'ing the EventsID column?

    Quote Originally Posted by Square1 View Post
    When I add MAX $rowPromoter["EventsID"] does not appear.
    ... which should be expected. After all, you're not SELECT'ing EventsID, so why should $rowPromoter['EventsID'] exist? You need to change the associative array index to match what you're actually SELECT'ing (which can be shortened a bit if you use an alias, e.g. 'SELECT MAX(colName) MaxCol').

  2. #17
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,740
    Quote Originally Posted by Square1 View Post
    Tried MAX() without success. Wrapping MAX around ID caused it not to display. Here is some test code:

    PHP Code:
                                <?php
                                    $queryPromoter 
    "
                                        SELECT
                                            promoter, MAX(EventsID) 
                                        FROM
                                            Events
                                        WHERE
                                            listingDate = CURDATE() AND
                                            LENGTH(tip) > '0'
                                        GROUP BY
                                            promoter    
                                        ORDER BY
                                            EventsID DESC
                                        "
    ;
                        
                                    
    $resultPromoter mysqli_query($link$queryPromoter);
                
                                    echo 
    "<select name='promoter' size='1'>";
                                        while(
    $rowPromoter mysqli_fetch_array($resultPromoterMYSQLI_ASSOC)){
                                            echo 
    "<option>".htmlentities($rowPromoter["promoter"])." | ".htmlentities($rowPromoter["EventsID"])."</option>";
                                        }
                                    echo 
    "</select>";
                
                                    
    mysqli_free_result($resultPromoter);
                                
    ?>
    $rowPromoter["promoter"] appears in the drop down. When I add MAX $rowPromoter["EventsID"] does not appear.
    Use an alias like MAX(EventsID) as EventsID and it will work again, otherwise the column name ends up being MAX(EventsID) such that $rowPromoter["MAX(EventsID)"] would be how you access it without the alias.

    Edit: Didn't see page two - this is what brad said.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  3. #18
    Senior Member
    Join Date
    Mar 2010
    Posts
    327
    MAX(EventsID) as EventsID solved it. Thank you!

    As for SHOW CREATE TABLES (SHOW CREATE TABLE tbl_name) how do I get it to display? print_r?

  4. #19
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,619
    It's the same as issuing any other query. You fetch the data from a result set resource into a variable. The type and contents of that variable will vary depending on what you select and how you fetch it from the result set.

    One thing that never does change though, is var_dump: It will always tell you the type and contents of any variable. Thus it can be used even if you don't know what that type is and so you can find out on your own instead of asking without trying.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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