I'm a PHP student and I'm developing my first app. I need to add pagination on the search results with this code below. I can't use datatables or another plug-ins because it's hard for me to put action buttons and my data on table.
If you know some simple method that can be not so hard to implement will help a lot.
I'm using the example from this dev: how to search and filter with php
if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM `users` WHERE CONCAT(`id`, `fname`, `lname`, `age`) LIKE '%".$valueToSearch."%'";
$search_result = filterTable($query);
}
else {
$query = "SELECT * FROM `users`";
$search_result = filterTable($query);
}
// function to connect and execute the query
function filterTable($query)
{
$connect = mysqli_connect("localhost", "root", "", "test_db");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP HTML TABLE DATA SEARCH</title>
<style>
table,tr,th,td
{
border: 1px solid black;
}
</style>
</head>
<body>
<form action="php_html_table_data_filter.php" method="post">
<input type="text" name="valueToSearch" placeholder="Value To Search"><br><br>
<input type="submit" name="search" value="Filter"><br><br>
<table>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
<!-- populate table from mysql database -->
<?php while($row = mysqli_fetch_array($search_result)):?>
<tr>
<td><?php echo $row['id'];?></td>
<td><?php echo $row['fname'];?></td>
<td><?php echo $row['lname'];?></td>
<td><?php echo $row['age'];?></td>
</tr>
<?php endwhile;?>
</table>
</form>
</body>
</html>
Below example will work wells for requirements :
mysql_connect("localhost","wellho","wawawawa");
mysql_select_db("wellho");
$perpage = 10;
$html = "";
$startat = $_REQUEST[page] * $perpage;
$limlim = "%".$_REQUEST[look4]."%";
$q = mysql_query("select count(entry_id) from mt_entry where entry_title like '$limlim'");
$row = mysql_fetch_array($q);
$havesome = $row[0];
$pages = floor(($row[0]-1) / $perpage) +1 ;
$q = mysql_query("select * from mt_entry where entry_title like '$limlim' order by entry_id desc limit $startat,$perpage");
while ($row = mysql_fetch_assoc($q)) {
$text = strip_tags($row[entry_text]);
$text = substr($text,0,300);
$html .= "<dt>$row[entry_id] - <a href=/mouth/$row[entry_id]_.html target=pix>$row[entry_title]</a></dt>";
$html .= "<dd>$text ....<br><br></dd>";
};
$lynx = "Please choose the next page you want to view:";
for ($k=0; $k<$pages; $k++) {
if ($k != $_REQUEST[page]) {
$lynx .= " <a href=$PHP_SELF"."?page=$k&look4=".urlencode(stripslashes($_REQUEST[look4])).">".($k+1)."</a>";
} else {
$lynx .= " <b>--".($k+1)."--</b>";
}
}
if ($pages < 2) {
$lynx = "All results shown on this page";
}
if ($havesome == 0) {
$lynx = "Sorry - no titles matched. Please change your search string";
}
?>
<html><head>
<title>Showing blog entries</title>
<body>
<h2>Search titles on "The Horse's Mouth"</h2>
<form>Search only for titles including ... <input name=look4
value="<?= htmlspecialchars(stripslashes($_REQUEST[look4])) ?>">
(Please leave box empty to select all titles)<br>
<input type=submit></form><br>
<h2>Here are the entries you selected - page <?= $_REQUEST[page]+1 ?>:</h2><br>
<?= $html ?>
<?= $lynx ?>
</body>
You should use limit and offset to paginate the results.
<?php
...
// If no 'page' parameter is found, default to 1
$currentPage = isset($_GET['page']) ? $_GET['page'] : 1;
// Results per page
$limit = 10;
// Offset = (page - 1) * limit. (page 1 = 0, page 2 = 10, etc...)
$offset = ($currentPage - 1) * $limit;
if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM `users` WHERE CONCAT(`id`, `fname`, `lname`, `age`) LIKE '%".$valueToSearch."%' LIMIT $limit OFFSET $offset";
$search_result = filterTable($query);
}
else {
$query = "SELECT * FROM `users` LIMIT $limit OFFSET $offset";
$search_result = filterTable($query);
}
// function to connect and execute the query
function filterTable($query)
{
$connect = mysqli_connect("localhost", "root", "", "test_db");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP HTML TABLE DATA SEARCH</title>
<style>
table,tr,th,td
{
border: 1px solid black;
}
</style>
</head>
<body>
<form action="php_html_table_data_filter.php" method="post">
<input type="text" name="valueToSearch" placeholder="Value To Search"><br><br>
<input type="submit" name="search" value="Filter"><br><br>
<table>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
<!-- populate table from mysql database -->
<?php while($row = mysqli_fetch_array($search_result)):?>
<tr>
<td><?php echo $row['id'];?></td>
<td><?php echo $row['fname'];?></td>
<td><?php echo $row['lname'];?></td>
<td><?php echo $row['age'];?></td>
</tr>
<?php endwhile;?>
</table>
</form>
<a href="php_html_table_data_filter.php?page=<?php echo $page-- ?>">Previous</a>
<a href="php_html_table_data_filter.php?page=<?php echo $page++ ?>">Next</a>
</body>
</html>
PS: In the previous example i didn't checked if the page is the first or last, you should do that.