Search engine doesn't work with my paginator

Question

I'am quite noob in PHP. I did a php script that get data from a mysql table and store it in textboxes, then I can edit the text boxes and click update button to update the mysql. I added paginator to my script cause the results are so many. Now I am trying to add search engine. Here's my code:

<html>
<head>
<title>TITLE</title>
</head>
<style>
/* #container {
display:inline-block;width:100%;float:left;clear:left
}
table{
    width:100%;
}
td{
    border: 0;
}
td {width:100%;float:left;clear:left}
th {visibility:hidden;} */
</style>
<body>
<?php
$num_rec_per_page=5;
$con = mysql_connect("localhost","root","pass");
if (!$con) {
die("connE: " . mysql_error());
}
mysql_select_db("database_name",$con);

if(isset($_POST['update'])){
$UpdateQuery = "UPDATE users SET 
username='$_POST[username]',
password='$_POST[password]'
WHERE username='$_POST[hidden]'"; // etc, etc ... i deleted all the stuff cause it's too many

mysql_query($UpdateQuery, $con);    
};

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$start_from = ($page-1) * $num_rec_per_page; 

$sql = "SELECT * FROM users LIMIT $start_from, $num_rec_per_page";

if (isset($_GET['search'])) {
    $search_term = mysql_real_escape_string($_GET['search_box']);
    $sql .= "WHERE username = '{$search_term}'";
};

$myData = mysql_query($sql, $con) or die (mysql_error());

echo "<form name='search_form' method='GET' action='users.php'>
Search: <input type='text' name='search_box' value='Search' />
<input type='submit' name='search' value='Search'>
</form>";

echo "
<div id='container'>
<table>
<tr>
<th>Username</th>
<th>Password</th>
</tr>"; // etc, etc ... i deleted all the stuff cause it's too many
while($record = mysql_fetch_array($myData)){
echo "<form action=users.php method=post>";
echo "<tr>";
echo "<td>" . "<input type=text name=username value='" . $record['username'] . "' </td>";
echo "<td>" . "<input type='text' name='password' value='" . $record['password'] . "' </td>";
echo "<td>" . "<input type='hidden' name='hidden' value='" . $record['username'] . "' </td>";
echo "<td>" . "<input type='submit' name='update' value='update'" . " </td>";
echo "</tr>"; // etc, etc ... i deleted all the stuff cause it's too many
echo "</form>";
echo "<br />";
}
echo "</table>";

$sql = "SELECT * FROM users";
$myData = mysql_query($sql, $con);
$total_records = mysql_num_rows($myData);  //count number of records
$total_pages = ceil($total_records / $num_rec_per_page);

echo "</div>";

echo "<a href='users.php?page=1'>"."<img style='margin-right:10px;margin-top:-3px;' src='img/prev.png'>"."</a> "; // Goto 1st page 

for ($i=1; $i<=$total_pages; $i++) { 
            echo "<a href='users.php?page=".$i."'>&nbsp;".$i."&nbsp;</a> "; 
}; 

echo "<a href='users.php?page=$total_pages'>"."<img style='margin-left:11px;margin-top:-3px;' src='img/next.png'>"."</a> "; // Goto last page

mysql_close($con);
?>

</body>
</html>

Upon searching I get message: Undeclared variable: 5WHERE As I already said, when I remove paginator it's all fine, the search works, but it won't work with paginator enabled.

score 1 · Accepted Answer · edited May 23 '17 at 12:23

The LIMIT clause should be after WHERE clause. So your code should be like this:

// your code

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$start_from = ($page-1) * $num_rec_per_page;

$sql = "SELECT * FROM users";

if (isset($_GET['search'])) {
    $search_term = mysql_real_escape_string($_GET['search_box']);
    $sql .= " WHERE username = '{$search_term}'";
}

$sql .= " LIMIT $start_from, $num_rec_per_page";

$myData = mysql_query($sql, $con) or die (mysql_error());

// your code

Sidenote: Don't use mysql_* functions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or pdo instead. And this is why you shouldn't use mysql_* functions.

That worked perfectly. Thanks for you answer and the Sidenote. — , May 03 '16 at 01:12

Search engine doesn't work with my paginator

1 Answers1