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("/?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)

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)

• php
• wp-query
• query
• mysql
• wpdb

• 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 Commented 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 Commented Jan 25, 2022 at 15:00

1 Answer 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);

    }
          
}