Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

What is the consensus on how to insert variables in a MySQL query using the $wpdb->insert syntax: ($table, array($column=>$value),$format)?

I know this hasn't worked:

$wpdb->update('wp_mytable',
array('product'=>"$product"));

Nor this:

$wpdb->update('wp_mytable',
array('product'=>$product));

Nor this:

$insert_row = $wpdb->query($wpdb->prepare("INSERT INTO wp_mytable
                                                       ( product )
                                                       VALUES (%s)
                                                       ",
                                                       $product));

Where echoing $product indeed echoes the string I want to insert.

The other syntax that has worked for me in the past (but not via the $wpdb syntax) is:

INSERT INTO wp_mytable ('product') VALUES '%s', $product;

I'm still getting a blank field value in the database with all above methods. The row is created but the value is missing.

-- Update

$insert_row = $wpdb->insert( 'wp_mytable',
                                        array( 'product' => $product,
                                                'votes' => 1),
                                        array(  '%s', '%d' )
                                        );

This still produces a blank entry under the product column. The vote is counted for the row but the product field is left blank.

This is how I declare $product:

$product = wp_get_object_terms($post->ID,'product');
$product = strval($product[0]->name);

I'm selecting the first object term's name and making sure it's a string.

Also what's interesting about the update query is that although the WHERE clause has its own array, it requires an AND for multiple clauses. This is confusing if you are declaring variables for the WHERE parameters:

$update_vote = $wpdb->update( 'wp_mytable',
                                         array( 'votes' => $new_votes ),
                                         array( 'product' => $product
                                                'company' => $company), // WHERE DOES 'AND' GO?
                                         array( '%d' ),
                                         array( '%s', '%s' )


                                         );
share|improve this question
    
The $where parameter of $wpdb->update() does not require an "AND". This is a misconception. The method will join those where clauses by an "AND". From the codex: A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw". –  Johannes Pille Jun 12 '12 at 18:43
    
As for the empty DB field: What does var_dump($product); yield and what data type have you chosen for the 'product' column in the DB? Also, if this is now about the usage of wp_get_object_terms() it should have been asked in a new question, btw. –  Johannes Pille Jun 12 '12 at 18:53
    
var_dump returns 'string(7) "Product Name"' –  AlxVallejo Jun 12 '12 at 18:58
    
I posted the question here: wordpress.stackexchange.com/questions/55023/… –  AlxVallejo Jun 12 '12 at 19:17
add comment

1 Answer

up vote 1 down vote accepted

To insert a new row:

global $wpdb;
$wpdb->insert( 
    'wp_mytable',
    array( 'product' => $product ),
    array( '%s' ) 
);

For the sake of completeness, $wpdb->update is used to update an existing row and requires an additional WHERE clause, as would a "regular" SQL query. For example:

global $wpdb;
$wpdb->update( 
    'wp_mytable',
    array( 'product' => $product ),
    array( 'product_id' => 1312 ),
    array( '%s' ),
    array( '%d' )
);
share|improve this answer
    
Thanks for this. I'm still facing the same problem however. $product is still blank even after I confirm that it's a string: $product= wp_get_object_terms($post->ID,'product'); $product= strval($product[1]->name); –  AlxVallejo Jun 12 '12 at 17:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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