Why $offset Removes Pagination ?

sunny_pro

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

I have a query.

Do have a glance over the script ...
You will see it shows pagination. But guess what ? Just remove the comment from these 2 lines and the pagination disappears! Why is that ?
Line 139:
//GLOBAL $offset; $offset = $offset+$links_per_page;

Line 145:
//GLOBAL $offset; $offset = $offset-$links_per_page;

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    
	$links_per_page = 2;
    //$query = "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND id = ? LIMIT $links_per_page"; 
	$query = "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? "; 

    if ($stmt = mysqli_prepare($conn, $query)) 
    {
		//Get the Page Number, Default is 1 (First Page).     
        $page_number = $_GET["page_number"]; 
        if ($page_number == "") 
        {    
            $page_number = 1; 
        }
		
		$offset = $_GET["offset"]; 
        if ($offset == "") 
        {    
            GLOBAL $offset; $offset = 30; 
        }
    
    /* bind param */ 
   //mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$offset); 
	mysqli_stmt_bind_param($stmt,'s',$recipient_username); 
     
    /* execute statement */ 
    mysqli_stmt_execute($stmt);     

    mysqli_stmt_store_result($stmt); 
    $rows_num = mysqli_stmt_num_rows($stmt); 
    printf(" %d rows found.\n",$rows_num); 
    
    $links_per_page = 2;
    $total_pages = ceil($rows_num/$links_per_page); 
        
	echo "Total Pages results spreadover: $total_pages<br>";
	echo "Total Rows Found: $rows_num<br>";
	echo "Links Per Page: $links_per_page<br>";
	
	//Bind Result Variables     
    $result = mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice); 
	
    ?>
    <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
    <?php if(!$rows_num) 
    { 
        ?> 
        <tr> 
        <td bgcolor="FFFFFF">No record found! Try another time.</td> 
        </tr> 
        <?php 
    }
    else
    {
        ?> 
        <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(mysqli_stmt_fetch($stmt)) 
        { 
            ?> 
            <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 
            
            $rows_num = mysqli_stmt_num_rows($stmt); 
            echo "Offset: $offset";
            if($page_number < $total_pages) 
			{ 
				for($i=1;$i<=$total_pages;$i++) //Show Page Numbers in Serial Order. Eg. 1,2,3.
				//GLOBAL $offset; $offset = $offset+$links_per_page;
                echo "<a href=\"{$_SERVER['PHP_SELF']}?page_number={$i}&user=$user&offset={$offset}\">{$i}</a>  ";
            } 
            else 
            { 
				for($i=$total_pages;$i>=1;$i--) //Show Page Numbers in Reverse Order. Eg. 3,2,1.
				//GLOBAL $offset; $offset = $offset-$links_per_page;
                echo "<a href=\"{$_SERVER['PHP_SELF']}?page_number={$i}&user=$user&offset={$offset}\">{$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); 

//Close Database Connection 
mysqli_stmt_close($stmt); 
} 
}
?>
Ignore the OTHER commented-out parts of the script. I just left them so you get an idea how I'm experimenting.
Ignore the outdated html for now. Will fix them later.
Ignore the extra GLOBALs. Was experimenting. Still am.
This issue is a great mystery. Been going round in circles for over an hour or so! :confused::eek::(:mad:
 

sunny_pro

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

Here is my experiment results with 2 different sql queries:

1st Query
PHP:
//Following query manages to show 3 result.
$query = "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";
2nd Query

PHP:
Why following query shows no results when trying to COUNT('id') while the above query manages to show results when no COUNTING is done ?
$query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";

Why following query shows no results when trying to COUNT('id') while the above query manages to show results when no COUNTING is done ?
$query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";

The difference between the 2 queries is highlighted in the 2nd query.

Full code of the 1st query:
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 
	
	$offset = "28";
	$sender_username = "admin123";
    $links_per_page = 2; 
	//Following query manages to show 3 result.
	$query = "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";  
    
	
    if ($stmt = mysqli_prepare($conn, $query))  
    { 
        //Get the Page Number, Default is 1 (First Page).      
        $page_number = $_GET["page_number"];  
        if ($page_number == "")  
        {     
           $page_number = 1;  
        } 
         
     
    /* bind param */  
    mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$sender_username);  
     
      
    /* execute statement */  
    mysqli_stmt_execute($stmt);      

	$links_per_page = 2; 
    $total_pages = ceil(COUNT('id')/$links_per_page);  
	$rows_num = COUNT('id');
	
	
	printf(" %d rows found.\n",COUNT('id')); //Why this echoes 1 row found when actually found 3 ?
	echo "Total Rows Found: $rows_num<br>";
	echo "Links Per Page: $links_per_page<br>"; 
    echo "Total Pages results are spreadover: $total_pages<br>"; 
	//echo "Offset: $offset";    
     
    //Bind Result Variables      
    $result = mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice); 
    ?> 
    <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
	
    <?php if(!$rows_num)  
    {  
        ?>  
        <tr>  
        <td bgcolor="FFFFFF">No record found! Try another time.</td>  
        </tr>  
        <?php  
    } 
    else 
    { 
        ?>  
        <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(mysqli_stmt_fetch($stmt))  
        {  
            ?>  
            <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  
			$offset++;
			echo "Offset: $offset";
        } 
        ?> 
         
        <?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);  

//Close Database Connection  
mysqli_stmt_close($stmt);  
}  
} 
?>
NOTE: Even though this 1st query manages to display 3 rows, it echoes:
1 rows found. Total Rows Found: 1

