user3824280 user3824280 - 5 months ago 10
SQL Question

Search engine doesn't work with my paginator

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:

/* #container {
border: 0;
td {width:100%;float:left;clear:left}
th {visibility:hidden;} */
$con = mysql_connect("localhost","root","pass");
if (!$con) {
die("connE: " . mysql_error());

$UpdateQuery = "UPDATE users SET
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'>

echo "
<div id='container'>
</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



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.


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.