1

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?

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
7
  • 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! Commented Sep 10, 2012 at 1:04
  • 2
    @Robbiegod Fine. Be prepared for a hacked site. Honestly: Don't prepare and you leave your doors wide open. Commented Sep 10, 2012 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. Commented Sep 28, 2012 at 19:15
  • 1
    sigh (......) Commented Sep 28, 2012 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. Commented May 25, 2013 at 15:12
2

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));
Improve this answer
2
  • Thanks for this "real" answer! I will definitely circle back and try this again... Commented Oct 22, 2012 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. Commented May 25, 2013 at 15:00
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.