Why the false echo ?

Line 84:
PHP:
printf(" %d rows found.\n",COUNT('id')); //Why this echoes 1 row found when actually found 3 ?
	echo "Total Rows Found: $rows_num<br>";
	echo "Links Per Page: $links_per_page<br>"; 
    echo "Total Pages results are spreadover: $total_pages<br>";
-----------------------------

Full code of 2nd query:
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 
	
	$offset = "28";
	$sender_username = "admin123";
    $links_per_page = 2; 
	//Why following query shows no results when trying to COUNT('id') while the above query manages to show results when no COUNTING is done ?
	$query = "SELECT COUNT('id') AS RowCount,id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id LIMIT 1, 3";  
    
    if ($stmt = mysqli_prepare($conn, $query))  
    { 
        //Get the Page Number, Default is 1 (First Page).      
        $page_number = $_GET["page_number"];  
        if ($page_number == "")  
        {     
           $page_number = 1;  
        } 
         
     
    /* bind param */  
    mysqli_stmt_bind_param($stmt,'ss',$recipient_username,$sender_username);  
     
      
    /* execute statement */  
    mysqli_stmt_execute($stmt);        
     
    $links_per_page = 2; 
    $total_pages = ceil(COUNT('id')/$links_per_page);  
	$rows_num = COUNT('id');
	
	printf(" %d rows found.\n",COUNT('id')); //Why this echoes 1 row found when actually found 3 ?
	echo "Total Rows Found: $rows_num<br>";
	echo "Links Per Page: $links_per_page<br>"; 
    echo "Total Pages results are spreadover: $total_pages<br>"; 
	//echo "Offset: $offset";    
     
    //Bind Result Variables      
    $result = mysqli_stmt_bind_result($stmt,$rows_num,$id,$date_and_time,$recipient_username,$sender_username,$notice);  
    ?> 
    <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
	
    <?php if(!$rows_num)  
    {  
        ?>  
        <tr>  
        <td bgcolor="FFFFFF">No record found! Try another time.</td>  
        </tr>  
        <?php  
    } 
    else 
    { 
        ?>  
        <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(mysqli_stmt_fetch($stmt))  
        {  
            ?>  
            <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  
			$offset++;
			echo "Offset: $offset";
        } 
        ?> 
         
        <?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);  

//Close Database Connection  
mysqli_stmt_close($stmt);  
}  
} 
?>
This 2nd query manages to show no records but yet gives false echo like the 1st query:
1 rows found. Total Rows Found: 1

Again, why is that ?
 
Newer threads
Replies
3
Views
2,214
Replies
1
Views
2,877
Replies
5
Views
2,259
Replies
2
Views
4,403
Latest threads
Replies
2
Views
467
Replies
0
Views
637
Replies
4
Views
1,757
Replies
2
Views
762
Recommended threads

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