problem with pagination of php mysql results

Question

I've a problem with pagination of php mysql results. The user must be able to enter something in the text field, and when the 'send' button is pressed, it must immediately show the first ten results (and this now does it well). The problem is that the links (1, 2,3, etc.) don't work: in a certain way they return to an initial situation in which there is the pagination of all the data contained in my mysql database.

In my opinion the problem is with this line of my code: echo "<a href='prova.php?page=".$page."'>".$page."</a> -- "; , where I'm getting the link wrong. I have already searched a lot on the Internet and especially here on stackoverflow.com for possible solutions to my problem, but I've not found anything exhaustive to solve my problem. Where am I wrong in your opinion? The file name is prova.php.

<!-- The code may contain words taken from the Italian language -->
<html lang="it-IT">
<head>
    <link rel="icon" href="https://image.flaticon.com/icons/png/512/1427/1427106.png">
    <title>Pagination tests</title>
</head>
<body style="background: #ECF0F1;font-family: Myanmar Text;margin-top:90px;">
<form action="prova.php?page=1" method="get"><input value="<?php echo $_GET['text']; ?>" type="text" name="text"><button name="send">INVIA</button><br></form>
<?php 
include 'functions.php';
$con = mysqli_connect('localhost', 'root', '', 'cherubini');

$search = $_GET['text'];
if(isset($_GET['send'])) {
    $search = $_GET['text'];
}
$result = mysqli_query($con, "SELECT * 
                                FROM lemmi 
                                WHERE LemmaNo LIKE '%".$search."%'");
$number_of_results = mysqli_num_rows($result);

$number_of_pages = ceil($number_of_results / 10);
if(!isset($_GET['page'])) { 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
}
$this_page_first_result = ($page - 1) * 10; 
$result = mysqli_query($con, "SELECT * FROM lemmi WHERE LemmaNo LIKE '%".$search."%' LIMIT ".$this_page_first_result.',10');

while ($row = mysqli_fetch_array($result)) {
    echo "<b>". highlight($search, $row['Lemma']) . '</b> ' . $row['Definizione'] . "<br>";
}

for($page = 1; $page <= $number_of_pages; $page++) {
    echo "<a href='prova.php?page=".$page."'>".$page."</a> --  ";
}
?>
</body>
</html>

Please see about sql injection and the importance of prepared and bound queries — Strawberry, Dec 26 '20 at 13:38
With "importance of prepared and bound queries", you mean I've to use prepared statements in php right? In your opinion, this webpage https://www.w3schools.com/php/php_mysql_prepared_statements.asp can be useful for me? — Krishna Pilato, Dec 26 '20 at 13:43
No, see https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php. Always use php.net. — user3783243, Dec 26 '20 at 13:47
IMO, nothing on w3schools can be useful for you. But then I'm biased. — Strawberry, Dec 26 '20 at 14:01
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 26 '20 at 14:13

score 0 · Accepted Answer · answered Dec 26 '20 at 13:57

Your links don't have text or send set so the search query runs for:

SELECT * FROM lemmi WHERE LemmaNo LIKE '%%'

You should likely do:

for($page = 1; $page <= $number_of_pages; $page++) {
    echo "<a href='prova.php?page=".$page."&text=" . htmlspecialchars($search, ENT_QUOTES) . "&send=1'>".$page."</a> --  ";
}

You also should use prepared statements with parameterized queries, and use error reporting.

The $search = $_GET['text']; should have thrown an undefined index notice.

if(isset($_GET['send'])) {
    $search = $_GET['text'];
}

after that line also doesn't make sense, likely something like this would be better:

if(!isset($_GET['send'])) {
    echo 'a search term must be entered.';
} else {
    $search = $_GET['text'];
    ...continue with search code in this block
}

problem with pagination of php mysql results

1 Answers1