1

Im trying to insert bulk post meta in a single query, however when it wont insert data if i use variable on it.

Here are my codes so far. I added comments on which codes work & which does not.

(Please ignore no sanitization/prepared, it's a localhost test project):

$imdbid = $_GET['id'];
$string = file_get_contents("http://www.omdbapi.com/?i=".$imdbid."&apikey=9a187152");
$json = json_decode($string, true);

if( $json['Response'] === "True" ){    

    $dataID = $json['imdbID'];
    $dataTitle = $json['Title'];
    $dataYear = $json['Year'];

    $dbdata = array(
        'post_title'    => $dataID,
        'post_status'   => 'publish',
        'post_type' => 'datadb', 
        'post_author'   => 1
                );
          
        
    if( $dbid = wp_insert_post( $dbdata ) ){

        //ECHO TEST TO MAKING SURE THE VARIABLES ARE WORKING 
        
        echo $dataID;
        echo $dataTitle;
        echo $dataYear;
        (VARIABLES ARE WORKING)


        //#1 THIS DOES NOT WORK 
        $wpdb->query("INSERT INTO wp_postmeta
        (post_id, meta_key, meta_value)
        VALUES
        ($dbid, 'datadb_imdb_id', $dataID),
        ($dbid, 'datadb_title', $dataTitle),
        ($dbid, 'datadb_year', $dataYear);");


        //#2 BUT THIS WORK
        $wpdb->query("INSERT INTO wp_postmeta
        (post_id, meta_key, meta_value)
        VALUES
        ($dbid, 'datadb_imdb_id', 'tt6334354'),
        ($dbid, 'datadb_title', 'The Suicide Squad'),
        ($dbid, 'datadb_year', '2021');");

    }
          
}

Why is it when i use variable as values for my custom sql query nothing gets inserted? (Refer to #1)

But when i enter it directly, it does. (Refer to #2)

2
  • Can you update your question please will all methods you tried (e.g. with quotation marks)? Also, if you assign the query to a variable first, echo that - can you run this version? (Like $query = "INSERT INTO ..."; echo $query; then in next run try that output.) Does $wpdb->last_error (doc) have any useful information?
    – kero
    Jan 25, 2022 at 12:44
  • @kero I followed your suggestion and made my statement as variable first before using it on wpdb->query() and it worked perfectly! Thank you very much!!
    – Amenadiel
    Jan 25, 2022 at 15:00

1 Answer 1

1

Huge thanks to @kero !!

I was finally able to create a solution by making the sql statement a variable first (as kero suggested) then using that variable on wpdb->query();

Here's my updated and fully working codes now:

$imdbid = $_GET['id'];
$string = file_get_contents("http://www.omdbapi.com/?i=".$imdbid."&apikey=9a187152");
$json = json_decode($string, true);

if( $json['Response'] === "True" ){    

    $dataID = $json['imdbID'];
    $dataTitle = $json['Title'];
    $dataYear = $json['Year'];

    $dbdata = array(
        'post_title'    => $dataID,
        'post_status'   => 'publish',
        'post_type' => 'datadb', 
        'post_author'   => 1
                );
          
        
    if( $dbid = wp_insert_post( $dbdata ) ){

        $statement = "INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES ('".$dbID."', 'datadb_imdb_id', '%s'), ('".$dbID."', 'datadb_title', '%s');";                                      
        $sql = $wpdb->prepare($statement, $dataID, $dataTitle);
        $wpdb->query($sql);

    }
          
}
3
  • You really should use the post meta API instead, e.g. add_post_meta() for adding post meta. And actually, you could just use the meta_input argument for wp_insert_post(). Either way, they are better than using custom SQL queries - e.g. plugins would be able to use post meta hooks to run their specific actions when a post meta is added.
    – Sally CJ
    Jan 25, 2022 at 15:14
  • @SallyCJ Hi there! I thought of using add_post_meta/update_post_meta but i notice it's slower compared to using a custom sql on wpdb when importing bunch of post meta.
    – Amenadiel
    Jan 26, 2022 at 9:00
  • 1
    Even so, you should still use the API. And if you're concerned about timeouts, then you could split the items in the queue into batches, e.g. 20 items per batch.. (the actual value depends on your server and site setup, and your code) and either manually trigger the batches or you could automate the whole process using background jobs - e.g. create a WP-Cron job or use a library like Action Scheduler.
    – Sally CJ
    Jan 26, 2022 at 14:33

Your Answer

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

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