Jump to content

Pagination With Prepared Statement A Failure!

phpsane

By phpsane
in PHP Coding Help

 Share

Recommended Posts

phpsane Advanced Member

phpsane

Posted
Posted (edited)

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: 

	<?php 
	//Required PHP Files. 
include 'config.php'; 
include 'header.php'; 
include 'account_header.php'; 
	?> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional/EN"> 
<html> 
<head> 
<meta content="text/html; charset=ISO-8859-1" http-equiv=" content-type"> 
<title><?php echo "$site_name User $user Notices in $server_time time."; ?></title> 
</head> 
<body> 
<br> 
<p align="center"><span style="font-weight:bold;"><?php echo "$site_name User $user Notices in $server_time time."; ?></span></align> 
<br> 
<br> 
<?php 
if (!$conn) 
{ 
    $error = mysqli_connect_error(); 
    $errno = mysqli_connect_errno(); 
    print "$errno: $error\n"; 
    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: $error\n"; 
    exit(); 
} 
else 
{     
    //Get the Page Number. Default is 1 (First Page). 
    $page_number = $_GET["page_number"]; 
    if ($page_number == "") 
    { 
        $page_number = 1; 
    } 
	    $sender_username = $social_network_admin_username; 
    $recipient_username = $user; 
    $links_per_page = 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>

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

EDIT: I fixed the: 

	<!DOCTYPE html> 

So don't bother mentioning it.

Edited by phpsane
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.