Hi,
I just joined.
I need advice. On php. I starter level student.
I learning procedural style. Mysqli. Prepared Statement. Don’t know anything else. No pdo. No oop.
Can you people check my pagination code ?
It supposed to work like this:
When you click Search button, at beginning rows_count() does action. It gets matching rows number. Like this:
$query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
Is query ok ?
Then the fetch_rows() fetches rows data and displays in a pagination mode.
When you click any page numbers on any pagination links, like page 2, then fetch_rows() is supposed to fetch the relevant rows again for page 2.
I fetching rows with this query:
$query_2 = "SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $offset,$last_row_on_page";
Is query ok ?
I got problem here …
while($row = mysqli_fetch_array($result_2,MYSQLI_ASSOC))
It does not get into action in any pages beyond page 1 like page 2, page 2, etc.
It only manage to get into action fetching matching rows for page 1. Not passed page 1. That is my big problem.
Code I configure to display 1 row per page only in dev mode. Will switch to 10-100 later on production mode.
Since exist 5 matching rows then these rows supposed to display across many pages via pagination.
Lines I am having trouble are CAPITALISED in my code comments. Do put attention on them to understand my problem to give right solution.
//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:
<?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
$_SESSION['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 SEARCH button, I get echoed these line numbers as expected:
22
24
32
39
42
50After clicking the SEARCH button I get these echoed as expected:
193
71
78
Row Start: 0
Row End: 1
103
114
TOTAL PAGES: 5
123After paginated clicking link for ‘page 2’, I get echoed all these same line numbers I get echo before clicking the SEARCH button as if everything is starting all over again with a new query (I not intend this). That not supposed to happen.
I reckon line 200 is not taking action:
fetch_rows(); //On PAGINATION PAGE 2, THIS FUNCTION IS NOT GETTING TRIGGERED! WHY ? IT IS LINE: 200. MAIN ISSUE HERE, I SUSPECT.You think it is line 200 the crook here ?
How to correctify the coding ?
All procedural style and mysqli only. I confused about oop and pdo. I used to mysqli. Still beginner for long time now.