Php pdo execute query from mysql variable

Question

I am creating dynamic mysql query where I need to select * column except primary key column. Then , I need to get data of this column in php using pdo.

$qry_getData="SELECT
               GROUP_CONCAT(COLUMN_NAME)FROM
              information_schema. COLUMNS WHERE
               TABLE_SCHEMA = 'db1'AND 
               TABLE_NAME = 'books' AND 
               COLUMN_KEY <> 'PRI' INTO @columnName;";
$stmt_getData   =$conn->prepare($qry_getData);
$stmt_getData->execute();

$qry_getData="SET @query=CONCAT('SELECT ', @columnName , ' FROM `books`');";
$stmt_getData   =$conn->prepare($qry_getData);
$stmt_getData->execute();

$qry_getData="SELECT @query";
$stmt_getData   =$conn->prepare($qry_getData);
$stmt_getData->execute();
$data_getData   =$stmt_getData->fetchAll(PDO::FETCH_ASSOC);

output:

array(1) {
 [0]=>
  array(1) {
    ["@query"]=>
      string(70) "SELECT ColA,ColB,ColC,ColD FROM `books`"
  }
 }

I expecting the data from this column as output. But, it output the sql query that has been created

Answer 1

I'm not sure it's worth going to all this trouble, because wouldn't it be simpler to just run this query:

$qry_getData    ="SELECT * FROM `books`";
$stmt_getData   =$conn->query($qry_getData);
$data_getData   =$stmt_getData->fetchAll(PDO::FETCH_ASSOC);

But I will answer the question you asked, trusting that you have a good reason for omitting the primary key column from the result.

Why did you get the query itself as a result, instead of data that that query would have produced?

You defined a string variable named @query and then selected that variable. The fact that it happens to contain a string that is an SQL query is immaterial; it's still just a string value.

How to get what you wanted, which is the result of all columns from that table except for the primary key column?

To execute that variable as a string, you could use the PREPARE statement. But it takes more steps:

$qry_getData    ="SET @query=CONCAT('SELECT ', @columnName , ' FROM `books`');";
$stmt_getData   =$conn->exec($qry_getData);

$qry_getData    ="PREPARE stmt FROM @query";
$stmt_getData   =$conn->exec($qry_getData);

$qry_getData    ="EXECUTE stmt";
$stmt_getData   =$conn->query($qry_getData);
$data_getData   =$stmt_getData->fetchAll(PDO::FETCH_ASSOC);

$qry_getData    ="DEALLOCATE PREPARE stmt";
$stmt_getData   =$conn->exec($qry_getData);

(There is no need to use PDO::prepare() in these cases, since the SQL statements have no parameters.)

This is technically SQL injection, since you don't know if the column names contain special keywords, punctuation, whitespace, etc. If you are not in control of the column names to prevent them from causing SQL injection, then you should take care to delimit them.

Then simply return the query and then execute it.

$qry_getData="SELECT
               GROUP_CONCAT(CONCAT('`', REPLACE(COLUMN_NAME, '`', ''), '`'))FROM
              information_schema. COLUMNS WHERE
               TABLE_SCHEMA = 'db1'AND 
               TABLE_NAME = 'books' AND 
               COLUMN_KEY <> 'PRI' INTO @columnName;";
$rowsAffected   =$conn->exec($qry_getData);

$qry_getData    ="SELECT CONCAT('SELECT ', @columnName , ' FROM `books`') AS `query`;";
$stmt_getData   =$conn->query($qry_getData);
$data_getData   =$stmt_getData->fetchAll(PDO::FETCH_ASSOC);

$stmt->getData  =$conn->query($data_getData["query"]);
$data_getData   =$stmt_getData->fetchAll(PDO::FETCH_ASSOC);