Querying MySQL table using dynamic variables

Question

I am trying to Query a MySQL table to to bring any result that matches data that the user has input. The database,table and column names are also dynamically stored in variables. var_dump produces a bool(false) which means my query is wrong.

My Code

if (isset ( $_POST ['name'] )) {
            $name = trim ( $_POST ['name'] );
            $tblName = $_REQUEST ['tbl'];
            $colqry = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$dbName' AND TABLE_NAME = '$tblName'";
            echo "<ul>";
            $col_names = mysqli_query ( $link, $colqry );
            while ( $col = mysqli_fetch_array ( $col_names, MYSQL_ASSOC ) ) {
                $colName = $col ['COLUMN_NAME'];
                $tblQry = "SELECT * FROM $tblName WHERE $colName=$name LIMIT 10";
                $query2 = mysqli_query ($link, $tblQry);
                echo $query2;
                while ( $query3 = mysqli_fetch_array ( $query2 ) ) {

                    echo "<li onclick=fill'" . $query3 [0] . "'>" . $query3 [0] . "</li>";
                }
            }
        }

What I want to achieve is list a table where the search terms matches something on the table either the column name or the data inside the columns

Add error reporting to the top of your file(s) right after your opening PHP tag for example <?php error_reporting(E_ALL); ini_set('display_errors', 1); then the rest of your code, to see if it yields anything, as well as or die(mysqli_error($link)) to mysqli_query(). You'll see errors in your query. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 18, 2015 at 11:53
WHERE '$colName'='%$name%' you're looking to use LIKE when using % signs. I.e.: WHERE '$colName' LIKE '%$name%'. Otherwise, do WHERE '$colName'='$name' for an exact match. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 18, 2015 at 11:54
If i use LIKE it will list all the data in that column, but I want to specific data in the column not the actual column name. — b0w3rb0w3r
– b0w3rb0w3r, Commented Mar 18, 2015 at 11:55
You're quoting the table and column names as if they were strings, e.g. SELECT * FROM '$tblName' WHERE '$colName'='%$name%' — TobyLL
– TobyLL, Commented Mar 18, 2015 at 11:55

Funk Forty Niner · Accepted Answer · 2015-03-18 12:41:55Z

2

This line:

$tblQry = "SELECT * FROM $tblName WHERE $colName=$name LIMIT 10";

Quote the $name variable:

so it reads as WHERE $colName='$name'

You can then use $query3[$colname] to get the search match you're looking for.

For more information on identifer qualifiers, visit:

http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

answered Mar 18, 2015 at 12:41

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying MySQL table using dynamic variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related