#16
  1. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,959
    Rep Power
    4575
    Originally Posted by Barand
    I think you both need to RTFM regarding the use of null values in queries. Whe checking for null values the syntax is "... IS NULL" or "... IS NOT NULL"

    https://dev.mysql.com/doc/refman/5.7...with-null.html
    Thank you for the correction.
    -- Cigars, whiskey and wild, wild women. --
  2. #17
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,959
    Rep Power
    4575
    nm
    -- Cigars, whiskey and wild, wild women. --
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by SimonJM
    Barand has addressed this already, via the id column, but I'd add to the mix the following question: "what defines a row as being 'last'?"
    Last one for each particular Username. I already defined it and therefore Baradd understood my question.
    I am now testing his mysql query using php PREP STMT.

    Thanks for the interest in my question.
  6. #19
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,959
    Rep Power
    4575
    Q: What defines a row as being 'last'?

    A: Last one for each particular Username

    Pure genius at work here, folks.
    -- Cigars, whiskey and wild, wild women. --
  8. #20
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    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 |
    +----+----------+-----------------------+
    Ha! ha! Seeing your tbl name made me laugh out of my chair!
    Anyway, I have a long list of columns but you can say the major columns are:
    id
    username
    browsed_page

    And, I edited your code (cut & paste) into mysql and it worked like a charm:
    SELECT id, username, browsed_page FROM browsing_histories JOIN (SELECT username, MAX(id) as id FROM browsing_histories GROUP BY username) mx USING (username, id);

    Now, as you know, I don't just cut & paste without understanding the code. And, others here suspect that I just cut & paste and never really learn the code lines. Got to prove them wrong. Especially, Sepodati & Gw1500se.
    Therefore:

    1. Care to explain your code ?
    2. Care to show a php equivalent so I can use php to do the same job ? I need it in mysqli procedural style using PREP STMT ?

    I actually did not understand from this "max(id)" point onwards:
    SELECT id, username, browsed_page FROM browsing_histories JOIN (SELECT username, MAX(id) as id FROM browsing_histories GROUP BY username) mx USING (username, id);

    What is "max" here for ? You mean the row with the "max" int in the id column for each username ?
    What is "mx" ? Mix ? Exchange ? Those don't seem right.

    I am checking:
    https://stackoverflow.com/questions/...rder-by-number

    But, I prefer you give me the answers rather than I search hither & thither all over the net for the answers.
    if you answer my Q2, then that should speak volumes in terms of a good, short & concise explanation.

    Thanks!
    Last edited by UniqueIdeaMan; March 22nd, 2018 at 01:40 PM.
  10. #21
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    I gave you an explanation:

    Originally Posted by Barand
    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
    What is "max" here for ?
    See the explanation

    What is "mx" ?
    It's the mandatory alias for the table subquery

    Why would you use a prepared statement for a querythat has no parameters?
    PHP Code:
    $result mysqli_query($conn$sql
  12. #22
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Sepodati
    nm
    Have you now started talking to yourself ? What is "nm" ?
    "No mention!" ?
    How about "dm" for "don't mention it" ?
    Last edited by UniqueIdeaMan; March 26th, 2018 at 06:21 AM.
  14. #23
  15. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Barand
    Why would you use a prepared statement for a querythat has no parameters?
    PHP Code:
    $result mysqli_query($conn$sql
    I was sort of asleep when I did that line.
    It was after midnight.
    Last edited by UniqueIdeaMan; March 26th, 2018 at 06:31 AM.
  16. #24
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    Originally Posted by UniqueIdeaMan
    First show me, how would you get php to do all that:
    SELECT id, username, browsed_page FROM browsing_histories JOIN (SELECT username, MAX(id) as id FROM browsing_histories GROUP BY username) mx USING (username, id);
    I just did - read last line of my previous post.
  18. #25
  19. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,959
    Rep Power
    4575
    Originally Posted by Barand
    I just did - read last line of my previous post.
    Welcome to the ****show, man.
    -- Cigars, whiskey and wild, wild women. --
  20. #26
  21. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Barand
    I just did - read last line of my previous post.
    Swell! You're right. There is no "WHERE" and so there can be no parameters in your query:
    $query = "SELECT id, username, browsed_page FROM browsing_histories JOIN (SELECT username, MAX(id) as id FROM browsing_histories GROUP BY username) mx USING (username, id)";


    Ok. You got me. You knew what you was talking about. You won, ofcourse!

    Now, do you mind editing this rough code. I sure messed it up. It's 3am here and even though my eyes are not sleepy, I guess my intellect is. But, I'm staying awake till 5am or sunrise. So, stick around for another 2hrs.
    Thanks. I appreciate you gluing to me tonight! I mean it! We'll learn more if you stick around this forum often.
    Anyway, here's my rough attempt:
    PHP Code:
    <?php   

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

    //Check if User is already logged-in or not. Get the login_check() FUNCTION to check.   
    if (login_check() === FALSE)   
    {  
        
    //Redirect User to Log-in Page after 2 secs.   
        
    header("refresh:2; url=login.php");   
        exit();   
    }   
    else   
    {   
        
    $user $_SESSION["user"];   
          
        
    $id $_SESSION["id"];   
        
    $account_activation_status $_SESSION["account_activation_status"];   
        
    $id_video_verification_status $_SESSION["id_video_verification_status"];   
        
    $id_video_verification_url $_SESSION["id_video_verification_url"];   
        
    $sponsor_username $_SESSION["sponsor_username"];   
        
    $recruits_number $_SESSION["recruits_number"];   
        
    $on_day_number_on_7_days_wish_list $_SESSION["on_day_number_on_7_days_wish_list"];   
        
    $primary_website_domain $_SESSION["primary_website_domain"];   
        
    $primary_website_email $_SESSION["primary_website_email"];   
        
    $username $_SESSION["username"];   
        
    $first_name $_SESSION["first_name"];   
        
    $middle_name $_SESSION["middle_name"];   
        
    $surname $_SESSION["surname"];   
        
    $gender $_SESSION["gender"];   
        
    $age_range $_SESSION["age_range"];   
        
    $religion $_SESSION["religion"];   
        
    $marital_status $_SESSION["marital_status"];   
        
    $working_status $_SESSION["working_status"];   
        
    $profession $_SESSION["profession"];   
          
        
    $recipient_username $user;  

        
    ?>   
        <!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 "$user "?>Notices in <?php echo "$server_time ";?> time.</title> 
        </head>   
        <body>   
        <br>   
        <center><span style="font-weight: bold;"><?php echo "$user ";?>Notices in <?php echo "$server_time ";?> time.</span></center>   
        <br>   
        <br>   
          
        <?php     
        
        $sender_username 
    "admin123";
        
    $recipient_username "admin123";
        
    $result_limit 1
        
    $total_pages ceil($max_result/$links_per_page); 

        
    //FORMULA: SELECT id, sender_username, recipient_username FROM notices JOIN (SELECT sender_username, MAX(id) as id FROM notices GROUP BY sender_username) mx USING (sender_username, id);
        
    $query "SELECT id, username, browsed_page FROM browsing_histories JOIN (SELECT username, MAX(id) as id FROM browsing_histories GROUP BY username) mx USING (username, id)";
        
    $stmt mysqli_prepare($conn$query);    
        
    mysqli_stmt_bind_param($stmt,'ssi',$recipient_username,$sender_username,$result_limit);      
        
    mysqli_stmt_execute($stmt);  
        
    $result mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice);
        
    mysqli_stmt_fetch($stmt);    
        
        echo 
    "id: $id";?><br><?php 
        
    echo "date & time: $date_and_time";?><br><?php 
        
    echo "recipient username: $recipient_username";?><br><?php 
        
    echo "sender_username: $sender_username";?><br><?php   
         
        ?>
     
        <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
        
        <?php if(!$stmt)  
        {  
            
    ?>  
            <tr>  
            <td bgcolor="FFFFFF">No record found! Try another time.</td>  
            </tr>  
            <?php  
        

        else 
        { 
            
    ?>
            <tr name="headings">  
            <td bgcolor="#FFFFFF" name="column-heading_submission-number">Submission Number</td>  
            <td bgcolor="#FFFFFF" name="column-heading_logging-server-date-&-time">Date & Time in <?php echo $server_time ?></td>  
            <td bgcolor="#FFFFFF" name="column-heading_to">To</td>  
            <td bgcolor="#FFFFFF" name="column-heading_from">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="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 
            mysqli_stmt_fetch
    ($stmt)
            
                
    ?>  
                <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  
            
    //} 
            
    ?> 
             
            <?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>  
        <center><span style="font-weight: bold;"><?php echo "$site_name $user "?>User's Notices in <?php echo "$server_time "?> time.</span></center> 
        <br>  
        <br>  
    </div>  
    <br>  
    </body>  
    </html>  
    <?php  

    //Free Result Set  
    mysqli_stmt_free_result($stmt);  

    //Close Database Connection  
    mysqli_stmt_close($stmt);  
     

    ?>
    Now, I did say "rough". Care to edit it and smoothen it up a bit with comments so we can learn from your sample code ?
    I need the code to show pagination.
    Page 1234 and so on.
    And ofcourse, each page shows only the final row of each username based on my post 13:
    MySql, Php & Prep Stmt
    And, based your post 14:
    MySql, Php & Prep Stmt

    Thanks Barand!

    And sorry for mispelling your Username earlier many times to Barrad!
  22. #27
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Sepodati
    Welcome to the ****show, man.
    Ha! Ha! Ha! So, it's a show now, is it ? Ha! Ha! Ha!
    It would be if you were awake till 3am, like me!

    Originally Posted by Sepodati
    Q: What defines a row as being 'last'?

    A: Last one for each particular Username

    Pure genius at work here, folks.
    What ? I was talking about my own definition so you know what I am trying to do. Nothing genius about that be it raw/fake/original or pure.
    Last edited by UniqueIdeaMan; March 26th, 2018 at 06:23 AM.
  24. #28
  25. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Folks,

    You know, this line is doing all the replacing:
    PHP Code:
    if($response['http_code'] == '200' )
    {   
        
    $regex '/\b';
        
    $regex .= implode('\b|\b'$banned_words);
        
    $regex .= '\b/i';
        
    $substitute '****';
        
    $cleanresult preg_replace($regex$substitute$result);
        echo 
    $cleanresult;

    I am stuck on how to change this part to achieve what I want mentioned in my original post about not getting the banned words substituted but getting the script to find one banned word and give alert to user that the page contains so banned word and then halt the script (kill it!).
    What lines of codes must I use to substitute the above mentioned code ?
    Any ideas, anyone ?

    Thanks

    @barand:

    What do you think of it now ?
    http://forums.devshed.com/php-develo...ml#post2984312
    Last edited by UniqueIdeaMan; March 26th, 2018 at 06:25 AM.
  26. #29
  27. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Bump!
    Last edited by UniqueIdeaMan; March 26th, 2018 at 06:22 AM.
  28. #30
  29. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,959
    Rep Power
    4575
    -- Cigars, whiskey and wild, wild women. --

IMN logo majestic logo threadwatch logo seochat tools logo