wpdb prepare: passing varible number of fields as second argument

4 Views

As i want to manage the NULL fields in my db and wordpress functions doesn’t allow to do so, i will need to dynamically generate a query depending on the situation. The problem is that i don’t know how to pass a variable number of fields as second argument! this is what i’ve been tr but it returns an “Empty query” error: 

if ($a == '') {
    $fields = 'b, c';
    $placeholders = "'%s', '%s'";
    $vars = $b . ', ' . c;
} else {
    $fields = 'a, b, c';
    $placeholders = "'%s', '%s', '%s'";
    $vars = $a . ', ' .$b . ', ' . c;
}
global $wpdb;
$wpdb->show_errors();
$query = 'INSERT INTO table (' . $fields . ') VALUES (' . $placeholders . ')';
$wpdb->query($wpdb->prepare($query, $vars));

is there any way to do so (even using $wpdb->insert)?

Post Views: 4

Related posts

Leave a Reply

2 comments

  1. You must use an array for your $vars, so replace

    $vars = $a . ', ' .$b . ', ' . c;

    with 

    $vars = array( $a, $b, $c );

    But I would rather recommend you to use the $wpdb->insert( $table, $data, $format ) method. Then your code example could look like this:

    $data   = array( 'a' => $a, 'b' => $b, 'c' => $c );
$format = array( '%s', '%s', '%s' );

if( empty( $a ) )
{
    $data   = array_slice( $data,   1, 2 );
    $format = array_slice( $format, 1, 2 ); 
} 

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

  2. You are trying (almost) to construct the whole query and then pass it through prepare. That is not right.

    1. prepare works more like sprintf.or vsprintf
    2. Don’t quote your replacement formats– these $s. That won’t work.
    3. The second argument to prepare can be an array, and that is what I’d use here.

    Try this:

    $a = $b = $c = "abc";
if ($a == '') {
    $fields = 'b, c';
    $placeholders = "'%s', '%s'";
    $vars = $b . ', ' . c;
} else {
    $fields = 'a, b, c';
    $placeholders = "%s, %s, %s";
    $vars = array($a,$b,$c);
}
$query = 'INSERT INTO table (' . $fields . ') VALUES (' . $placeholders . ')';
var_dump($query);
var_dump($wpdb->prepare($query, $vars));