Jaymin Sejpal Jaymin Sejpal - 4 months ago 15
PHP Question

Return only searched rows in table and hide other

If I write something in search box and press search , it should only return matched rows and hide other rows.

Here is my code, it works perfects only issue is it gives me searched record + all record list of table.
What can I do to show only searched data in table.?

<div id="pageContent"><br />
<div class="search" align="right">
<form action="" method="post">
Search: <input type="text" name="term" /><br />
<input type="submit" value="Submit" />
</form>
</div>
<div class="container">
<table id="employee-grid" width="auto" cellpadding="1" cellspacing="1" border="1" class="table table-hover">
<?php
include_once '../storescripts/connect_to_mysql.php';
$num_rec_per_page=5;
if (isset($_GET["page"]))
{
$page = $_GET["page"];
}
else
{
$page=1;
}
$start_from = ($page-1) * $num_rec_per_page;
$result= mysql_query("SELECT * FROM products LIMIT $start_from, $num_rec_per_page");
?>
<thead>
<tr class="success">
<th>Id</th>
<th>Product Name</th>
<th>Price</th>
<th>Status</th>
<th>Quantity</th>
<th>Details</th>
<th>Category</th>
<th>Subcategory</th>
<th>Date Added</th>
<th colspan="2">Functions</th>
</tr>
</thead>
<?php
if (!empty($_REQUEST['term'])) {
$term = mysql_real_escape_string($_REQUEST['term']);

$sql = "SELECT * FROM products WHERE product_name LIKE '%".$term."%' or price LIKE '%".$term."' or details LIKE '%".$term."'";

$r_query = mysql_query($sql);
if($r_query>1)
{
while ($row = mysql_fetch_array($r_query)){
echo "<tr bgcolor='red'>";
echo "<td>".$row['id']."</td>";
echo "<td>".$row['product_name']."</td>";
echo "<td>".$row['price']."</td>";
echo "<td>".$row['status']."</td>";
echo "<td>".$row['quantity']."</td>";
echo "<td>".$row['details']."</td>";
echo "<td>".$row['category']."</td>";
echo "<td>".$row['subcategory']."</td>";
echo "<td>".$row['date_added']."</td>";
echo "<td><a href='product_listing_edit.php?id=".$row['id']."'>Edit</a></td>";
echo "<td><a name='delete' href='product_listing_delete.php?id=".$row['id']."'>Delete</a></td><tr>";
echo "</tr>";
}
}
else{
echo "Nothing should be displayed";
}
}
?>
<?php
while($row=mysql_fetch_array($result))
{
echo "<tr class='danger'>";
echo "<td>".$row['id']."</td>";
echo "<td>".$row['product_name']."</td>";
echo "<td>".$row['price']."</td>";
echo "<td>".$row['status']."</td>";
echo "<td>".$row['quantity']."</td>";
echo "<td>".$row['details']."</td>";
echo "<td>".$row['category']."</td>";
echo "<td>".$row['subcategory']."</td>";
echo "<td>".$row['date_added']."</td>";
echo "<td><a href='product_listing_edit.php?id=".$row['id']."'>Edit</a></td>";
echo "<td><a name='delete' href='product_listing_delete.php?id=".$row['id']."'>Delete</a></td><tr>";
echo "</tr>";
}
?>
</table>

Answer

Just keep single while loop and run the query and search query as shown below it will solve the issue:

<div id="pageContent"><br />
    <div class="search" align="right">
        <form action="" method="post">  

            Search: <input type="text" name="term" /><br />  
            <input type="submit" value="Submit" /> 
        </form>  
    </div>
    <div class="container">
        <table id="employee-grid" width="auto" cellpadding="1" cellspacing="1" border="1" class="table table-hover">
            <?php
            include_once '../storescripts/connect_to_mysql.php';
            $num_rec_per_page = 5;
            if (isset($_GET["page"])) {
                $page = $_GET["page"];
            } else {
                $page = 1;
            };
            $start_from = ($page - 1) * $num_rec_per_page;
            $sql = "SELECT * FROM products LIMIT $start_from, $num_rec_per_page";
            ?>
            <thead>
                <tr class="success">
                    <th>Id</th>
                    <th>Product Name</th>
                    <th>Price</th>
                    <th>Status</th>
                    <th>Quantity</th>
                    <th>Details</th>
                    <th>Category</th>
                    <th>Subcategory</th>
                    <th>Date Added</th>
                    <th colspan="2">Functions</th>
                </tr>
            </thead>


<?php
if (!empty($_REQUEST['term'])) {

    $term = mysql_real_escape_string($_REQUEST['term']);

    $sql = "SELECT * FROM products WHERE product_name  LIKE '%" . $term . "%' or price LIKE '%" . $term . "'  or details LIKE '%" . $term . "'";
}
$r_query = mysql_query($sql);
if ($r_query > 1) {

    while ($row = mysql_fetch_array($r_query)) {
        echo "<tr bgcolor='red'>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['product_name'] . "</td>";
        echo "<td>" . $row['price'] . "</td>";
        echo "<td>" . $row['status'] . "</td>";
        echo "<td>" . $row['quantity'] . "</td>";
        echo "<td>" . $row['details'] . "</td>";
        echo "<td>" . $row['category'] . "</td>";
        echo "<td>" . $row['subcategory'] . "</td>";
        echo "<td>" . $row['date_added'] . "</td>";
        echo "<td><a href='product_listing_edit.php?id=" . $row['id'] . "'>Edit</a></td>";
        echo "<td><a name='delete' href='product_listing_delete.php?id=" . $row['id'] . "'>Delete</a></td><tr>";



        echo "</tr>";
    }
} else {
    echo "Nothing should be displayed";
}
?>
        </table>