/    Sign up×
Community /Pin to ProfileBookmark

Pagination With Prepared Statement A Failure!

Copy linkTweet thisAlerts:
Nov 20.2018

Php Experts,

Do you mind helping me out here ? Been swimming in the mess for many months and then I gave up for half a yr and now want to look back into it.

I got a non-prep stmt pagination code. Need to convert it to prep stmt. I tried for many months but failed. Look here:

http://forums.devshed.com/php-development-5/converting-pagination-procedural-style-code-prep-stmt-980129.html

And came across many obstacles such as:

http://forums.devshed.com/php-development-5/mixed-pagination-mystery-980539.html

And others which I have now lost track off!

I’m not in Devshed anymore. Don’t know why password fails over there but anyway I am now over here and that is that and so let us move-on and get on with it.

Let me start all over again with the code afresh here in this community.

Help me convert this into prep stmt:

[code]
 <?php 
//Required PHP Files. 
include ‘config.php’; 
include ‘header.php’; 
include ‘account_header.php’; 
?> 
<!DOCTYPE html>
<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> 
<?php 
if (!$conn) 
{ 
    $error = mysqli_connect_error(); 
    $errno = mysqli_connect_errno(); 
    print “$errno: $errorn”; 
    exit(); 
} 
else 
{ 
    $query = “SELECT * FROM notices”; 
    $result = mysqli_query($conn,$query); 
    $rows_num = mysqli_num_rows($result); 
    
    //Total Number of Pages records are 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; 
        
        $query .= ” limit {$offset},{$page_size}”; 
        $result = mysqli_query($conn,$query); 
    ?> 
    <table width=”1500″ border=”0″ cellpadding=”5″ cellspacing=”2″ bgcolor=”#666666″> 
    <?php 
    if($rows_num) 
    { 
        printf(“<b> %d Result Found …</b>n”,$rows_num); ?><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-&-time”>Date & Time in <?php $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> 
        <?php while($row = mysqli_fetch_array($result)) 
        { 
            ?> 
            <tr name=”user-details”> 
            <td bgcolor=”#FFFFFF” name=”submission-number”><?php echo $row[‘id’]; ?></td> 
            <td bgcolor=”#FFFFFF” name=”logging-server-date-&-time”><?php echo $row[‘date_and_time’]; ?></td> 
            <td bgcolor=”#FFFFFF” name=”recipient-username”><?php echo $row[‘recipient_username’]; ?></td> 
            <td bgcolor=”#FFFFFF” name=”sender-username”><?php echo $row[‘sender_username’]; ?></td> 
            <td bgcolor=”#FFFFFF” name=”notice”><?php echo $row[‘notice’]; ?></td> 
            </tr> 
            <?php 
        } 
        ?> 
        <tr name=”pagination”> 
        <td colspan=”10″ bgcolor=”#FFFFFF”> Result Pages: 
        <?php 
        if($rows_num <= $page_size) 
        { 
            echo “Page 1”; 
        } 
        else 
        { 
            for($i=1;$i<=$page_count;$i++) 
            echo “<a href=”{$_SERVER[‘PHP_SELF’]}?page_number={$i}”>{$i}</a> “; 
        } 
        ?> 
        </td> 
        </tr> 
        <?php 
    } 
    else 
    { 
        ?> 
        <tr> 
        <td bgcolor=”#FFFFFF”>No record found! Try another time.</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> 
    <br> 
    </div> 
    <br> 
    </body> 
    </html>

Best I tried in 6mnths or so was this and I can’t seem to get the pagination part done right:

<?php 
//Required PHP Files. 
include ‘config.php’; //Required on all webpages of the site. 
include ‘sessions.php’; //Required on all webpages inside the account. 
include ‘settings.php’; //Required on all webpages inside the account. 
include ‘account_header.php’; //Required on all webpages inside the account. 
if (!$conn) 
{ 
    $error = mysqli_connect_error(); 
    $errno = mysqli_connect_errno(); 
    print “$errno: $errorn”; 
    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 = 1; 
    $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 WHERE recipient_username = ? AND sender_username = ?”; 
    $query_1 = “SELECT COUNT(*) FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT ? OFFSET ?”; 
    $stmt_1 = mysqli_prepare($conn,$query_1); 
    //mysqli_stmt_bind_param($stmt_1,’ss’,$recipient_username,$sender_username); 
    mysqli_stmt_bind_param($stmt_1,’ssii’,$recipient_username,$sender_username,$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 WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT ? OFFSET ?”; 
    $stmt_2 = mysqli_prepare($conn,$query_2); 
    mysqli_stmt_bind_param($stmt_2,’ssii’,$recipient_username,$sender_username,$links_per_page,$offset); 
    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> 
    <meta name=”viewport” content=”width=device-width, initial-scale=1″> 
    </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> “; 
                ?><br>
                <?php echo “$total_pages”; //DELETE
            }             
            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> “; 
                ?><br>
                <?php echo “$total_pages”; //DELETE
            } 
            ?> 
            </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>
[/code]

Let me have a look how you do your prep stmt pagination. Yes ?

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@rootNov 20.2018 — > @site-developer#1598084 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional/EN">

its [code=html]<!DOCTYPE html>[/code]
As for pagination, you would be best off using session variables to pass the current page to the next page and the number of results to display per page so that the new page can be generated.
Copy linkTweet thisAlerts:
@site-developerauthorNov 25.2018 — @root#1598095

Best you show me an example code on how to do all the offsettings via the session.

It's the offsetting parts I keep messing up. For mnths now! ?
Copy linkTweet thisAlerts:
@rootNov 25.2018 — SELECT * FROM table_name WHERE whatever=something ORDERBY chosen_field LIMIT offset, count;

So, if you have 20 items per page, you have a known page number like 5.

SO, offset = itemsPerPage * pageNumber;

The offset would be, 20 * 5 = 100.

SELECT * FROM table_name WHERE whatever=something ORDERBY chosen_field LIMIT offset, count;
Copy linkTweet thisAlerts:
@VelazquezNov 25.2018 — Let me start all over again
×

Success!

Help @site-developer spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.4,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...