2

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)?

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 );
  • if i understood your question, there's an error: it should be $data = array_slice( $data, 1, 2 ); and $format = array_slice( $format, 1, 2 ); right? as if $a is at the first position, if it is missing the slice should start from position 1 right? – Mariano Mar 13 '14 at 14:40
  • @Mariano, yes you're correct, I was thinking about a and b but you wanted b and c. I updated the answer. – birgire Mar 13 '14 at 15:00
  • 1
    that's ok, it means i correctly understood your suggestion ;) – Mariano Mar 13 '14 at 15:07
1

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));
  • thanks for your suggestions! anyway i will try birgire's solution as it uses the wpdpb insert function and it seems to me to be a more "official" way (i know they're both official) – Mariano Mar 13 '14 at 14:41
  • Choose the method you prefer, of course, but I never use the 'insert' wrapper-- helper function overkill, frankly. – s_ha_dum Mar 13 '14 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.