Home>

I'm sorry for the question.

I am currently studying PHP and Mysql in a local environment.

I don't know how to display all records registered in the database with pagination before
I asked a question here and it was solved successfully.
Below is the question.
https://www.tutorialfor.com/go.php?id=140851

This time is derived from here?
I want to display data with a specified value in a column with pagination, but it doesn't work.

The following is the source code.

Select category

<form name = "selectcate" method = "post" action = "menu-category.php">
<dl>
    <dt>
    <label for = "kind">Type</label>
    </dt>
    <dd>
    <select name = "kind">
    <option value = "fish">fish</option>
    <option value = "meat">meat</option>
    <option value = "vegetable">vegetable</option>
    <option value = "Drink">Drink</option>
    </select>
    </dd>
</dl>
<input type = "submit" value = "search">
</form>

Display screen created first

<? php
require ('dbconnect.php');
if (isset ($_ REQUEST ['page'])) {
$page = $_REQUEST ['page'];
} else {
$page = 1;
}
$page = max ($page, 1);
$category = $_REQUEST ['kind'];
// get the last page
$cnt = $mysqli->query ('SELECT COUNT (kind = "$category") AS cnt FROM menu');
$table = mysqli_fetch_assoc ($cnt);
$maxPage = ceil ($table ['cnt']/5);
$page = min ($page, $maxPage);
$start = ($page-1) * 5;
?>
<div>
<table>
    <tr>
    <th>Product Management ID</ht>
    <th>Category</ht>
    <th>Product Name</ht>
    <th>Price</ht>
    <th>Remarks</ht>
    </tr>
<? php
echo ($category);
$recodeSet = $mysqli->query ("SELECT * FROM menu WHERE kind = '". $category. "' ORDER BY id LIMIT $start, 5");
while ($table = mysqli_fetch_assoc ($recodeSet)) {
?>
<tr>
    <td><? php echo (htmlspecialchars ($table ['id']));?></td>
    <td><? php echo (htmlspecialchars ($table ['kind']));?></td>
    <td><? php echo (htmlspecialchars ($table ['name']));?></td>
    <td><? php echo (htmlspecialchars ($table ['price']));?></td>
    <td><? php echo (htmlspecialchars ($table ['remarks']));?></td>
</tr>
<? php
    }
?>
</table>
<ul>
<? php
    if ($page>1) {
?>
<li><a href = "menu-category.php? page =<? php print ($page-1);?>" onclick = "document.form1.submit ();return false;"&gt ;Previous page</a></li>
<form name = "form1" method = "POST" action = "menu-category.php? page =<? php print ($page-1);?>">
<input type = hidden name = "kind" value = "<? php echo ($category);?>">
<? php
    } else {
?>
<? php
}
?>
<? php
    if ($page<$maxPage) {
?>
<li><a href = "menu-category.php? page =<? php print ($page + 1);?>" onclick = "document.form2.submit ();return false;"&gt ;Next page</a></li>
<form name = "form2" method = "POST" action = "menu-category.php? page =<? php print ($page + 1);?>">
<input type = hidden name = "kind" value = "<? php echo ($category);?>">
<? php
    } else {
?>
<? php
    }
?></ul>
</div>


If this is the case, there will be up to 5 pages, so if you originally selected fish, there are 7 count results, so the second page will be the last page.
However, I noticed that it was counted by sql and there was not enough value for a specific column
Added WHERE = kind"$category"to the sql statement.

By the way, with MySql
SELECT COUNT (kind ='fish') AS cnt FROM menu WHERE kind ='fish&apos ;;
The desired result (7) was displayed in the sentence.

The newly created code based on that is the following code.

Modified version

<? php
require ('dbconnect.php');
if (isset ($_ REQUEST ['page'])) {
$page = $_REQUEST ['page'];
} else {
$page = 1;
}
$page = max ($page, 1);
$category = $_REQUEST ['kind'];
// get the last page
$cntsql = 'SELECT COUNT (kind ='. "$category". ') AS cnt FROM menu WHERE kind ='. "$category". '';
var_dump ($cntsql);// The value is checked here, but you should have the SQL I want.
$cnt = $mysqli->query ($cntsql);
$table = mysqli_fetch_assoc ($cnt);
$maxPage = ceil ($table ['cnt']/5);
$page = min ($page, $maxPage);
$start = ($page-1) * 5;
?>
<div>
<table>
    <tr>
    <th>Product Management ID</ht>
    <th>Category</ht>
    <th>Product Name</ht>
    <th>Price</ht>
    <th>Remarks</ht>
    </tr>
<? php
echo "$category</br>";
$statement = 'SELECT * FROM menu WHERE kind ='. "$category". 'ORDER BY id LIMIT'. $start. ', 5';
var_dump ($statement);// Where $start should be 0, it should be -5. That means the last page is not getting well. .
$recodeSet = $mysqli->query ($statement);
while ($table = mysqli_fetch_assoc ($recodeSet)) {
?>
<tr>
    <td><? php echo (htmlspecialchars ($table ['id']));?></td>
    <td><? php echo (htmlspecialchars ($table ['kind']));?></td>
    <td><? php echo (htmlspecialchars ($table ['name']));?></td>
    <td><? php echo (htmlspecialchars ($table ['price']));?></td>
    <td><? php echo (htmlspecialchars ($table ['remarks']));?></td>
</tr>
<? php
    }
?>
</table>
<ul>
<? php
    if ($page>1) {
?>
<li><a href = "menu-category.php? page =<? php print ($page-1);?>" onclick = "document.form1.submit ();return false;"&gt ;Previous page</a></li>
<form name = "form1" method = "POST" action = "menu-category.php? page =<? php print ($page-1);?>">
<input type = hidden name = "kind" value = "<? php echo ($category);?>">
<? php
    } else {
?>
<? php
}
?>
<? php
    if ($page<$maxPage) {
?>
<li><a href = "menu-category.php? page =<? php print ($page + 1);?>" onclick = "document.form2.submit ();return false;"&gt ;Next page</a></li>
<form name = "form2" method = "POST" action = "menu-category.php? page =<? php print ($page + 1);?>">
<input type = hidden name = "kind" value = "<? php echo ($category);?>">

<? php
    } else {
?>
<? php
    }
?>
</ul>
</div>


Nothing is displayed because $start is -5.
There are two errors displayed, both
Warning: mysqli_fetch_assoc () expects parameter 1 to be mysqli_result, boolean given in

The location of that code is
$table = mysqli_fetch_assoc ($cnt);
while ($table = mysqli_fetch_assoc ($recodeSet)) {
It becomes.

Since I can't find a solution, please let me know if you can teach me.