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

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0

    Question Why $offset Removes Pagination ?


    Php Buddies,

    I have a query.

    Do have a glance over the script ...
    You will see it shows pagination. But guess what ? Just remove the comment from these 2 lines and the pagination disappears! Why is that ?
    Line 139:
    //GLOBAL $offset; $offset = $offset+$links_per_page;

    Line 145:
    //GLOBAL $offset; $offset = $offset-$links_per_page;

    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    
        $links_per_page 
    2;
        
    //$query = "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND id = ? LIMIT $links_per_page"; 
        
    $query "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? "

        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
            }
            
            
    $offset $_GET["offset"]; 
            if (
    $offset == ""
            {    
                GLOBAL 
    $offset$offset 30
            }
        
        
    /* bind param */ 
       //mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$offset); 
        
    mysqli_stmt_bind_param($stmt,'s',$recipient_username); 
         
        
    /* execute statement */ 
        
    mysqli_stmt_execute($stmt);     

        
    mysqli_stmt_store_result($stmt); 
        
    $rows_num mysqli_stmt_num_rows($stmt); 
        
    printf(" %d rows found.\n",$rows_num); 
        
        
    $links_per_page 2;
        
    $total_pages ceil($rows_num/$links_per_page); 
            
        echo 
    "Total Pages results spreadover: $total_pages<br>";
        echo 
    "Total Rows Found: $rows_num<br>";
        echo 
    "Links Per Page: $links_per_page<br>";
        
        
    //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 
            
    }
            
    ?>
            
            <?php 
                ?>
                <tr name="pagination"> 
                <td colspan="10" bgcolor="#FFFFFF"> Result Pages: 
                <?php 
                
                $rows_num 
    mysqli_stmt_num_rows($stmt); 
                echo 
    "Offset: $offset";
                if(
    $page_number $total_pages
                { 
                    for(
    $i=1;$i<=$total_pages;$i++) //Show Page Numbers in Serial Order. Eg. 1,2,3.
                    //GLOBAL $offset; $offset = $offset+$links_per_page;
                    
    echo "<a href=\"{$_SERVER['PHP_SELF']}?page_number={$i}&user=$user&offset={$offset}\">{$i}</a>  ";
                } 
                else 
                { 
                    for(
    $i=$total_pages;$i>=1;$i--) //Show Page Numbers in Reverse Order. Eg. 3,2,1.
                    //GLOBAL $offset; $offset = $offset-$links_per_page;
                    
    echo "<a href=\"{$_SERVER['PHP_SELF']}?page_number={$i}&user=$user&offset={$offset}\">{$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); 

    }
    ?>
    Ignore the OTHER commented-out parts of the script. I just left them so you get an idea how I'm experimenting.
    Ignore the outdated html for now. Will fix them later.
    This issue is a great mystery. Been going round in circles for over an hour or so!
    Ignore the extra GLOBALs. Was experimenting. Still am.
    Last edited by UniqueIdeaMan; March 12th, 2018 at 05:07 PM.
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,439
    Rep Power
    2105
    There's no reason whatsoever to declare $offset as global.You check for a value in $_GET value, but if it's not there use 0 as an offset as that's what it should be.

    And why are you using $_GET['page_number'] and $_GET['offset']??? If you know the page number, calculate the offset from that. You'll save variables and confusion for yourself.

    Then there's your query... You don't actually use the value for $offset anywhere, so how would your query actually have the pagination work? You've been told how to do this many times in other threads so you should have that all set now. There's no excuse to have it not set.

    You're also using the wrong query for getting the total number of records. Why return all of that data when you just need to use COUNT() in your query???

    You're doing this all just SO wrong, and again, you have been told the right ways to do this before, but you're just ignoring all of the advice that you're given here.

    And...

    Your login check will fail. You have to set header() before you output any content, so calling that after your calls to include header.php will end in errors and no redirections. Also, why would you wait 2 seconds? Just redirect them straight away.
  4. #3
  5. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,956
    Rep Power
    4575
    lol... What are even doing, man? No ****ing clue anymore...

    Can you even diagram out what you want to happen? A little pseudo-code or a flowchart? Forget PHP for a bit and go back to the basics.

    You can't just copy and paste bits of **** and expect it to work.
    Last edited by Sepodati; March 12th, 2018 at 10:33 PM.
    -- Cigars, whiskey and wild, wild women. --
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,956
    Rep Power
    4575
    Just so we're crystal clear, there are a dozen plus people on here that could hammer out this code in 20 minutes. But I hope like hell no one is going to do that for you.
    -- Cigars, whiskey and wild, wild women. --
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Catacaustic
    There's no reason whatsoever to declare $offset as global.You check for a value in $_GET value, but if it's not there use 0 as an offset as that's what it should be.
    I usually start programming 3pm - 5:30pm and then 7/8p - 11pm. But lastnight, started 4pm-5:30pm and then 9pm - 5am (odd hrs) and I guess the wrong timing being up all night wasn't making me think clearly. Can't remember having sleep in my eyes and so I guess my brain or intellect was asleep at the time. Lol! Went to bed really pissed at 6am over this offset issue.

    Originally Posted by Catacaustic
    And why are you using $_GET['page_number'] and $_GET['offset']??? If you know the page number, calculate the offset from that. You'll save variables and confusion for yourself.
    Tried to be unique. Uniquely simplify another's code but I guess I uniquely complicated it.

    Originally Posted by Catacaustic
    Then there's your query... You don't actually use the value for $offset anywhere, so how would your query actually have the pagination work? You've been told how to do this many times in other threads so you should have that all set now. There's no excuse to have it not set.
    Well, I did try this at first:
    PHP Code:
    $query "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND id = ? LIMIT $links_per_page"
    "id" was related to the offset. But, when that did not work for reasons I soon spotted, I switched to the query you see. Since my intellect is wide awake now I can clearly see your point as daylight. Bad thing is, it was really sunny the last few days even yesterday but today cloudy and windy and it's clouding my mood.

    Originally Posted by Catacaustic
    You're also using the wrong query for getting the total number of records. Why return all of that data when you just need to use COUNT() in your query???
    I thought this was doing the counting:
    PHP Code:
    $rows_num mysqli_stmt_num_rows($stmt); 
    So, what is the difference between that and the COUNT function ? Might aswell google it. But, from experience if you know something then better to say so so newbies can learn from your past experiences.
    difference between mysqli_stmt_num_rows and COUNT

    Originally Posted by Catacaustic
    You're doing this all just SO wrong, and again, you have been told the right ways to do this before, but you're just ignoring all of the advice that you're given here.
    Good! Now, guys like you can point-out to me how wrong and that would help me alert myself to my mistakes.

    Originally Posted by Catacaustic
    Your login check will fail. You have to set header() before you output any content, so calling that after your calls to include header.php will end in errors and no redirections. Also, why would you wait 2 seconds? Just redirect them straight away.
    Don't worry about that. See those include files ? Well, they reference to more include files and those files got what you expect me to have.
    How I test if it is working or not ? I head-over to the home.php?user=catacaustic. If I am logged in (session=user is present) then I remain on the page. Else, I get redirected to the login.php.
    One day, on another thread, I'll reveal the "membership" feature of the script and then you can see things for yourself and bring any errors to my attention. As of now, this feature is working. Don't worry but thanks for the concern.
    In the past, have revealed the code here and other forums and got help from everyone. Kicken here and others elsewhere. The session thing is complete. Struggling a little with the cookies to get it to call the data onto the login web form (Remember Me feature). I'll open another thread on that one day. Very soon. And pester you and the others to help out. If none do then I'll harass Sepodati till he vomits blood! Lol!

    Anyway, googling for the difference between the mysqli_stmt_num_rows and the COUNT() function.
    Anything else I should look into ?
    Gonna look again into the mysqli_stmt_bind_result from other tutorials apart from the manual. Manual too brief and have not been a real help on this one.
    Oh, I have a feeling my problems are related to the offset. Got to check if there is an offset() reserved function which is conflicting with my code. That's the first thing that came to my head when I woke-up today.

    EDIT:
    And thanks for your feed-back. You went quiet all of a sudden. I'm glad you came out of your slumber (ignoring me).
    You came to the conclusion that I was bluffing about my web proxy project. But now look, I built an Android app revolving around it. Still on the look-out for the proper webhost. Out of about 15 only 2-3 allowed it. 2 said I'd be responsoble for my users actions (liability) and one said they'll just temorarily put a hold on my site (not their exact words). Might look into them over the others.

    Yes, I compiled the Android app (web proxy) that you were waiting 6 mnths to see. You were waiting to see a web app and now to your surprise you're now hearing about an Android app that is a web proxy but that is not my big unique marvellous secret idea. Unique idea is not revolving around a web proxy Android app but a web proxy that earns it's users money to help them out of poverty. Anyway, I am getting somewhere even though it took me 13 months to get where I am now. Because my site is not fully ready yet I did not publicly release it. The app has some bookmarked links. These links will point to my site. The money making features are not finished yet and won't finish unless I get this irritating pagination (query for results with PREP STMT using procedural style mysqli) out of the way.
    Very tired of procedural style and mysqli. Had enough of the BS (as DSismad or whatever his Username is) put it. Once this pagination is out of the way, get to get on the pdo bandwagon asap. I have a feeling I'll get more response from you guys if i get stuck on pdo. After-all, everyone wants me to start on pdo and I now get the feeling no one wants to look back at an old timer mysqli as it's been long swept under the carpet by you guys. I can now understand the reluctance from your ends to help me finish this pagination using mysqli.
    Last edited by UniqueIdeaMan; March 13th, 2018 at 06:21 AM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    @Catacaustic,

    Look what I found to learn the difference between mysqli_stmt_num_rows and COUNT().
    This part made me laugh:


    Use COUNT, internally the server will process the request differently.

    When doing COUNT, the server will only allocate memory to store the result of the count.

    When using mysql_num_rows, the server will process the entire result set, allocate memory for all those results, and put the server in fetching mode, which involves a lot of different details, such as locking.

    Think of it like the following pseudo scenarios:

    SELECT COUNT(*)

    Hey Bob, how many people are in the class room?

    mysql_num_rows

    Hey Bob, send all the people from the classroom over to me, ... I'll count them to get the number of people myself

    In summary, when using mysql_num_rows you are transferring all records to the client, and the client will have to calculate the count itself.

    https://stackoverflow.com/questions/...mysql-num-rows

    Now, I learnt the difference between the 2. I got to rid my code of the mysqli_stmt_num_rows for COUNT. Dsismad or gw1500se did suggest I use the COUNT but the code I found had the mysqli_stmt_num_rows and so I stuck to that.
    It's a good thing I showed you guys my work. Because, when everytime I do that, all my weaknesses in php prgramming get revealed at you good guys' hands.
    Good thing you responded to my thread. I am happy and most of all grateful. Recently, to DSismad, gw1500se and even the little crazy Sepodati and ofcourse to you.
    Keep up the good work and always check-out my threads and codes out of curiosity how well (or bad) I'm fairing with php.

    Cheers!

    EDIT: I see them mentioning caching in StckOverFlow. Must make use of that for speeding the results.
    Last edited by UniqueIdeaMan; March 13th, 2018 at 09:16 AM.
  12. #7
  13. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,956
    Rep Power
    4575
    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

    EDIT: You "love" it so much, but didn't attempt any answer. But for those following along that can actually learn... the problem is defining who "#30" is, right? What is that based on? Start at the first row and going left to right? Or right to left? Or is it by last name? Height? Age? ... If you want to do pagination, you need to have consistent ordering so when you ask for the next group, the database knows which one is #30. That means you need a consistent ORDER BY clause in the queries.

    Comments on this post

    • UniqueIdeaMan agrees : I LOVE it when SENIORS give me RIDDLES and then CLUES along with it to solve the PUZZLE!
    Last edited by Sepodati; March 15th, 2018 at 11:06 AM.
    -- Cigars, whiskey and wild, wild women. --
  14. #8
  15. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,439
    Rep Power
    2105
    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.
  16. #9
  17. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,956
    Rep Power
    4575
    Dude... don't give him the ****ing answer. wth....
    -- Cigars, whiskey and wild, wild women. --
  18. #10
  19. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,439
    Rep Power
    2105
    Don't worry. What I've given is a start, but you know as well as I do that you that there's a lot more to it then what I've said, and you can't copy-and-paste that into any queries anywhere. It sill needs work and understanding to accomplish it.
  20. #11
  21. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,956
    Rep Power
    4575
    Yeah, I guess. :/
    -- Cigars, whiskey and wild, wild women. --
  22. #12
  23. 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.
    I did experiment 2 nights ago with the ORDER BY ID, ORDER BY ID ASC, ORDER BY ID DESC. Experimented again las night but no luck as I changed my code a little last night from 2 nights ago and maybe they are conflicting with the mysqli query.
    Anyway, I'll paste my most recent updates in the upcoming posts.
    This is the page I opened in my browser tab along with a lot of other readings but haven't quite read them yet:
    https://www.w3schools.com/php/php_my...lect_limit.asp

    2 nights ago, I came across someone trying to do similar on StackOverFlow or some site and someone suggested to QUERY using BY ID. Then, I tried with that. Even last night but no luck. I'm doing something SO WRONG that I can't see. I have a gut feeling the mysqli_stmt_bind_result is interfering some-how. Will have to look more into this.
    Anyway, I'll experiment a little now and then paste my code so you guys can see how far I have come.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    845
    Rep Power
    0
    Originally Posted by Catacaustic
    Don't worry. What I've given is a start, but you know as well as I do that you that there's a lot more to it then what I've said, and you can't copy-and-paste that into any queries anywhere. It sill needs work and understanding to accomplish it.
    I know what you mean. Today, when I woke-up, I realized the ORDER BY ID ASC won't work unless I put some more code to do the filtering.

    For example, consider the mysql tbl looks like this (I highlighted the matching rows for your convenience):

    Tbl: Notices

    Column Names:

    id | $date_and_time | recipient_username| sender_username | notice
    -----------------------------------------------------------------------------------------------------------------------------------
    0 | 15-03-2018 | catacaustic | admin | Veify ui man
    1 | 15-03-2018 | catacaustic | mod | Veify sepodati
    2 | 15-03-2018 | ui man | mod | Veify gw1500se
    3 | 15-03-2018 | sepodati | gw1500se | Veify dbismad
    4 | 15-03-2018 | catacaustic | admin | Veify Jesus
    5 | 15-03-2018 | catacaustic | admin | Veify Florence Nightingale


    Now imagine, you want to pull all records where the recipient is "catacaustic", sender is "admin".
    And you want 2 records per page.

    How many pages the records are spreadover is dependant on how many matching rows get pulled.
    So from this example, there are 6 records with 3 matching rows.
    The 1st page should show 2 records like this:

    Page 1:
    id | $date_and_time | recipient_username| sender_username | notice
    -----------------------------------------------------------------------------------------------------------------------------------
    0 | 15-03-2018 | catacaustic | admin | Veify ui man
    4 | 15-03-2018 | catacaustic | admin | Veify Jesus

    Go To Page: 2.


    So, on page 1, the 1st record is offset: 0. And 2nd record is offset: 1.

    And the 2nd page should show 1 remaining matching record like this:

    Page 2
    id | $date_and_time | recipient_username| sender_username | notice
    -----------------------------------------------------------------------------------------------------------------------------------
    5 | 15-03-2018 | catacaustic | admin | Veify Florence Nightingale


    Go to Page 1.


    And here, the 1st record is offset: 2.

    Note that, the final page no longer shows pagination in serial order like this:

    Pages 123456.

    Instead, it should show in reverse order like this:

    Pages: 654321.


    In the last 2 nights been experimenting in a variety of ways. Sometimes the paginations are displayed as I want (final page shows page numbers in reverse order else serial order) but all pages show the same records. Failed to get the offset thing running properly.
    And sometimes no records get shown.

    Tested with OR, AND, ORDER BY ID ASC, ORDER BY ID DESC, etc. but no luck.
    Here's my latest code. Don't know why no records are being shown:
    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";
        
    $links_per_page 2
        
    $query "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? AND id = ? ORDER BY id LIMIT $links_per_page";  
        
    //$query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? ";  
        
        
    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,'ssi',$recipient_username,$sender_username,$offset);  
        
    //mysqli_stmt_bind_param($stmt,'s',$recipient_username);  
          
        /* execute statement */  
        
    mysqli_stmt_execute($stmt);      

        
    /*
        Use COUNT() over mysqli_stmt_num_rows as COUNT() just counts the number of records rather than fetching all the records, 
        saving them and then counting them.
        mysqli_stmt_store_result($stmt);  
        $rows_num = mysqli_stmt_num_rows($stmt); 
        */    
         
        
    $links_per_page 2
        
    $total_pages ceil(COUNT('id')/$links_per_page);  
        
    $rows_num COUNT('id');
        
    $offset $page_number*$links_per_page-$links_per_page//Initial Offset: 0.
        
        
    printf(" %d rows found.\n",COUNT('id')); 
        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  
            

            
    ?> 
             
            <?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);  
    }  

    ?>
    And so far, I have taken your advice from yesterday.

    One other thing, notice:
    $query = "SELECT COUNT('id') AS RowCount,

    Now, if I want to display the count then how can i do it using the reference "RowCount" ?
    No matter what I try, I get error undefined constant RowCount!
    Googled yesterday for examples but no real luck in finding a simple code example.

    EDIT:
    Editing after making the next post to Sepodati to show update on my progress.
    These queries aren't working:
    $query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? AND id = ? ORDER BY id LIMIT $links_per_page OFFSET 2";
    $query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? AND id = ? ORDER BY id LIMIT $links_per_page OFFSET 1";
    $query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? AND id = ? ORDER BY id LIMIT $links_per_page,2";
    Was no good:
    https://www.petefreitag.com/item/451.cfm
    https://www.techonthenet.com/sql/select_limit.php

    Going all haywire now!
    Last edited by UniqueIdeaMan; March 14th, 2018 at 06:16 AM.
  26. #14
  27. 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
    2 nights ago, I did come across 2 params in the LIMIT where one was the starting point. Or, should we call it offset ?
    I think the 2nd param was the offset:
    LIMIT 30,10

    So now, you want me to write code that will count the offsets that have already been displayed in the previous page and then start the offset for the current page from the offset count.
    Example, if each page shows 5 records and I'm on page 3 then the offset should start from 10.
    So, on the 1st query I query like this:
    LIMIT 5,0.
    (Limit 5 links and start from offset 0).

    Then on the 2nd query, I limit like this:
    LIMIT 5,2.
    (Limit 5 links and start from offset 0).

    My subconscious is telling me I should keep the offset count. create a variable for that. But my subconscious is also telling me I got the params wrong.
    meaning, the 2nd param is not the offset.
    Let me google what the 2nd param means.
    Tell me atleast that, I'm getting on track on how to count the offset for each page, even though I associated the offset with the wrong param.

    You've been some help. It's your 2nd/3rd good start with me within 2wks.

    Cheers!

    EDIT:
    Googled: "ORDER BY ID LIMIT 1,2".
    Was hoping a link would explain the 2nd param.
    Found:
    SELECT * FROM * WHERE * ORDER BY id DESC LIMIT * (Heh)
    No good. But, look at the date! Lol! I don't see those Usernames roaming this forum any more!
    Anyway, now at:
    https://www.petefreitag.com/item/451.cfm
    Again, look at the date! Lol!
    Last edited by UniqueIdeaMan; March 14th, 2018 at 06:05 AM.
  28. #15
  29. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,956
    Rep Power
    4575
    Originally Posted by Catacaustic
    It sill needs work and understanding to accomplish it.
    You're right. Both of which are seriously lacking.
    -- Cigars, whiskey and wild, wild women. --
Page 1 of 4 123 ... Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo