Showing only the search results without displaying the whole table

Question

I want the webpage to show the results once the search button is clicked. Currently, the whole table is displayed while the page is loading. If I change the value of the variable $sql from “Select * FROM users” then the search query doesn’t work anymore

I’m using XAMPP with webpage being a php file and the database is Mariadb

users table

+--------+-------------+---------------+--------------------------+--------+
|    ID  |  firstname  |  lastname     |    address               |  count |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     1  |    john     |    doe        |james street, idaho, usa  |  2     |                    
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     2  |    cindy    |   smith       |rollingwood av,lyn, canada| 1      |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     3  |    rita     |   chatsworth  |arajo ct, alameda, cali   | 0      |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     4  |    randy    |   plies       |smith spring, lima, (peru)| 1      |                       
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     5  |    Matt     |   gwalio      |park lane, (atlanta), usa | 2      |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+

The webpage with search bar.

<?php
ini_set('memory_limit', '1042M');
$localhost = "localhost";
$username = "root";
$password = "";
$dbname = "samueldb";
$con = new mysqli($localhost, $username, $password, $dbname);
if( $con->connect_error){
  die('Error: ' . $con->connect_error);
}

$sql = "SELECT * FROM users";

if( isset($_GET['btn']) ){
  $name = mysqli_real_escape_string($con, htmlspecialchars($_GET['search']));
   $sql =  "SELECT * FROM 'users' WHERE 'firstname' ='$name'";
}
$result = $con->query($sql);

?>
<!DOCTYPE html>
<html>
<head>
     <title>Find my search results</title>
<link href="css/templatemo-style.css" rel="stylesheet" />
 <link href="css/font-awesome.min.css" rel="stylesheet" />
<link href="css/font-awesome.css" rel="stylesheet" />
<link href="css/bootstrap.css" rel="stylesheet" />
 <link href="css/search-style.css" rel="stylesheet" />

</head>

<body>

  <h2>List of students</h2>

     <div class="search">

    <input type="text" class="searchTerm" placeholder="What are you looking for?">
    <button type="search" class="searchButton">
      <i class="fa fa-search"></i>
   </button>
 </div>
</div>
           <table class="table table-striped table-dark">
           <tr>
           <th>ID</th>
           <th>First_Name</th>
           <th>Address</th>
           <th>Count</th>
           </tr>

           <?php

            while($row = $result->fetch_assoc()){

               ?>
               <tr>
               <td><?php echo $row['ID']; ?></td>
               <td><?php echo $row['firstname']; ?></td>
               <td><?php echo $row['address']; ?></td>
               <td><?php echo $row['count']; ?></td>
               </tr>

               <?php
              }
               ?>
           </table>
           </div>


</body>
</html>

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) 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, Aug 10 '20 at 10:34

bertdida · Answer 1 · 2020-08-10T11:17:14.587

1

There are some issues going on to your code.

Your form. You forgot to wrap your inputs inside a form tag. You should also add a name="search" on your input, and type="search" on button doesn't make sense. Change your form to

<form>
    <input type="search" name="search" class="searchTerm" placeholder="What are you looking for?">
    <button type="submit" name="btn" class="searchButton">
        <i class="fa fa-search"></i>
    </button>
</form>

name attributes should match on your $_GET[name].

Your search query. You should put single quotes on the value only, not on columns. Change your query to

$sql = "SELECT * FROM users WHERE firstname = '$name'";

And most importantly, you are not using prepared statements and parameterized queries.What your are doing is vulnerable from SQL injection attack. Read more here ASAP.

edited Aug 10 '20 at 11:17

answered Aug 10 '20 at 02:02

bertdida

4,988
2
16
22

I plugged in your code and the search results still showed up when I opened the page. – NotAFriendlyTurtle Aug 11 '20 at 23:19
Maybe because you still have the `btn` parameter on the url. If it exists our search query will be used. – bertdida Aug 11 '20 at 23:37
There are no button parameters on the url until the search button is clicked. – NotAFriendlyTurtle Aug 13 '20 at 17:41
I noticed that I've the following line that may be causing the table to be displayed as the page is loaded: $sql = "SELECT * FROM 'users' " But if I remove this line then I get an error: `Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in C:\xampp\htdocs\opensourcetest.php:58 Stack trace: #0 {main} thrown in C:\xampp\htdocs\opensourcetest.php on line 58 ` – NotAFriendlyTurtle Aug 18 '20 at 05:48

Showing only the search results without displaying the whole table

users table

1 Answers1