PHP SQL Change style of filtered data while still display all data

Question

I hava a page with a list of data displayed from database table and a search bar.

When I filter the data by id, the searched data will be highlighted (background color change) but I need it to remain displaying the rest of data.

I managed to change the background color of searched data however if I search the data that is not in table, the Record not found not displayed.

<?php
$search_keyword = '';
if (isset($_POST['search'])) {
    $search_keyword = $_POST['search'];
}
?>

<head></head>
<body>
    <form name="searchForm" action="" method="POST">
        <input type="text" id="search" name="search" placeholder="Enter Employee ID Search">
    </form>

    <?php
    $sql_search = "";
    if (!empty($search_keyword)) {
        $sql_search = " WHERE id = '" . $search_keyword . "' ";
    }

    $sql1 = "SELECT id, name, address FROM employee";
    $result = $conn->query($sql1);

    if ($result->num_rows > 0) {
        // output data of each row
        while ($row = $result->fetch_assoc()) {
    ?>
    
    <div class="row">
        <div class="employee">
            <?php
            if ($row["id"] == $search_keyword) {
            ?>
            <div class="red-bg">
            <?php
            } else {
            ?>
                <div class="white-bg">
            <?php
            }
            ?>
                    <div class="col-md-2"><?php echo $row["id"] ?></div>
                    <div class="col-md-3"><?php echo $row["name"] ?></div>
                    <div class="col-md-5"><?php echo $row["address"] ?></div>
                </div>
            </div>
        </div>
    </div>
    <?php
        }
    } else {
    ?>
    <div class="row">
        <div class="employee">
            <div class="white-bg">
                <?php echo "Record not found." ?>
            </div>
        </div>
    </div>
    <?php
    }
    ?>
</body>

<script>
    document.onkeydown = function(evt) {
        var keyCode = evt ? (evt.which ? evt.which : evt.keyCode) : event.keyCode;
        if (keyCode == 13) {
            //your function call here
            document.searchForm.submit();
        }
    }
</script>

If I include the search keyword in query, I can display the Record not found when searching for data not in table however if I search data that is in table will only display and highlight 1 data.

$sql1 = "SELECT id, name, address FROM employee ". $sql_search ."";

So how do I display all data and highlight searched data that is in table and only display Record not found when search for data that is not in table?

**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Jul 14 '22 at 15:33
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. — ADyson, Jul 14 '22 at 15:33
P.S. Your search query doesn't include the `WHERE` keyword in the appropriate place - it seems to use `AND` instead. This should cause a SQL syntax error. Add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` before your `mysqli_connect()` (or `new mysqli()`) command, and this will ensure that errors with your SQL queries are reported correctly to PHP automatically. Also make sure PHP error reporting / logging is enabled - see [How can I get useful error messages in PHP?](https://stackoverflow.com/questions/845021/how-can-i-get-useful-error-messages-in-php) for details. — ADyson, Jul 14 '22 at 15:35
P.S. Your requirement makes no sense from a UX perspective. It's inconsistent to, on the one hand, display all data but just highlight matches, but then suddenly hide _all_ the data when there is no match. It's a great way to confuse your users. What you should surely do instead when there are no matches is to still show all the data, but put a message saying "no matches found" at the beginning or end of the list. — ADyson, Jul 14 '22 at 15:45
If you simply process all the records as you do now, checking for matches, you just need a variable which is set to false initially, but is then set true when you find a match. At the end if it's still false, then display the "no matches found" message in your chosen place. — ADyson, Jul 14 '22 at 15:45
@ADyson Sorry I already change the AND to WHERE since in the original code actually have WHERE clause hence the query for search contain AND. Putting aside about the the code being vulnerable to sql injection first, I am able to do either one of it [only display and highlight matched data when found and display no match when not found] or [display all and highlight match when found but unable to display no match when not found] however the requirement given to me need both which what I'm having trouble doing. — Rhya, Jul 14 '22 at 16:19
I described above how you can do the no match thing, have you tried? — ADyson, Jul 14 '22 at 17:18
@ADyson Hi, I actually didnt quite understand your solution but somehow I manage to solve the problem. I have uploaded my answer below. — Rhya, Jul 15 '22 at 04:27

score 2 · Accepted Answer · answered Jul 15 '22 at 04:25

I have managed to highlight/bold the search data while still display all the other data and will display a message Record not found if the data is not in table.

<?php
include("database.php");

$search_keyword = '';
if (isset($_POST['search'])) {
    $search_keyword = $_POST['search'];
}
?>

<head>
</head>

<body>
    <form name="searchForm" action="" method="POST">
        <input type="text" id="search" name="search" placeholder="Enter Employee ID Search">
    </form>

    <?php

    //query for searching
    $sql_search_keyword = "";
    if (!empty($search_keyword)) {
        $sql_search_keyword = " WHERE id = '" . $search_keyword . "' ";

        $sql_want_search = "SELECT id, name, address FROM employee" . $sql_search_keyword;
        $result_want_search = $conn->query($sql_want_search);
    
        if ($result_want_search->num_rows > 0) {
            while ($row_want_search = $result_want_search->fetch_assoc()) {
                $searched_data = $row_want_search["id"];
            
                //query for displaying all data - if match with search will bold
                $sql_display_searched = "SELECT id, name, address FROM employee";
                $result_display_searched = $conn->query($sql_display_searched);
            
                if ($result_display_searched->num_rows > 0) {
                    while ($row_display_searched = $result_display_searched->fetch_assoc()) {
                        if ($searched_data == $row_display_searched["id"]) {
                            //bold match
                            echo "<b>" . $row_display_searched["id"] . " " . $row_display_searched["name"] . " " . $row_display_searched["address"] . " </b><br>";
                    } else {
                        echo $row_display_searched["id"] . " " . $row_display_searched["name"] . " " . $row_display_searched["address"] . " <br>";
                        }
                    }
                }
            }
        } else {
            echo "Record not found.";
        }
    } else {
    //Initial display all data before search
    $sql_initial = "SELECT id, name, address FROM employee";
            $result_initial = $conn->query($sql_initial);
    
            if ($result_initial->num_rows > 0) {
                while ($row_initial = $result_initial->fetch_assoc()) {
                echo $row_initial["id"] . " " . $row_initial["name"] . " " . $row_initial["address"] . " <br>";
            }
        }
    }
    ?>

</body>

<script>
    document.onkeydown = function(evt) {
        var keyCode = evt ? (evt.which ? evt.which : evt.keyCode) : event.keyCode;
        if (keyCode == 13) {
            //your function call here
            document.searchForm.submit();
        }
    }
</script>

PHP SQL Change style of filtered data while still display all data

1 Answers1