Converting Pagination Procedural Style Code To PREP STMT

sunny_pro

New member
Joined
Jun 18, 2017
Messages
86
Points
0
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

Moderator
Joined
May 24, 2016
Messages
2,489
Points
113
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

New member
Joined
Jun 18, 2017
Messages
86
Points
0
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!
 
Latest threads
Replies
2
Views
383
Replies
1
Views
366
Replies
4
Views
1,888
Replies
3
Views
794
Recommended threads
Replies
2
Views
1,403
Replies
4
Views
4,307
Replies
2
Views
2,286
Replies
4
Views
3,746

Referral contests

Referral link for :

Sponsors

Popular tags

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top