Php/SQL Table Search with Space-CodePudding

I setup a php file to pull data from a database, and list that on a table. I then made a simple search function to search that data, however I can't figure out how to implement a space when searching. For example, if I searched test 2 (test from one column, 2 from another) it won't display anything. However, if I were to search 'test2' or '2test' it displays all results. How would I implement that space into my code?

Here's my code:


if(isset($_POST['search']))
{
    $valueToSearch = $_POST['valueToSearch'];
    // search in all table columns
    // using concat mysql function
    $query = "SELECT * FROM `master` WHERE CONCAT_WS(`id`, `office`, `firstName`, `lastName`, `type`, `status`, `deadline`, `contactPref`, `email`, `phoneNumber`, `taxPro`) LIKE '%".$valueToSearch."%'";
    $search_result = filterTable($query);
    
}
 else {
    $query = "SELECT * FROM `master`";
    $search_result = filterTable($query);
}

// function to connect and execute the query
function filterTable($query)
{
    $connect = mysqli_connect("localhost", "", "", "");
    $filter_Result = mysqli_query($connect, $query);
    return $filter_Result;
    echo "test";
}

?>

<!DOCTYPE html>
<html>
    <head>
        <title>PHP HTML TABLE DATA SEARCH</title>
        <style>
            table,tr,th,td
            {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>
        
        <form action="Untitled-1.php" method="post">
            <input type="text" name="valueToSearch" placeholder="Value To Search"><br><br>
            <input type="submit" name="search" value="Filter"><br><br>
            
            <table>
                <tr>
                    <th>ID</th>
                    <th>Office</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Type</th>
                    <th>Status</th>
                    <th>Deadline</th>
                    <th>Contact Preference</th>
                    <th>Email</th>
                    <th>Phone Number</th>
                    <th>Tax Pro</th>
                </tr>

      <!-- populate table from mysql database -->
                <?php while($row = mysqli_fetch_array($search_result)):?>
                <tr>
                    <td><?php echo $row['id'];?></td>
                    <td><?php echo $row['office'];?></td>
                    <td><?php echo $row['firstName'];?></td>
                    <td><?php echo $row['lastName'];?></td>
                    <td><?php echo $row['type'];?></td>
                    <td><?php echo $row['status'];?></td>
                    <td><?php echo $row['deadline'];?></td>
                    <td><?php echo $row['contactPref'];?></td>
                    <td><?php echo $row['email'];?></td>
                    <td><?php echo $row['phoneNumber'];?></td>
                    <td><?php echo $row['taxPro'];?></td>
                </tr>
                <?php endwhile;?>
            </table>
        </form>
        
    </body>
</html>```

CodePudding user response：

A way to do this could be to have your string spillted up into words in php. You could use explode function for this (https://www.php.net/manual/en/function.explode.php)

And then write SQL where clause like this (im assuming you are using mysql): WHERE col LIKE %word% OR col LIKE %word2%

This would be a bit inefficient, maybe the tool what you are looking for is elasticsearch? https://www.elastic.co/what-is/elasticsearch

Also, you should NEVER use . operator on an untrusted input. Because it creates MySQL Injection vulnerability. See this question for reference: How can I prevent SQL injection in PHP?

CodePudding user response：

The simplest solution would be using FULLTEXT index in your mysql field. But I don't recommend using it on production. Instead, for actual projects external searching engine must be considered (e.g. Elasticsearch and other).