user3824280 user3824280 - 7 months ago 18
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:

<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.

Answer

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.

Comments