How to use prepare() with dynamic column names?

✔ Recommended Answer

You could use a list of "approved" values instead, that way you're not really using user data inside a query. Something like this:

$Approved = array ('firstname', 'lastname', 'birthdate') ;$Location = array_search($ColumnName, $Approved) // Returns approved column location as intif($Location !== FALSE) {    // Use the value from Approved using $Location as a key    $Query = $wpdb->Prepare('SELECT ' . $Approved[$Location] . ' FROM myTable WHERE user_id=:userid');    $Query->Execute(array(        :userid => $current_user->user_login    ));    return $Query;} else {    return false;}

Maybe it might be easier to just get all (SELECT * or SELECT a,b,c,d) of the user data and save it to session to use later?


Answered By: Lin Meyer

Method #2

In PHP, you can use parameterized queries to safely and effectively use dynamic column names with prepare(). Here's an example:

<?php // define your dynamic column name $column_name = "your_column_name"; // build your SQL query using parameterized placeholders for the column name and other values $query = "SELECT * FROM your_table WHERE {$column_name} = ?"; // create a connection to your database $pdo = new PDO('pgsql:host=your_host;dbname=your_database_name', 'your_username', 'your_password'); // prepare the query $stmt = $pdo->prepare($query); // bind the parameter to the query and execute it $stmt->execute(['your_parameter_value']); // fetch the results $results = $stmt->fetchAll(); // close the statement and connection $stmt->closeCursor(); $pdo = null; ?>

In this example, we first define our dynamic column name as $column_name. Then, we build our SQL query using parameterized placeholders (?) for the column name and any other values that will be passed as parameters. We then create a PDO connection to our database and prepare the query using $pdo->prepare(). Finally, we bind the parameter to the query using $stmt->execute() and fetch the results using $stmt->fetchAll(). Once we're done, we close the statement using $stmt->closeCursor() and close the connection by setting $pdo to null.


Most Popular

Remove Unicode Zero Width Space PHP

PhpStorm, return value is expected to be 'A', 'object' returned

Laravel file upload returns forbidden 403, file permission is 700 not 755