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

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,950
    Rep Power
    4554
    At least you're bringing life to this dead forum, even if you are a waste of ****ing time.

    Have you ever logged directly into MySQL and ran queries to try and solve your problems? You'd at least learn (maybe) SQL without having all of your PHP errors and ****ed up logic in the way.

    You completely missed the point of my analogy, even though you were given the answer. Sad.
    -- Cigars, whiskey and wild, wild women. --
  2. #17
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Sepodati
    At least you're bringing life to this dead forum, even if you are a waste of ****ing time.

    Have you ever logged directly into MySQL and ran queries to try and solve your problems? You'd at least learn (maybe) SQL without having all of your PHP errors and ****ed up logic in the way.

    You completely missed the point of my analogy, even though you were given the answer. Sad.
    I'm going back to you and Catacaustic's replies to see what exactly I missed.
  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 Catacaustic
    And the next thing to do is add ORDER BY statements to your query.

    You probably haven't been told this before, but MySQL doesn't have a "standard" ordering system unless you tell it how you want it to order. That's extremely important in your case because unless you set the order you can't guarantee that what's on page 1 will always be on page one, or that something on page one won't show up on page two.
    Tonight, I concentrate on:

    And the next thing to do is add ORDER BY statements to your query.

    The offset answer I reckon is in ORDER BY Statements. But, how to ORDER by Statements ? No column name is called "Statements" and Mysqli it seems can only order by one of the columns.
    That's my problem.
  6. #19
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Sepodati
    That's a good analogy and lesson to learn. Good job.

    Let's take that a step further. You're the instructor, Bob, with 1000 students or so. And you get a request such as the following:

    Hey Bob, send me 10 students, starting at number 30.

    That'd equate to using LIMIT 30,10 in your database query, like you've been demonstrating.

    Now, how do you, as the instructor, process that request? What's missing?

    -John
    Mmm. So, the offset is the 1st param then ? Ok, testing it out now. I thought it was the 2nd param all this time due to this:
    https://www.techonthenet.com/sql/select_limit.php
    or, maybe you got your wires crossed too ?
    Last edited by UniqueIdeaMan; March 14th, 2018 at 09:27 AM.
  8. #20
  9. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,950
    Rep Power
    4554
    But, how to ORDER by Statements ? No column name is called "Statements"
    wtf... Do you have trouble finding the "any" key, too?

    As for using an OFFSET with LIMIT, the answer is of course in the manual. This shows there are two ways to do it:

    Code:
    SELECT
        [ALL | DISTINCT | DISTINCTROW ]
          [HIGH_PRIORITY]
          [STRAIGHT_JOIN]
          [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
          [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
        select_expr [, select_expr ...]
        [FROM table_references
          [PARTITION partition_list]
        [WHERE where_condition]
        [GROUP BY {col_name | expr | position}
          [ASC | DESC], ... [WITH ROLLUP]]
        [HAVING where_condition]
        [ORDER BY {col_name | expr | position}
          [ASC | DESC], ...]
        [LIMIT {[offset,] row_count | row_count OFFSET offset}]
        [PROCEDURE procedure_name(argument_list)]
        [INTO OUTFILE 'file_name'
            [CHARACTER SET charset_name]
            export_options
          | INTO DUMPFILE 'file_name'
          | INTO var_name [, var_name]]
        [FOR UPDATE | LOCK IN SHARE MODE]]
    -- Cigars, whiskey and wild, wild women. --
  10. #21
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Sepodati
    At least you're bringing life to this dead forum, even if you are a waste of ****ing time.

    Have you ever logged directly into MySQL and ran queries to try and solve your problems? You'd at least learn (maybe) SQL without having all of your PHP errors and ****ed up logic in the way.

    You completely missed the point of my analogy, even though you were given the answer. Sad.
    You were right. The 1st PARAM is the offset. Thanks for hinting to check my sql logic on mysql. I just did and this is working:

    SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = 'admin123' AND sender_username = 'admin123' ORDER BY id LIMIT 1, 3;

    Mysql showed record starting from the 2nd row (offset 1) and showed 3 records.
    My id starts from row 28 as I deleted the first 28 records.
    Now, when I set the offset to 1, it showed the record with the id=29. Note, I was expecting id=29 record to be pulled if I put offset=29 as I was associating the offset with the id.
    Mm. I've learnt that, with mysql, the offset is not related to the id number and so even though my records start with id-28, to pull the first record, i should not put offset=28 but offset=0.
    But, is this how I should do it with php too ? bear in mind the way I have structured my code before giving an answer. Or, maybe I should re-structure my code if it's illogical ? What should I do ?
  12. #22
  13. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,950
    Rep Power
    4554
    PHP just sends the query to MySQL, so if it's working in MySQL, it'll work in PHP. Nothing changes with the query; you're just using PHP to send it.
    -- Cigars, whiskey and wild, wild women. --
  14. #23
  15. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0

    Why False COUNT('id') ?


    Originally Posted by Sepodati
    PHP just sends the query to MySQL, so if it's working in MySQL, it'll work in PHP. Nothing changes with the query; you're just using PHP to send it.
    Ok Master Sepodati,

    Here is my experiment results with 2 different sql queries:

    1st Query
    PHP Code:
    //Following query manages to show 3 result.
    $query "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3"
    2nd Query

    PHP Code:
    Why following query shows no results when trying to COUNT('id') while the above query manages to show results when no COUNTING is done ?
    $query "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3"

    Why following query shows no results when trying to COUNT('id') while the above query manages to show results when no COUNTING is done ?
    $query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";

    The difference between the 2 queries is highlighted in the 2nd query.

    Full code of the 1st query:
    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 
        
        $offset 
    "28";
        
    $sender_username "admin123";
        
    $links_per_page 2
        
    //Following query manages to show 3 result.
        
    $query "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";  
        
        
        if (
    $stmt mysqli_prepare($conn$query))  
        { 
            
    //Get the Page Number, Default is 1 (First Page).      
            
    $page_number $_GET["page_number"];  
            if (
    $page_number == "")  
            {     
               
    $page_number 1;  
            } 
             
         
        
    /* bind param */  
        
    mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$sender_username);  
         
          
        
    /* execute statement */  
        
    mysqli_stmt_execute($stmt);      

        
    $links_per_page 2
        
    $total_pages ceil(COUNT('id')/$links_per_page);  
        
    $rows_num COUNT('id');
        
        
        
    printf(" %d rows found.\n",COUNT('id')); //Why this echoes 1 row found when actually found 3 ?
        
    echo "Total Rows Found: $rows_num<br>";
        echo 
    "Links Per Page: $links_per_page<br>"
        echo 
    "Total Pages results are spreadover: $total_pages<br>"
        
    //echo "Offset: $offset";    
         
        //Bind Result Variables      
        
    $result mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice); 
        
    ?> 
        <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
        
        <?php if(!$rows_num)  
        {  
            
    ?>  
            <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>      
            <?php while(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  
                $offset
    ++;
                echo 
    "Offset: $offset";
            } 
            
    ?> 
             
            <?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);  
    }  

    ?>
    NOTE: Even though this 1st query manages to display 3 rows, it echoes:
    1 rows found. Total Rows Found: 1

    Why the false echo ?

    Line 84:
    PHP Code:
    printf(" %d rows found.\n",COUNT('id')); //Why this echoes 1 row found when actually found 3 ?
        
    echo "Total Rows Found: $rows_num<br>";
        echo 
    "Links Per Page: $links_per_page<br>"
        echo 
    "Total Pages results are spreadover: $total_pages<br>"
    -----------------------------

    Full code of 2nd query:
    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 
        
        $offset 
    "28";
        
    $sender_username "admin123";
        
    $links_per_page 2
        
    //Why following query shows no results when trying to COUNT('id') while the above query manages to show results when no COUNTING is done ?
        
    $query "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";  
        
        if (
    $stmt mysqli_prepare($conn$query))  
        { 
            
    //Get the Page Number, Default is 1 (First Page).      
            
    $page_number $_GET["page_number"];  
            if (
    $page_number == "")  
            {     
               
    $page_number 1;  
            } 
             
         
        
    /* bind param */  
        
    mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$sender_username);  
         
          
        
    /* execute statement */  
        
    mysqli_stmt_execute($stmt);        
         
        
    $links_per_page 2
        
    $total_pages ceil(COUNT('id')/$links_per_page);  
        
    $rows_num COUNT('id');
        
        
    printf(" %d rows found.\n",COUNT('id')); //Why this echoes 1 row found when actually found 3 ?
        
    echo "Total Rows Found: $rows_num<br>";
        echo 
    "Links Per Page: $links_per_page<br>"
        echo 
    "Total Pages results are spreadover: $total_pages<br>"
        
    //echo "Offset: $offset";    
         
        //Bind Result Variables      
        
    $result mysqli_stmt_bind_result($stmt,$rows_num,$id,$date_and_time,$recipient_username,$sender_username,$notice);  
        
    ?> 
        <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
        
        <?php if(!$rows_num)  
        {  
            
    ?>  
            <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>      
            <?php while(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  
                $offset
    ++;
                echo 
    "Offset: $offset";
            } 
            
    ?> 
             
            <?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);  
    }  

    ?>
    This 2nd query manages to show no records but yet gives false echo like the 1st query:
    1 rows found. Total Rows Found: 1

    Again, why is that ?
    Last edited by UniqueIdeaMan; March 14th, 2018 at 11:05 AM.
  16. #24
  17. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,950
    Rep Power
    4554
    You have no ****ing idea what you're doing. You just copy & paste **** without understanding any of it.

    PHP Code:
        $total_pages ceil(COUNT('id')/$links_per_page);   
        
    $rows_num COUNT('id'); 
        
    printf(" %d rows found.\n",COUNT('id')); 
    lol... really?
    -- Cigars, whiskey and wild, wild women. --
  18. #25
  19. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,950
    Rep Power
    4554
    lol... this is the PHP function you're running, count(), which has NOTHING TO ****ING do with anything you're trying to do... heh...

    PHP: count - Manual
    -- 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 Sepodati
    You have no ****ing idea what you're doing. You just copy & paste **** without understanding any of it.

    PHP Code:
        $total_pages ceil(COUNT('id')/$links_per_page);   
        
    $rows_num COUNT('id'); 
        
    printf(" %d rows found.\n",COUNT('id')); 
    lol... really?
    Apart from the final line out of the 3, they are mine. No copy & paste.
    Here is the actual copy & paste which I modified and simplified:
    PHP Code:
    //Get Data from Table "browsing_histories". 
        
    $sql "SELECT * FROM browsing_histories"
        
    $result mysqli_query($conn,$sql); 
        
    //Total Number of Records 
        
    $rows_num mysqli_num_rows($result); 
        
    //Total Number of Pages Records to spread-over. 
        
    $page_count 10
        
    $page_size ceil($rows_num $page_count); 
        
    //Get the Page Number, Default is 1 (First Page). 
        
    $page_number $_GET["page_number"]; 
        if (
    $page_number == ""$page_number 1
            
    $offset = ($page_number -1) * $page_size
            
            
    $sql .= " limit {$offset},{$page_size}"
            
    $result mysqli_query($conn,$sql); 
    Anyway, what is wrong with this code of mine ?
    PHP Code:
    $total_pages ceil(COUNT('id')/$links_per_page);   
        
    $rows_num COUNT('id'); 
        
    printf(" %d rows found.\n",COUNT('id')); 
    I used the COUNT() instead of the mysqli_stmt_num_rows. What is wrong with that ?

    EDIT: Imagine there are 10 records/rows pulled and I set php to display 3 records/rows per page.
    That would make 3.33 pages. We can't be having that and so we want it round up to 4 pages. Hence, using the "ceil()". Now, what is wrong with that ?

    Here, I wanted php to count how many records/rows it fetched and so got the COUNT() to do it and print the fetched number on screen. What is wrong with that ?
    printf(" %d rows found.\n",COUNT('id'));

    Sorry mate but I failed to understand your hints on this one and so care to be more specific which line you're refering to ?
    Last edited by UniqueIdeaMan; March 14th, 2018 at 11:50 AM.
  22. #27
  23. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,271
    Rep Power
    4193
    I used the COUNT() instead of the mysqli_stmt_num_rows
    You're using the wrong count. You need to use the MySQL function COUNT, not PHP's count function.

    This means issuing two separate queries. The first one gets your total records count. The second gets the record data for the page you are showing. So you code would look something like
    Code:
    $sql = 'SELECT COUNT(*) FROM blah WHERE ...';
    $totalRecords = runQuery($sql);
    
    $offset = ($page-1)*$recordsPerPage;
    $sql = 'SELECT your,column,list FROM blah WHERE ... LIMIT offset,recordsPerPage';
    $rowData = runQuery($sql);
    Now, translate that pseudo-code into real code that actually works.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  24. #28
  25. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by kicken
    You're using the wrong count. You need to use the MySQL function COUNT, not PHP's count function.

    This means issuing two separate queries. The first one gets your total records count. The second gets the record data for the page you are showing. So you code would look something like
    Code:
    $sql = 'SELECT COUNT(*) FROM blah WHERE ...';
    $totalRecords = runQuery($sql);
    
    $offset = ($page-1)*$recordsPerPage;
    $sql = 'SELECT your,column,list FROM blah WHERE ... LIMIT offset,recordsPerPage';
    $rowData = runQuery($sql);
    Now, translate that pseudo-code into real code that actually works.
    Thanks Kicken.
    But, why do you have 2 $sql ? Translating your pseudo-code I should have 2 too ?
    So, one query to get hold of the records number or row count and the other to get hold of all the matching records data ?

    So far, I've translated it like this but not sure if I got the 1st $query right.
    And, what does your $rowData represent ? Row match count ? I'm using PREP STMT.
    If I'm on right track then I'm experimenting with your pseudo-code now.

    EDIT:
    Kicken, I translated your pseudo-code like this in terms of PREP STMT procedural style using mysqli.
    Let me know where I went wrong:

    PHP Code:
    <?php     
        
            
    //Get the Page Number, Default is 1 (First Page).      
            
    $page_number $_GET["page_number"];  
            if (
    $page_number == "")  
            {     
               
    $page_number 1;  
            }         

        
    $sender_username "admin123";
        
    $recipient_username "admin123";
            
    $links_per_page 5
        
        
    $offset "($page_number-1)*$links_per_page";
        
    $query "SELECT COUNT(*) id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";  
        
    $stmt mysqli_prepare($conn$query);     
        
    mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$sender_username);      
        
    mysqli_stmt_execute($stmt);  
        
    $result mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice); 
        
    $rows_num $rows_num COUNT('id');
        
        
    $query "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? LIMIT $offset$links_per_page"
        
    $stmt mysqli_prepare($conn$query);    
        
    mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$sender_username);      
        
    mysqli_stmt_execute($stmt);  
        
    $result mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice); 
    ?>
    Last edited by UniqueIdeaMan; March 14th, 2018 at 12:53 PM.
  26. #29
  27. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,950
    Rep Power
    4554
    Do you see a LIMIT or any other columns after COUNT in the pseudo code, for the first query?
    Last edited by Sepodati; March 14th, 2018 at 01:18 PM.
    -- Cigars, whiskey and wild, wild women. --
  28. #30
  29. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Sepodati
    Do you see a LIMIT or any other columns after COUNT in the pseudo code?
    No, I don't. I see this:
    PHP Code:
    $sql 'SELECT COUNT(*) FROM blah WHERE ...'
    I see the WHERE and so thought I should add the remaining part of the query line including the LIMITs.
    So, you are saying I should not add the limits ?
    Do you mind completing his pseudo-code query lines ? That would be easier for me to solve the puzzle.
    Here are the 2 incomplete queries for his pseudo-code:
    PHP Code:
    $sql 'SELECT COUNT(*) FROM blah WHERE ...';

    $sql 'SELECT your,column,list FROM blah WHERE ... LIMIT offset,recordsPerPage'
    And, I need to know the equivalent lines of these 2 in terms of PREP STMTs:
    $totalRecords = runQuery($sql);
    $rowData = runQuery($sql);


    I translated them like this:
    Version 1:
    $rows_num = COUNT('id');
    $result_1 = mysqli_stmt_bind_result($stmt_1,$id,$date_and_time,$recipient_username,$sender_username,$notice);


    Version 2:
    $rows_num = COUNT('id');
    $query_1 = "SELECT COUNT(*) id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ?


    Is my translation right ? If not then which line not and on which version ?
    Last edited by UniqueIdeaMan; March 14th, 2018 at 01:24 PM.

IMN logo majestic logo threadwatch logo seochat tools logo