Page 3 of 3 First 123
  • Jump to page:
    #31
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    757
    Rep Power
    0
    Originally Posted by Sepodati
    Are you sure I should be using the preg_match here ?
  2. #32
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,933
    Rep Power
    4554
    Originally Posted by UniqueIdeaMan
    Are you sure I should be using the preg_match here ?
    I'm not sure you even know how to spell it, yet alone use it in code.
    -- Cigars, whiskey and wild, wild women. --
  4. #33
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    757
    Rep Power
    0
    Originally Posted by Sepodati
    I'm not sure you even know how to spell it, yet alone use it in code.
    What do you mean spell it ? I spelt it correct.
    Nevertheless, I am glad you are still holding onto me. I would like to make you change your opinions about me.
    Which of my following .exe softwares do you want ? Check this link:
    http://forums.devshed.com/outhouse-1...ml#post2984640
  6. #34
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    757
    Rep Power
    0
    Mmm.
  8. #35
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    757
    Rep Power
    0
    Originally Posted by Barand
    SQL is quite capable of doing it. You would use a table subquery to find the max id value for each user then use a join to find the records in your table matching those users/ids
    Code:
    mysql> select * from uniqueidiot;
    +----+----------+-----------------------+
    | id | username | url                   |
    +----+----------+-----------------------+
    |  1 | Sepodati | 301 Moved             |
    |  2 | UI Man   | http://yahoo.com      |
    |  3 | gw1500se | mamma.com             |
    |  4 | Sepodati | http://dogpile.com    |
    |  5 | Baradd   | http://dogpile.com    |
    |  6 | Baradd   | 301 Moved Permanently |
    +----+----------+-----------------------+
    6 rows in set (0.00 sec)
    
    mysql> SELECT id
        ->      , username
        ->      , url
        -> FROM uniqueidiot
        ->      JOIN (
        ->         SELECT username
        ->              , MAX(id) as id
        ->         FROM uniqueidiot
        ->         GROUP BY username
        ->      ) mx USING (username, id);
    +----+----------+-----------------------+
    | id | username | url                   |
    +----+----------+-----------------------+
    |  2 | UI Man   | http://yahoo.com      |
    |  3 | gw1500se | mamma.com             |
    |  4 | Sepodati | http://dogpile.com    |
    |  6 | Baradd   | 301 Moved Permanently |
    +----+----------+-----------------------+
    Barand,

    Why isn't the following working ?

    Tbl: notice
    Columns: id, date_and_time,recipient_username,sender_username,notice.

    I want the latest row pulled from each Username. Like mentioned on post 14 of Barand's grouping query:
    MySql, Php & Prep Stmt

    Where did I go wrong ?
    PHP Code:
    $query_2 "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices JOIN (SELECT date_and_time,recipient_username,sender_username,MAX(id) as id FROM notices GROUP by recipient_username) mx USING (date_and_time,recipient_username,sender_username,id)"
    I am not getting any errors but not seeing results like I should.
    PHP Code:
    <?php 

    //Required PHP Files. 
    include 'config.php'
    include 
    'header.php'
    include 
    'account_header.php'

    if (!
    $conn

        
    $error mysqli_connect_error(); 
        
    $errno mysqli_connect_errno(); 
        print 
    "$errno$error\n"
        exit(); 

    else 
    {     
        
    //Get the Page Number. Default is 1 (First Page). 
        
    $page_number $_GET["page_number"]; 
        if (
    $page_number == ""
        { 
            
    $page_number 1
        } 

        
    $sender_username "$social_network_admin_username"
        
    $recipient_username "$user"
        
    $links_per_page 2
        
    $max_result 100
        
    //$offset = ($page_number*$links_per_page)-$links_per_page; 
        
    $offset = ($page_number-1)*$links_per_page
        
        
    $query_1 "SELECT COUNT(*) FROM notices ORDER BY id LIMIT ? OFFSET ?"
        
    $stmt_1 mysqli_prepare($conn,$query_1);
        
    mysqli_stmt_bind_param($stmt_1,'ss',$links_per_page,$offset); 
        
    mysqli_stmt_execute($stmt_1); 
        
    $result_1 mysqli_stmt_bind_result($stmt_1,$matching_rows_count); 
        
    mysqli_stmt_fetch($stmt_1); 
        
    mysqli_stmt_free_result($stmt_1); 
        
        
    $total_pages ceil($matching_rows_count/$links_per_page); 
        
    $query_2 "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices JOIN (SELECT date_and_time,recipient_username,sender_username,MAX(id) as id FROM notices GROUP by recipient_username) mx USING (date_and_time,recipient_username,sender_username,id)"
        
    $stmt_2 mysqli_prepare($conn,$query_2); 
        
    mysqli_stmt_execute($stmt_2); 
        
    $result_2 mysqli_stmt_bind_result($stmt_2,$id,$date_and_time,$recipient_username,$sender_username,$notice); 
        
    mysqli_stmt_fetch($stmt_2);     
        
    ?>
        <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional/EN"> 
        <html> 
        <head> 
        <meta content="text/html; charset=ISO-8859-1" http-equiv=" content-type"> 
        <title><?php echo "$site_name User $user Notices in $server_time time."?></title> 
        </head> 
        <body> 
        <br> 
        <p align="center"><span style="font-weight:bold;"><?php echo "$site_name User $user Notices in $server_time time."?></span></align> 
        <br> 
        <br> 
        <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
        <?php if(!$stmt_2
        { 
            
    ?> 
            <tr> 
            <td bgcolor="#FFFFFF">No record found! Try another time.</td> 
            </tr> 
            <?php 
        

        else 
        { 
            if((
    $offset+1)<=$max_result
            { 
                
    printf("<b> %d Result Found ...</b>\n",$matching_rows_count); ?><br> 
                <br> 
                <tr name="headings"> 
                <td bgcolor="#FFFFFF" name="column-heading_submission-number">Submission Number</td> 
                <td bgcolor="#FFFFFF" name="column-heading_logging-server-date-and-time">Date & Time in <?php echo "$server_time?></td> 
                <td bgcolor="#FFFFFF" name="column-heading_recipient-username">To</td> 
                <td bgcolor="#FFFFFF" name="column-heading_sender-username">From</td> 
                <td bgcolor="#FFFFFF" name="column-heading_notice">Notice</td> 
                </tr> 
                <tr name="user-details">             
                <td bgcolor="#FFFFFF" name="submission-number"><?php printf("%s",$id); ?></td> 
                <td bgcolor="#FFFFFF" name="logging-server-date-and-time"><?php printf("%s",$date_and_time); ?></td> 
                <td bgcolor="FFFFFF"  name="column-heading_recipient-username"><?php printf("%s",$recipient_username); ?></td> 
                <td bgcolor="#FFFFFF" name="column-heading_sender-username"><?php printf("%s",$sender_username); ?></td> 
                <td bgcolor="#FFFFFF" name="notice"><?php printf("%s",$notice); ?></td> 
                </tr> 
                <?php 
                
    //Use this technique: http://php.net/manual/en/mysqli-stmt.fetch.php 
                
    while(mysqli_stmt_fetch($stmt_2)) 
                { 
                    
    ?> 
                    <tr name="user-details"> 
                    <td bgcolor="#FFFFFF" name="submission-number"><?php printf("%s",$id); ?></td> 
                    <td bgcolor="#FFFFFF" name="logging-server-date-and-time"><?php printf("%s",$date_and_time); ?></td> 
                    <td bgcolor="#FFFFFF" name="recipient-username"><?php printf("%s",$recipient_username); ?></td> 
                    <td bgcolor="#FFFFFF" name="sender-username"><?php printf("%s",$sender_username); ?></td> 
                    <td bgcolor="#FFFFFF" name="notice"><?php printf("%s",$notice); ?></td> 
                    </tr> 
                    <?php 
                    ?>
     
                    <tr name="pagination"> 
                    <td colspan="10" bgcolor="#FFFFFF"> Result Pages:             
                    <?php              
                    
    if($page_number $total_pages
                    { 
                        for(
    $i=1;$i<=$total_pages;$i++) //Show Page Numbers in Serial Order. Eg. 1,2,3.
                        
    echo "<a href=\"{$_SERVER['PHP_SELF']}?user=$user&page_number={$i}\">{$i}</a> "
                    }             
                    else 
                    { 
                        for(
    $i=$total_pages;$i>=1;$i--) //Show Page Numbers in Reverse Order. Eg. 3,2,1.
                        
    echo "<a href=\"{$_SERVER['PHP_SELF']}?user=$user&page_number={$i}\">{$i}</a> "
                    } 
                    
    ?> 
                    </td> 
                    </tr> 
                    <?php 
                

            } 
        }
        
    ?>   
        </table>     
        <br> 
        <br> 
        <p align="center"><span style="font-weight:bold;"><?php echo "$site_name User $user Notices in $server_time time."?></span></align> 
        <br> 
        </div> 
        <br> 
        </body> 
        </html> 
        <?php 
        
    //Free Result Set. 
        
    mysqli_stmt_free_result($stmt_2); 
        
    //Close Statement Connection. 
        
    mysqli_stmt_close($stmt_2); 
        
    //Close Database Connection. 
        
    mysqli_close($conn); 

    ?>
    This query shows error:
    SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices JOIN (SELECT date_and_time,recipient_username,sender_username,MAX(id) as id FROM notices GROUP by recipient_username) mx USING (recipient_username,id)";

    Error:
    Fatal error: Uncaught mysqli_sql_exception: Column 'date_and_time' in field list is ambiguous in C:\xampp\htdocs\test\search_final_rows_for_each_username.php:41 Stack trace: #0 C:\xampp\htdocs\test\search_final_rows_for_each_username.php(41): mysqli_prepare(Object(mysqli), 'SELECT id,date_...') #1 {main} thrown in C:\xampp\htdocs\test\search_final_rows_for_each_username.php on line 41
    Last edited by UniqueIdeaMan; April 11th, 2018 at 08:51 AM.
  10. #36
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    86
    Rep Power
    72
    You want to find the latest ( MAX(date_and_time) ) record for each recipient username, therefore your subquery would be concerned only with these two columns. You then find the matching notice records by joining to the subquery on these two columns.

    I gave you an example. All that is required is that you give a bit of thought to what you are doing. Try that for a unique idea.
    Code:
    SELECT id
          , n.date_and_time
          , n.recipient_username
          , sender_username
          , notice 
    FROM notices n
    	JOIN (
            SELECT recipient_username
                 , MAX(date_and_time) as date_and_time 
            FROM notices 
            GROUP by recipient_username
            ) mx USING (recipient_username, date_and_time)

    Comments on this post

    • Sepodati agrees : Not holding my breath... ;)
    • UniqueIdeaMan agrees : Thanks! Never would have done it without you!
  12. #37
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    757
    Rep Power
    0
    Originally Posted by Barand
    You want to find the latest ( MAX(date_and_time) ) record for each recipient username, therefore your subquery would be concerned only with these two columns. You then find the matching notice records by joining to the subquery on these two columns.

    I gave you an example. All that is required is that you give a bit of thought to what you are doing. Try that for a unique idea.
    Code:
    SELECT id
          , n.date_and_time
          , n.recipient_username
          , sender_username
          , notice 
    FROM notices n
    	JOIN (
            SELECT recipient_username
                 , MAX(date_and_time) as date_and_time 
            FROM notices 
            GROUP by recipient_username
            ) mx USING (recipient_username, date_and_time)
    Thanks Barand!
    Frankly, all this GROUPING is way out of my league. Never in a million yrs would I have figured-out that "n." is required. Never came across it. What does it mean ? Knowing this might teach me something and solve my confusion.
    @Sepodati, don't hold your breath too long or you'll drown in your own breath! Lol!
    Last edited by UniqueIdeaMan; April 20th, 2018 at 04:11 AM.
  14. #38
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    86
    Rep Power
    72
    "n" is a shorthand alias for "notices" table. I could have left out the n and written notices.recipient_username and notices.date_and_time. Sometimes aliases are essentioal, but in this case, it just saves time.

    As those two column names occur in the notices table and the table subquery, it is necessary to specify which of the two you want, to remove ambiguity.

    GROUP BY X is used to aggregate records to a single row for each value of X. In this case we want a single row for each recipient and the aggregation is the MAX(date_and_time). So for e aech recipient we now know the latest date.

    There are several aggregation functions, but the most commonly used are COUNT(), SUM(), AVG(), MIN() and MAX()

    Example
    Code:
    mysql> SELECT * FROM sample_uim;
    +--------------+----------+----------+
    | sampleUIM_id | username | quantity |
    +--------------+----------+----------+
    |            1 | Peter    |        1 |
    |            2 | Paul     |        2 |
    |            3 | Mary     |        3 |
    |            4 | Peter    |        5 |
    |            5 | Mary     |        6 |
    |            6 | Paul     |        4 |
    |            7 | Peter    |        3 |
    |            8 | Paul     |        5 |
    +--------------+----------+----------+
    8 rows in set (0.00 sec)
    
    mysql> SELECT username
        ->      , COUNT(quantity) as mum_quantities
        ->      , SUM(quantity) as total_quantity
        ->      , AVG(quantity) as average_quantity
        ->      , MIN(quantity) as minimum_quantity
        ->      , MAX(quantity) as maximum_quantity
        -> FROM sample_uim
        -> GROUP BY username;
    +----------+----------------+----------------+------------------+------------------+------------------+
    | username | mum_quantities | total_quantity | average_quantity | minimum_quantity | maximum_quantity |
    +----------+----------------+----------------+------------------+------------------+------------------+
    | Mary     |              2 |              9 |           4.5000 |                3 |                6 |
    | Paul     |              3 |             11 |           3.6667 |                2 |                5 |
    | Peter    |              3 |              9 |           3.0000 |                1 |                5 |
    +----------+----------------+----------------+------------------+------------------+------------------+
    3 rows in set (0.00 sec)

    Comments on this post

    • UniqueIdeaMan agrees : Thanks you again! Will be going through your work very soon!
  16. #39
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    757
    Rep Power
    0
    Originally Posted by Barand
    "n" is a shorthand alias for "notices" table. I could have left out the n and written notices.recipient_username and notices.date_and_time. Sometimes aliases are essentioal, but in this case, it just saves time.

    As those two column names occur in the notices table and the table subquery, it is necessary to specify which of the two you want, to remove ambiguity.

    GROUP BY X is used to aggregate records to a single row for each value of X. In this case we want a single row for each recipient and the aggregation is the MAX(date_and_time). So for e aech recipient we now know the latest date.

    There are several aggregation functions, but the most commonly used are COUNT(), SUM(), AVG(), MIN() and MAX()

    Example
    Code:
    mysql> SELECT * FROM sample_uim;
    +--------------+----------+----------+
    | sampleUIM_id | username | quantity |
    +--------------+----------+----------+
    |            1 | Peter    |        1 |
    |            2 | Paul     |        2 |
    |            3 | Mary     |        3 |
    |            4 | Peter    |        5 |
    |            5 | Mary     |        6 |
    |            6 | Paul     |        4 |
    |            7 | Peter    |        3 |
    |            8 | Paul     |        5 |
    +--------------+----------+----------+
    8 rows in set (0.00 sec)
    
    mysql> SELECT username
        ->      , COUNT(quantity) as mum_quantities
        ->      , SUM(quantity) as total_quantity
        ->      , AVG(quantity) as average_quantity
        ->      , MIN(quantity) as minimum_quantity
        ->      , MAX(quantity) as maximum_quantity
        -> FROM sample_uim
        -> GROUP BY username;
    +----------+----------------+----------------+------------------+------------------+------------------+
    | username | mum_quantities | total_quantity | average_quantity | minimum_quantity | maximum_quantity |
    +----------+----------------+----------------+------------------+------------------+------------------+
    | Mary     |              2 |              9 |           4.5000 |                3 |                6 |
    | Paul     |              3 |             11 |           3.6667 |                2 |                5 |
    | Peter    |              3 |              9 |           3.0000 |                1 |                5 |
    +----------+----------------+----------------+------------------+------------------+------------------+
    3 rows in set (0.00 sec)
    Barand,

    Here's where I am using your query grouping code:
    Mixed Pagination Mystery

    Your mysql query code is working fine. Once again: Thanks!
    But, I encountered another issue (pagination) and so opened that thread about an hr ago.
Page 3 of 3 First 123
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo