Converting Pagination Procedural Style Code To PREP STMT

sunny_pro · Feb 24, 2018

Folks,

Do have a look at my pagination code using procedural style that is working fine:

PHP:

<?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 $user ?>Notices in <?php $server_time ?> time.</title>
	</head> 
	<body> 
	<br> 
	<center><span style="font-weight: bold;"><?php $user ?>Notices in <?php $server_time ?> time.</span></center> 
	<br> 
	<br> 
	
<?php 
	if (!$conn)
{
    $error = mysqli_connect_error();
    $errno = mysqli_connect_errno();
    print "$errno: $error\n";
    exit();
}

	$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) {?> 
	<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_username">To</td> 
	<td bgcolor="#FFFFFF" name="column-heading_gender">From</td> 
	<td bgcolor="#FFFFFF" name="column-heading_age-range">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="username"><?php echo $row['recipient_username']; ?></td> 
	<td bgcolor="#FFFFFF" name="gender"><?php echo $row['sender_username']; ?></td> 
	<td bgcolor="#FFFFFF" name="age-range"><?php echo $row['message']; ?></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> 
	<center><span style="font-weight: bold;"><?php $user ?>Notices in <?php $server_time ?> time.</span></center> 
	<br> 
	<br> 
</div> 
<br> 
</body> 
</html> 
<?php 
}
?>

Now, to prevent sql injection, need to use PREP STMT. I need your help.
I got upto this far where I get an error:

Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now in C:\xampp\htdocs\test\notices.php:86 Stack trace: #0 C:\xampp\htdocs\test\notices.php(86): mysqli_query(Object(mysqli), 'SELECT id,date_...') #1 {main} thrown in C:\xampp\htdocs\test\notices.php on line 86

Line 86 looks like this:

PHP:

$result = mysqli_query($conn,$query);

I have put a comment on line 86 on the full code below ....
Here is the full code of my attempt. Do check it out on your wampp/xampp and see what I should change line 86 to:

PHP:

<?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 $user ?>Notices in <?php $server_time ?> time.</title>
	</head> 
	<body> 
	<br> 
	<center><span style="font-weight: bold;"><?php $user ?>Notices in <?php $server_time ?> time.</span></center> 
	<br> 
	<br> 
	
<?php 
	if (!$conn)
{
    $error = mysqli_connect_error();
    $errno = mysqli_connect_errno();
    print "$errno: $error\n";
    exit();
}

$query = "SELECT id,date_and_time,recipient_username,sender_username,message FROM notices WHERE recipient_username = ?";

if ($stmt = mysqli_prepare($conn, $query)) {

    /* bind param */
    mysqli_stmt_bind_param($stmt,'s',$recipient_username);
	
	/* execute statement */
    mysqli_stmt_execute($stmt);	

    /* bind result variables */
    $result = mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$message);

	$rows_num = mysqli_stmt_num_rows($stmt);
    
	//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); //THIS IS LINE 86 WHERE THE ERROR OCCURS. WHAT TO CHANGE THIS LINE TO ?
    ?> 
	<table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
	<?php if($rows_num) {?> 
	<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_username">To</td> 
	<td bgcolor="#FFFFFF" name="column-heading_gender">From</td> 
	<td bgcolor="#FFFFFF" name="column-heading_age-range">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="username"><?php echo $row['recipient_username']; ?></td> 
	<td bgcolor="#FFFFFF" name="gender"><?php echo $row['sender_username']; ?></td> 
	<td bgcolor="#FFFFFF" name="age-range"><?php echo $row['message']; ?></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> 
	<center><span style="font-weight: bold;"><?php $user ?>Notices in <?php $server_time ?> time.</span></center> 
	<br> 
	<br> 
</div> 
<br> 
</body> 
</html> 
<?php 
    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($conn);

}
?>

Rob Whisonant · Feb 25, 2018

You can prevent sql injections using a different method. You don't have to use PREP STMT syntax. Test the variables being sent to make sure they are what you would expect. For example, if you are expecting an integer value, test it to make sure it is an integer within the range expected before passing it to mysql. Same with strings etc.

sunny_pro · Mar 22, 2018

Why COUNT(*) Failing ?

Folks,

I managed to get the pagination working but why oh why on earth is not my count(*) working ?
Note the comments for my problem.

PHP:

<?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 	
	
        //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 = 2; 
	$max_result = 100; 
	$offset = ($page_number-1)*$links_per_page;
	//$offset = ($page_number*$links_per_page)-$links_per_page;
	$query_1 = "SELECT COUNT(*) FROM notices WHERE recipient_username = ? AND sender_username = ?";
    $stmt_1 = mysqli_prepare($conn, $query_1); 	
	mysqli_stmt_bind_param($stmt_1,'ss',$recipient_username,$sender_username);      
    mysqli_stmt_execute($stmt_1);  
	$result_1 = mysqli_stmt_bind_result($stmt_1,$matching_rows_count); 
	mysqli_stmt_free_result($stmt_1); 
	
	printf(" %d rows found.\n",$matching_rows_count); ?><br><?php //Why $matching_rows_count showing "0" when there are "6" ?
	//$total_pages = ceil($matching_rows_count/$links_per_page); Matching rows showing as "0" but exists "6". And so not using this line in the code but the next line.
	$total_pages = ceil($max_result/$links_per_page); 
	
	$query_2 = "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? 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);	
	
	echo "Links Per Page: $links_per_page<br>"; 
    echo "Total Pages results are spread-over: $total_pages"; ?><br><?php 
	echo "Offset: $offset"; ?><br><?php 	
    echo "id: $id";?><br><?php 
	echo "date & time: $date_and_time";?><br><?php 
	echo "recipient username: $recipient_username";?><br><?php 
	echo "sender_username: $sender_username";?><br><?php   
     
    ?> 
    <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) 			
        {  
		?>  
		
		<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>      
        <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 
		//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  
        ?> 
         
        <?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_2);  

//Close Database Connection  
mysqli_stmt_close($stmt_2);  
}  

?>

Why is not my $matching_rows_count variable not holding the value "6" and is holding "0" since there are 6 matching rows ?
Any code sample to sort this issue out is welcome. I tried my best for about a week to solve this issue. But no luck.

Thanks!

Converting Pagination Procedural Style Code To PREP STMT

sunny_pro

New member

Rob Whisonant

Moderator

sunny_pro

New member

Latest posts New threads

Latest posts

New threads

Referral contests

Refer Your Friends to WebmasterSun to Win Cash and Prizes!

Latest Offers New Reviews

Latest affiliate promotion

Popular Contributors - Past 30 Days

Sponsors

Popular tags