How can I filter through an SQL generated table using a search box?

Question

This is what I have currently (which doesn't do anything)

<input type="text" id="search" " placeholder="Search..">

<table id='table'>
    <!-- INVENTORY TABLE -->
    <?php
    $query = "SELECT * FROM StatesboroSwitchActive";
    $result = mysqli_query($db, $query);
    $count = 0;

    echo "
    <tr>
    <th></th>
    <th></th>
    <th>Name</th>
    <th>Model</th>
    <th>Serial</th>
    <th>MAC</th>
    <th>Type</th>
    <th>Notes</th>
    <th>Cisco Notes</th>
    <th>Location</th>
    </tr>";
    while ($row = mysqli_fetch_array($result)) {
        $count++;
            echo "<tr><td>" . "<button id='delete'>RMA</button>" .
            "</td><td>" . "<button id='modify'>Modify</button>" .
            "</td><td>" . $row['Name'] .
            "</td><td>" . $row['Model'] .
            "</td><td>" . $row['Serial'] .
            "</td><td>" . $row['MAC']  .
                "</td><td>" . $row['Type']  .
                "</td><td>" . $row['Notes']  .
                "</td><td>" . $row['Cisco Notes']  .
                "</td><td>" . $row['Location']  . "</td></tr>";
    }
    echo "<h3>Total: " . $count . "</h3>";
    ?>

I want to be able to use the search box to make this table filter down into what is typed by the user. I am pretty lost right now

I would recommend you some javascript for that, for example https://www.w3schools.com/howto/howto_js_sort_table.asp — Hynek Bernard, May 20 '21 at 12:37
if you are working with tables look at : https://datatables.net/ — kelvin, May 20 '21 at 12:38
What does it have to do with MySQL or PHP? It looks to me like you are asking how to implement a filter for HTML table in the user interface — Dharman, May 20 '21 at 12:51

score 1 · Answer 1 · edited May 20 '21 at 17:06

It's all depends on all of your row names on your SQL. Suppose that yours were Name, Model, Serial, MAC, Type, Notes, and Cisco_Notes, Location. So you can add one submit button to apply those search arguments. Then, you can add some filter from SQL, e.g.

SELECT * FROM StatesboroSwitchActive 
WHERE Name LIKE '%something%' 
OR Model LIKE '%something%' 
OR Serial LIKE '%something%'
...

The rest doesn't need to be changed. Please correct me if I'm wrong.

<form action="" method="post">  
<input type="text" name="search" id="search" " placeholder="Search.."/>
<input type="submit" value="Submit" />  
</form>  
<?php
$search = '%' . mysqli_real_escape_string($db, $_REQUEST['search']) . '%';     

$query = "SELECT * FROM StatesboroSwitchActive 
WHERE Name LIKE '$search' 
OR Model LIKE '$search' 
OR Serial LIKE '$search' 
OR MAC LIKE '$search' 
OR Type LIKE '$search' 
OR Notes LIKE '$search' 
OR Cisco_Notes LIKE '$search' 
OR Location LIKE '$search'"; 

$result = mysqli_query($db, $query);
$count = 0;
echo "
    <tr>
    <th></th>
    <th></th>
    <th>Name</th>
    <th>Model</th>
    <th>Serial</th>
    <th>MAC</th>
    <th>Type</th>
    <th>Notes</th>
    <th>Cisco Notes</th>
    <th>Location</th>
    </tr>";
while ($row = mysqli_fetch_array($result)) {
        $count++;
        echo "<tr><td>" . "<button id='delete'>RMA</button>" .
            "</td><td>" . "<button id='modify'>Modify</button>" .
            "</td><td>" . $row['Name'] .
            "</td><td>" . $row['Model'] .
            "</td><td>" . $row['Serial'] .
            "</td><td>" . $row['MAC']  .
            "</td><td>" . $row['Type']  .
            "</td><td>" . $row['Notes']  .
            "</td><td>" . $row['Cisco Notes']  .
            "</td><td>" . $row['Location']  . "</td></tr>";
    }
    echo "<h3>Total: " . $count . "</h3>";

inspired from : Creating a search form in PHP to search a database?

this worked perfectly, i was missing the wildcard selectors in sql (%), thanks! — apsofatl, May 21 '21 at 13:09

How can I filter through an SQL generated table using a search box?

1 Answers1