Inserting a MySQL record into a table with an array of field names and an array of values

Question

I see this in the Codex:

$wpdb->insert( $table, $data, $format );

I'm using this type of code and it works:

$wpdb->query( 
    $wpdb->prepare(
        "INSERT INTO `table` (`field1`,`field2`,`field3`,`field4`) VALUES (%d,%d,%d,%s) ", 
        $_REQUEST['field1'], $_REQUEST['field2'], $_REQUEST['field3'], $_REQUEST['field4']
    )
);

I have a form that has 20 optional fields, so I don't want to just insert them all because some may not pass any values. I'd like to check them first and then only add the code to the table if it was filled in.

Can I put that last section:

$_REQUEST['field1'], $_REQUEST['field2'], $_REQUEST['field3'], $_REQUEST['field4']

as an array, so that I could build it dynamically, like this:

$_sqlCode = "";
$_sqlValues = array();
if(isset($_REQUEST['field1']) && $_REQUEST['field1'] != "") {
    if($_sqlCode) { $_sqlCode .= ', `field1`';} else { $_sqlCode = '`field1`';}
    $_field1Value = $_REQUEST['field1'];
    $_sqlValues["$_field1Value"] = $_field1Value;
}

and do that for each of the fields in the form, for the table?

Would it be easier just to have an array built with key=>value, then do a foreach loop, and build the string like that? I don't know if I can put an array into the values section, or how I'd do it.

Answer 1

Yes, you can pass an array of values. This is explained in the codex under Protect Queries Against SQL Injection Attacks

value_parameter (int|string|array) The value to substitute into the placeholder. Many values may be passed by simply passing more arguments in a sprintf()-like fashion. Alternatively the second argument can be an array containing the values as in PHP's vsprintf() function.

$metakey = "Harriet's Adages";
$metavalue = "WordPress' database interface is like Sunday Morning: Easy.";

$wpdb->query( $wpdb->prepare( 
    "
        INSERT INTO $wpdb->postmeta
        ( post_id, meta_key, meta_value )
        VALUES ( %d, %s, %s )
    ", 
        array(
        10, 
        $metakey, 
        $metavalue
    ) 
) );