Display Mysql Tbl Results With Pagination

sunny_pro

New member
Joined
Jun 18, 2017
Messages
86
Points
0
Php Folks,

I need to do things in procedural style using mysqli. Have not learnt oop style or pdo yet.

I am trying to create a pagination page where 10 pages display all records. Records spreadover 10 pages.
Even though my tbl has records, I get notice that no records exist. No records are being displayed in the html table.
Why is that ?
Following is my attempt to show records using PREP STMT procedural style using mysqli.

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 
{ 
	//Grab User details from Session Variables and echo them. 
	$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"]; 
	$date_of_birth = $_SESSION["date_of_birth"]; 
	$age_range = $_SESSION["age_range"]; 
	$religion = $_SESSION["religion"]; 
	$marital_status = $_SESSION["marital_status"]; 
	$working_status = $_SESSION["working_status"]; 
	$profession = $_SESSION["profession"]; 
	$home_town = $_SESSION["home_town"]; 
	$home_borough = $_SESSION["home_borough"]; 
	$home_city = $_SESSION["home_city"]; 
	$home_county = $_SESSION["home_county"]; 
	$home_region = $_SESSION["home_region"]; 
	$home_state = $_SESSION["home_state"]; 
	$home_country = $_SESSION["home_country"]; 
	
	$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 "$site_name $user"; ?> User's Notices in <?php echo $server_time; ?> time.</title> 
	</head> 
	<body> 
	<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> 
	<?php 
	$query = "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ?"; 
    
	if ($stmt = mysqli_prepare($conn,$query)) 
	{     
		//Bind Parameter 
		mysqli_stmt_bind_param($stmt,'s',$recipient_username); 	
		//Execute Statement 
		mysqli_stmt_execute($stmt);
		//$result_1 = mysqli_query($conn,$query); 
		//Bind Result Variables     
		$result_1 = mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice); 
		mysqli_stmt_store_result($stmt); 
		$rows_num = mysqli_stmt_num_rows($stmt); 	
		printf("Result set has %d rows.\n",$rows_num); 
		
		$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}"; 		
		
	?> 	
	<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 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> 	
	<?php while($row = mysqli_stmt_fetch($stmt)) 
	{ 
		?> 
		<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 
	$rows_num = mysqli_stmt_num_rows($stmt); 
	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 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); 

//Close Database Connection 
mysqli_stmt_close($stmt); 
}
?>
I get echoed:
**Result set has 0 rows.
No record found! Try another time.**

This is false. There are records in the mysql tbl.

I'd appreciate it if someone can show a code sample by editing my code. Or, if you think there is a simpler way of doing things then be kind enough to show a code sample with comments and I will try learning from it.
Other future newbies to this thread will also appreciate your hard contribution.

Thank You!
 

sunny_pro

New member
Joined
Jun 18, 2017
Messages
86
Points
0
Thanks but I prefer to use php since I am learning it. Javascript, I don't like and seem to complicated to me.
 

Marc van Leeuwen

Premium Member
Joined
May 29, 2016
Messages
1,113
Points
63
Why you used this command mysqli_stmt_free_result($stmt);

while you can easily count records in your table with this command
Code:
SELECT COUNT(*) FROM yourTable;
?
 
Newer threads
Latest threads
Replies
1
Views
80
Replies
1
Views
175
Replies
4
Views
383
Replies
11
Views
524
Replies
2
Views
228
Recommended threads
Replies
1
Views
2,704
Replies
16
Views
5,963
Replies
0
Views
2,606

Latest postsNew threads

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