Dynamic value in sql query using php

I want to search a certain string in all the columns of different tables, so I am looping the query through every column name. but if i give it as dynamic value it does not seem to work. what is wrong?

<?php

$search = $_POST['search'];

$columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'feedback'";
$columns_result = $conn->query($columns);
$columns_array = array();
if (!$columns_result) {
    echo $conn->error;
} else {
    while ($row = $columns_result->fetch_assoc()) {
        //var_dump($row);
        //echo $row['COLUMN_NAME']."</br>";
        array_push($columns_array, $row['COLUMN_NAME']);
    }
}
var_dump($columns_array);
$row_result = array();
for ($i = 0; $i < count($columns_array); $i++) {
    echo $columns_array[$i] . "</br>";
    $name = "name";
    // $sql = 'SELECT * FROM feedback WHERE "'.$search.'" in ("'.$columns_array[$i].'")';
    $sql = 'SELECT * FROM feedback WHERE ' . $name . ' like "' . $search . '"';
    $result = $conn->query($sql);
    if (!$result) {
        echo "hi";
        echo $conn->error;
    } else {
        foreach ($result as $row) {
            array_push($row_result, $row);
            echo "hey";
        }
    }
}
var_dump($row_result);

I am getting the column names of the table and looping through them because I have so many other tables which I need to search that given string. I don’t know if it is optimal I did not have any other solution in my mind. If someone can tell a good way I will try that.

Answer

It looks to me that you want to generate a where clause that looks at any available nvarchar column of your table for a possible match. Maybe something like the following is helpful to you?

I wrote the following with SQL-Server in mind since at the beginning the question wasn’t clearly tagged as MySql. However, it turns out that with a few minor changes the query work for MySql too (nvarchar needs to become varchar):

$search='%';$tbl='feedback';
if (isset($_POST['search'])) $search = $_POST['search'];
$columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = '$tbl' AND DATA_TYPE ='nvarchar'";
$columns_result = $conn->query($columns);
$columns_array = array();
if(!$columns_result) print_r($conn->errorInfo());
else while ($row = $columns_result->fetch(PDO::FETCH_ASSOC)) 
   array_push($columns_array, "$row[COLUMN_NAME] LIKE ?");

$where = join("n OR ",$columns_array);
$sth = $conn->prepare("SELECT * FROM $tbl WHERE $where");
for ($i=count($columns_array); $i;$i--) $sth->bindParam($i, $search);
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_ASSOC);

print_r($result);

The above is a revised version using prepared statements. I have now tested this latest version using PHP 7.2.12 and SQL-Server. It turned out that I had to rewrite my parameter binding part. Matching so many columns is not a very elegant way of doing queries anyway. But it has been a nice exercise.