Create table from array with prepare

Question

Create table from array with prepare

MattWithAHat

2022年5月17日 16:02

Is it possible to create a table with the wpdb::prepare function?

I read the documentation and tried to find examples but there where none that helped me.

Even creating a table with prepare doesn't give me any useful examples on Google.

Currently I'm creating my table like this:

public function createTableFromFields( $tablename, $fields ) {

    //
    $wpdb = $this-db;
    $tablename = $wpdb-prefix . $tablename;

    $sql = 'CREATE TABLE IF NOT EXISTS ' . $tablename . ' (id INT(6) UNSIGNED
        AUTO_INCREMENT PRIMARY KEY';

    foreach ( $fields as $field ) {

        $sql .= ", $field TEXT";

    }

    $sql .= ')';

    $result = $wpdb-query( $sql );

    return $result;

}

But I learned that just using

$wpdb-query($sql)

is unsafe and that you should rather do it with

$wpdb-query(prepare($sql, $args).

So what do I put in $args here and what would be the SQL code then using the query format strings?

Topic mysql wpdb database Wordpress sql

Category Web

Sonali · Accepted Answer · 2017年3月2日 08:44

I tried this in local, and I think you have multiple fields inside $fields so I've added them in array.

Just look at the code below; it works fine as tested:

add_action('your_hook', 'createTableFromFields');
function createTableFromFields($tablename)
{
    $wpdb = $this->db;
    $tablename = $wpdb->prefix . $tablename;
    $fields = array('PersonID','LastName');
    $sql = 'CREATE TABLE IF NOT EXISTS %s (id INT(6) UNSIGNED
        AUTO_INCREMENT PRIMARY KEY';
    $test = array();
    foreach ($fields as $field) {
        $test[] =  $field." TEXT";
    }
    $t = implode(",", $test);
    $sql .= ",%s)";

    $result = $wpdb->query($wpdb->prepare(sprintf($sql, $tablename, $t)));
    return $result;
}

Create table from array with prepare

About