database - Create table from array with prepare

admin管理员组

文章数量:1122832

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?

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?

• database
• mysql
• wpdb
• sql

Share Improve this question edited Mar 2, 2017 at 1:32 nyedidikeke 49211 gold badge66 silver badges1515 bronze badges asked Mar 1, 2017 at 21:02 MattWithAHatMattWithAHat 9111 silver badge22 bronze badges 1

• You have to add '%s' inside $sql where $tablename took and pass $wpdb->query(prepare($sql, $tablename , $field). What is iniside foreach loop $fields? – Sonali Commented Mar 2, 2017 at 5:35

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;
}

本文标签： databaseCreate table from array with prepare