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.

I am using Wordpress 3.4.2, latest version! I have no plugins installed except the one that I am writing.

So Here's the deal, I'm reading a json feed and i'm trying to import its content into a table that i created in my wordpress database. I've used a similar set of code in another plugin that I wrote and it works fine there. I do not understand why it does not work here.

When i run this different plugin that i am writing i am get the dreaded and oh so clear "WordPress database error: [Query was empty]".

Here is my code...

// Setup the array
$values = array();

foreach ($bugs as $bug) {
 $status_text = $bug['text'];
 $tContent = htmlSpecialChars(html_entity_decode($status_text));
 $imported = 2;

 [ shortened to keep this post short, but other values are listed here. ]


  // for the values insert part
  $values[] = '("'.$tContent. '", "'.$imported.'")';
}

if(is_array($values)) {
   echo "<hr><strong>WHAT TO IMPORT?</strong><br/>";
   var_dump(implode(',', $values));
}

This output all of the values as expected. Everything is wrapped in parentheses and seperate by a comma.

Then i do this...

global $wpdb;
$table_name = $wpdb->prefix . "bugtbl_temp";

$sql = $wpdb->prepare (
    "INSERT INTO $table_name
    (`post_content`, `imported`)
    VALUES ". implode(',', $values) . " ON DUPLICATE KEY UPDATE imported = 1"
);
$wpdb->query($sql); // execute query


$wpdb->print_error(); // any errors?
$wpdb->last_query; // show the qry
$wpdb->flush(); // cleanup

And then when i run this function I get the "WordPress database error: [Query was empty]" and nothing is imported into my table.

I saw on here one other guy was having problems with $wpdb->prepare. Am I using it incorrectly?

share|improve this question
add comment

3 Answers

up vote 0 down vote accepted

Success! I got the wpdb->prepare to work FINALLY and I'd love to share my code snippet here -- Hopefully someone else will find this useful.

My Situation

I was trying to import data from a feed into a custom table from a plugin I am writing. Several websites and even the guys that replied to this thread encouraged me to protect my import queries. However, even after reading the codex, I was not able to get this to work correctly or at all.

Noteworthy Mistakes

A couple of things that I was doing wrong were:

  1. Pulling all values into an array. I didn't need to do this at all.
  2. I was trying to import more than one row of values using only one wpdb->prepare. The solution was to use one wp-db->prepare per row. I moved the wpdb->prepare command into my foreach statement. The thing is sometimes I will get more than one result that I want to import at one time and this does the trick. I will never have more than 20 results, so this should work just fine.

A Solution

This probably isnt the only way to do this, but this is the way I was able to make it to work. Here is the final working code snippet. I put this in my foreach statement. Hope it helps.

foreach ($rows as $row) {

 // I've omitted the part in my where i take all of the values from the query and define variables, but that part would happen here.
// Prepare query
$sql = $wpdb->prepare("
    INSERT INTO $tablename (`ijAuthorID`, `ijDate`, `ijDescription`, `ijTitle`, `ijSlug`, `ijPostType`, `ijImported`, `ijUKID`) 
    VALUES (%d, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE ijImported = 1", 
    $tAuthor, $tPubDate, $tContent, $tTitle, $tPostName, $tPostType, $tImported, $tID
);

// Execute query
$wpdb->query($sql);

 }

Thanks to those guys that answered my original post.

share|improve this answer
add comment

I've had the same error when I forgot to add the last numeric argument in an update statement; Check that your arguments match up with the replacement tokens.

Say you have an sql string like this:

$sql = "update mytable set column1 = %s where id  = %d;"
$wpdb->query($wpdb->prepare($sql, 'foobar'));

The solution is obviously to add the id

$id = 5;
$wpdb->query($wpdb->prepare($sql, 'foobar', $id));
share|improve this answer
    
Thanks for this "real" answer! I will definitely circle back and try this again... –  Robbiegod Oct 22 '12 at 18:09
    
I'm working on this again. So what if you are doing an insert statement, you probably don;t need the $id at the end. –  Robbiegod May 25 '13 at 15:00
add comment

I saw on here one other guy was having problems with $wpdb->prepare. Am I using it incorrectly?

As you can read in the Codex, it's a simple No.

You have to use it like sprintf/printf(). The only allowed inputs are

  • string: %s
  • digit: %d

Example

$sql = $wpdb->prepare( 
     "
        INSERT INTO %s
        (`post_content`, `imported`)
        VALUES %s
        ON DUPLICATE KEY UPDATE imported = 1
     "
    ,"{$wpdb->prefix}bugtbl_temp"
    ,implode( ',', $values )
);

Disclaimer: The ↑ shown query, doesn't necessarily mean that it will give you results, just because you then used the $wpdb->prepare() right.

share|improve this answer
    
I tried what you supplied and it gave more information then what I had before, but it still didnt work. It told me that i had an error with my MySQL statement. I did figure out how to get that to work though, but I am not using $wpdb->prepare. I just made my array and use a regular sql INSERT INTO statement. I trigger it with $wpdb->query($sql); -- Thanks for the help! –  Robbiegod Sep 10 '12 at 1:04
2  
@Robbiegod Fine. Be prepared for a hacked site. Honestly: Don't prepare and you leave your doors wide open. –  kaiser Sep 10 '12 at 11:28
    
Well obviously, we don't want the website to get hacked. I don't see how to use "prepare" though. Like i said i fiddled with your code, but it didnt work - it kept giving me an error. I will come back to this and tighten up the security. –  Robbiegod Sep 28 '12 at 19:15
1  
sigh (......) –  kaiser Sep 28 '12 at 23:41
    
Your disclaimer below your original is kinda throwing me off. You are saying that even if i do everything right, it still might not work? I'm still confused by wpdb->prepare and how to use it (and have it work). Like you said its best to protect your queries, but if use it correctly one would expect it to work. –  Robbiegod May 25 '13 at 15:12
show 2 more comments

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.