Menu
Folks,
I am stuck at the final stage of building a pagination script.
This is how it works ….
When you click SUBMIT button, initially the rows_count() uses following query to get the matching rows number:
[code]
$query_2 = “SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page”;
Then it forwards you to the fetch_rows() that fetches the rows and displays them in a pagination style.
When you click any page numbers on the pagination section, like page 2, then the fetch_rows() is supposed to fetch the relevant rows again for page 2.
Rows fetching is done with query:
[code]
$query_2 = “SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page”;
It displays the matching rows using this repetition:
[code]
while($row = mysqli_fetch_array($result_2,MYSQLI_ASSOC))
Is anything wrong with my loop ?
ISSUE:
The fetch_rows() or $query_2 is seriously failing to fetch any matching rows beyond page 1.
This happens no matter what page you click. Be it page 2, page 2 and so on.
Remember, it does manage to fetch all matching rows for page 1. Just not for any pages beyond page 1. That is the main nightmare for 3 nights now!
Code is configured to display 1 row per page in DEVMODE. Will switch this on PRODUCTION MODE.
Right now, since there are 5 matching rows then the rows are supposed to be spread across many pages via the pagination.
You can easily see which lines I am having trouble with if you notice the CAPITALISED comments.
[code]
//Do following if “Search” button clicked.
if($_SERVER[‘REQUEST_METHOD’] === ‘POST’)
{echo __LINE__; echo “<br>”;//DELETE
//Do following if “Search” button clicked.
if(isset($_POST[‘search’]))
{echo __LINE__; echo “<br>”;//DELETE
rows_count(); //This function will forward script flow to fetch_rows() before halting the script.
die();
}
}
echo __LINE__; echo “<br>”;//On PAGINATION PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 24.
//Do following if “Search” button not clicked but pagination numbered links are clicked. Eg Page 1, 2, 3, etc..
fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ?
echo __LINE__; echo “<br>”;//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 205.
Here is the full code for your convenience. You won’t understand the code without the context.
DEVMODE CONTEXT:
[code]
<?php
error_reporting(E_ALL);
?>
<!DOCTYPE HTML”>
<html>
<head>
<meta name=”viewport” content=”width-device=width, initial-scale=1″>
</head>
<body>
<?php
session_start();
if(!isset($_GET[‘query_type’]) && empty($_GET[‘query_type’]))
{
die(“Invalid Query!”);
}
else
{
$_SESSION[‘query_type’] = $_GET[‘query_type’]; echo __LINE__; echo “<br>”;//DELETE
}
echo __LINE__; echo “<br>”;//DELETE
if(!isset($_GET[‘form_type’]) && empty($_GET[‘form_type’]))
{
die(“Invalid Form!”);
}
else
{
$_SESSION[‘form_type’] = $_GET[‘form_type’]; echo __LINE__; echo “<br>”;//DELETE
if(!function_exists($_SESSION[‘form_type’]))
{
die(“Invalid Form!”);
}
else
{echo __LINE__; echo “<br>”;//DELETE
if(!session_id() || !isset($_SESSION[‘form_step’]) || $_SESSION[‘form_step’] != ‘end’)
{
$_SESSION[‘form_step’] = ‘start’; echo __LINE__; echo “<br>”;//DELETE
$_SESSION[‘form_type’]();
}
}
}
//FUNCTIONS START FROM HERE
function search()
{echo __LINE__; echo “<br>”;//DELETE
function rows_count()
{
//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
$conn = mysqli_connect(“localhost”,”root”,””,”powerpage”);
$conn->set_charset(‘utf8mb4’); //Always set Charset.
if($conn === false)
{
die(“ERROR: Connection Error!. ” . mysqli_connect_error());
}
$query_1 = “SELECT COUNT(id) FROM users WHERE first_name = ? AND marital_status = ?”;
$stmt_1 = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt_1,$query_1))
{
mysqli_stmt_bind_param($stmt_1,”ss”,$_POST[“first_name”],$_POST[“marital_status”]);
mysqli_stmt_execute($stmt_1);
$result_1 = mysqli_stmt_bind_result($stmt_1,$row_count);
mysqli_stmt_fetch($stmt_1);
$_SESSION[‘row_count’] = $row_count;
echo __LINE__; echo “<br>”;//DELETE
$form_step = ‘end’;
fetch_rows();
}
}
function fetch_rows()
{ echo __LINE__; echo “<br>”;//DELETE
$form_step = $_GET[‘form_step’];
$page_number = $_GET[‘page’];
$result_per_page = $_GET[‘page_limit’];
$offset = (($page_number * $result_per_page) – $result_per_page); //Offset (Row Number that ‘Starts’ on page).
$last_row_on_page = ($page_number * $result_per_page); //Max Result (Row Number that ‘Ends’ on page).
$previous_page = $page_number-1;
$next_page = $page_number+1;
echo “Row Start: $offset”;echo “<br>”;
echo “Row End: $last_row_on_page”;echo “<br>”;
//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
$conn = mysqli_connect(“localhost”,”root”,””,”powerpage”);
$conn->set_charset(‘utf8mb4’); //Always set Charset.
if($conn === false)
{
die(“ERROR: Connection Error!. ” . mysqli_connect_error());
}
$query_2 = “SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page”;
$stmt_2 = mysqli_stmt_init($conn);
if(mysqli_stmt_prepare($stmt_2,$query_2))
{echo __LINE__; echo “<br>”;//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 103.
mysqli_stmt_bind_param($stmt_2,”ss”,$_POST[“first_name”],$_POST[“marital_status”]);
mysqli_stmt_execute($stmt_2);
$result_2 = mysqli_stmt_get_result($stmt_2);
if(!$result_2)
{
//Close Connection.
mysqli_close($conn);
die(“<pre>2c. Statement Fetching failed!</pre>”);
}
else
{echo __LINE__; echo “<br>”;//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 114.
//Grab total number of pages to paginate.
$row_count = $_SESSION[‘row_count’];
//$total_pages = ceil($result_1/$result_per_page);
$total_pages = ceil($row_count/$result_per_page);
echo “TOTAL PAGES: $total_pages<br><br>”;
while($row = mysqli_fetch_array($result_2,MYSQLI_ASSOC))//On PAGE 2, PHP IGNORING THIS AND BYPASSING THIS WHOLE WHILE LOOP ON PAGE 2. IT IS LINE: 122.
{echo __LINE__; echo “<br>”;//On PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 123. PHP IGNORING IT BYPASSING IT ON PAGE 2.
//Retrieve Values.
$id = $row[“id”];
$first_name = $row[“first_name”];
$middle_name = $row[“middle_name”];
$surname = $row[“surname”];
$gender = $row[“gender”];
$marital_status = $row[“marital_status”];
$working_status = $row[“working_status”];
echo “Id: $id<br>”;
echo “First Name: $first_name<br>”;
echo “Middle Name: $middle_name<br>”;
echo “Surname: $surname<br>”;
echo “Gender: $gender<br>”;
echo “Marital Status: $marital_status<br>”;
echo “Working Status: $working_status<br>”;
echo “<br>”;
echo “<br>”;
$i = 1;
while($i<=$total_pages)
{
if($i<$total_pages)
{
echo “<a href=’http://localhost/power.page/pagination_test_simple_WORKING_ON_NOW.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&page=<?php echo $i;?>’><?php echo ” $i “;?></a><?php
}
elseif($i==$page_number)
{
echo “<a href=’http://localhost/power.page/pagination_test_simple_WORKING_ON_NOW.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&page=<?php echo $i;?>’><?php echo “<b> $i </b>”;?></a><?php
}
$i++;
}
if($page_number>$total_pages)
{
echo “<a href=’http://localhost/power.page/pagination_test_simple_WORKING_ON_NOW.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&page=<?php echo $previous_page;?>’><?php echo “<b> Previous </b>”;?></a><?php
}
}
}
}
$_SESSION[‘form_step’] == ‘end’;
}
?>
<form action=”<?php echo $_SERVER[‘PHP_SELF’];?>?form_type=<?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&page=1″ method=’post’ enctype=’plain/text’>
<?php
//Added ‘*’ (asterisk) to indicate the ‘Text Field’ is a ‘required’ one.
echo “<label for=”first_name”>First Name *:</label>
<input type=”text” name=”first_name” placeholder=”First Name” value = “”>”;?>
<br>
<?php
echo “<label for=”marital_status”>Marital Status *:</label>”;
echo “<select name=”marital_status”>”;
echo “<option value=”single”>Single</option>”;
echo “<option value=”married”>Married</option>”;
echo “</select>”;
echo “<br>”;
?>
<input type=”submit” name=”search” value=”Search”>
<?php
//$current_function = __FUNCTION__;
//echo $current_function;
//Do following if “Search” button clicked.
if($_SERVER[‘REQUEST_METHOD’] === ‘POST’)
{echo __LINE__; echo “<br>”;//DELETE
//Do following if “Search” button clicked.
if(isset($_POST[‘search’]))
{echo __LINE__; echo “<br>”;//DELETE
rows_count(); //This function will forward script flow to fetch_rows() before halting the script.
die();
}
}
echo __LINE__; echo “<br>”;//On PAGINATION PAGE 2, THIS FAILS TO ECHO. IT IS LINE: 24.
//Do following if “Search” button not clicked but pagination numbered links are clicked. Eg Page 1, 2, 3, etc..
fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ?
echo __LINE__; echo “<br>”;//On PAGINATION PAGE 2, THIS GETS ECHOED. IT IS LINE: 205.
}
?>
What is wrong ? Why is fetch_rows() or $query_2 failing to fetch the matching rows for pages beyond page 1 ?
ECHOES
Before clicking the SUBMIT button, I get echoed these line numbers as expected:
[code]
22
24
32
39
42
50
[code]
193
71
78
Row Start: 0
Row End: 1
103
114
TOTAL PAGES: 5
123
After clicking the link for ‘page 2’ on pagination section, I get echoed the same line numbers I get echoed before clicking the SUBMIT button as if everything is starting all over with a new query (when not). That is not supposed to happen.
I reckon line 200 is not taking action:
[code]
fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ? IT IS LINE: 200. MAIN ISSUE HERE, I SUSPECT.
NOTE: I am a beginner still on OOP and mysqli. Not on pdo either. So, kindly show samples to that level, if you must.
NOTICE:
It seems the WHILE loop isn’t running beyond page 1. Can’t figure why!