Jump to content

Pagination With Prepared Statement A Failure!


phpsane

Recommended Posts

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.