How to display the query table of a searched and filtered value?

Question

My search and filter PHP code does not display the query table of a value that I searched and filtered. How do I solve this problem?

PHP

if(isset($_POST['search']))
{
    $valueToSearch = $_POST['valueToSearch'];
    // search in all table columns
    // using concat mysql function
    $query = "SELECT * FROM `details` WHERE CONCAT('id','date_of_entry','region','province_of_provider') LIKE '%".$valueToSearch."%'";
    $search_result = filterTable($query);
}
 else {
    $query = "SELECT * FROM `details`";
    $search_result = filterTable($query);
}

// function to connect and execute the query
function filterTable($query)
{

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";

    $connect = mysqli_connect($servername, $username, $password, $dbname);
    $filter_Result = mysqli_query($connect, $query);
    return $filter_Result;
}

while ($trainee = mysqli_fetch_array($search_result)){

    echo "<tr>";
    echo "<td>".$trainee['id']."</td>"; 
    echo "<td>".$trainee['date_of_entry']."</td>";
    echo "<td>".$trainee['region']."</td>";
    echo "<td>".$trainee['province_of_provider']."</td>";
    echo "</tr>";

}// End while

?>

When I search and filter from the textbox, the table rows does not contain any value including its borders, but the table headers for every column is still present. Also, when I leave the search empty and click on the filter button, all of the table rows contain all the values of all the data found in the database's table.

1st Edit:

I changed my:

$query = "SELECT * FROM `details` WHERE CONCAT('id','date_of_entry','region','province_of_provider') LIKE '%".$valueToSearch."%'";

To this:

$query = "SELECT * FROM `details` WHERE CONCAT_WS(' ','id','date_of_entry','region','province_of_provider') LIKE '%".$valueToSearch."%'";

Still the same, my search result is an empty table but with the table columns headers only.

You should run your mysql query with `EXPLAIN` at the beginning so you can see exactly how the sql is being processed by the database. — Derek Pollard, Jun 12 '18 at 15:07
Possible duplicate of [Using mysql concat() in WHERE clause?](https://stackoverflow.com/questions/303679/using-mysql-concat-in-where-clause) — Artem Ilchenko, Jun 12 '18 at 15:10
https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql — AbraCadaver, Jun 12 '18 at 15:22

score 1 · Accepted Answer · answered Jun 12 '18 at 15:19

1

Replace this part:

$query = "SELECT * FROM `details` WHERE CONCAT('id','date_of_entry','region','province_of_provider') LIKE '%".$valueToSearch."%'";

with that:

$query = "SELECT * FROM `details` WHERE CONCAT(`id`, `date_of_entry`, `region`, `province_of_provider`) LIKE '%".$valueToSearch."%'";

answered Jun 12 '18 at 15:19

Petya

312
2
6

1

I can not believe it but this answer solved my problem! It took me time! Thank you so much for the simplest answer Petya! You highly deserve my thanks! – Genesis Emmanuel Gonzales Jun 12 '18 at 18:23
You are welcome! If you will ever have problems with sql quires split them into smaller chunks and use one of many online mysql resource for testing. Its the quickest way. – Petya Jun 12 '18 at 21:29

How to display the query table of a searched and filtered value?

PHP

1st Edit:

1 Answers1