So I was able to successfully insert entries from a JSON File into a MySQL DB. My issue now is that I only see 10 entries but I have over 200 entries in that file.

Below you will see how I was able to accomplish this. I am not sure if it's something within the DB, or the actual entries themselves causing it to stop at the 10th entry.

In my DB, 'id' is a primary field for the table and I have set to be int(100) I don't have Auto increment on because the ID is passed from the form itself.

Thoughts?

REST API to JSON File

function tiletechv2 () {
    
$consumer_key    = 'key';
$consumer_secret = 'secret';
$url             = 'https://example.com/wp-json/gf/v2/forms/2/entries';
$method          = 'GET';
$args            = array('paging[page_size]'=>'300');
 
// Use helper to get oAuth authentication parameters in URL.
// Download helper library from: https://s22280.pcdn.co/wp-content/uploads/2017/01/class-oauth-request.php_.zip
require_once( 'class-oauth-request.php' );
$oauth = new OAuth_Request( $url, $consumer_key, $consumer_secret, $method, $args );
 
    
    // Make the request to the API.
//$response = wp_remote_get( $url, $consumer_key, $consumer_secret, $method, $args );
 $form = "hi";
// Send request.
$response = wp_remote_request( $oauth->get_url(),
   array(
       'method'  => $method,
       'body'    => json_encode( $form, JSON_PRETTY_PRINT ),
       'headers' => array( 'Content-type' => 'application/json' ),
   )
);
 
// Check the response code.
if ( wp_remote_retrieve_response_code( $response ) != 200 || ( empty( wp_remote_retrieve_body( $response ) ) ) ) {
    // If not a 200, HTTP request failed.
    die( 'There was an error attempting to access the API.' );
}
    
    $body = json_decode( wp_remote_retrieve_body( $response ), true );
    
    $json_string = json_encode($body, JSON_PRETTY_PRINT);
print  $json_string;
print "File has been created/updated! <br />Copy this url into All Import: https://example.com/wp-content/themes/tiletechpavers-child/file.json";
    $file = dirname(__FILE__). '/file.json';
file_put_contents($file, $json_string); 

}
add_shortcode( 'tiletechv2', 'tiletechv2' );

JSON TO MYSQL DB

<?php

$servername = "localhost";
$username = "user";
$password = "pass";
$dbname = "dbname";

// Create connection
$con = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if ($con->connect_error) {
    echo "Connection failed: " . $con->connect_error;
    exit;
}

$query ='';
$table_data = '';
$filename = 'https://example.com/wp-content/themes/tiletechforms-child/file.json';


    //read the json file contents
    $jsondata = file_get_contents($filename);
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    
    foreach ($data['entries'] as $row)
     {
      $query .= "INSERT INTO contact VAlUES ('".$row["id"]."','".$row["date_created"]."','".$row["3.3"]."','".$row["3.6"]."','".$row["1"]."','".$row["5"]."','".$row["6"]."'); ";
      
      $table_data .= '
     <tr>
      <td>'.$row["id"].'</td>
      <td>'.$row["3.3"].'</td>
      </tr>
      ';
     }
      
       if(mysqli_multi_query($con, $query)) {
                echo '<h3>Inserted JSON Data</h3><br />';
                echo '
                <table >
                <tr>
                    <th width="45%">ID</th>
                    <th width="10%">First Name</th>
                    
                </tr>
                ';
                echo $table_data;  
                echo '</table>';
            }
          ?>

Sample of JSON

{
    "total_count": 214,
    "entries": [
        {
            "id": "544542",
            "form_id": "2",
            "post_id": null,
            "date_created": "2022-10-24 14:11:56",
            "date_updated": "2022-10-24 14:11:56",
            "is_starred": "0",
            "is_read": "0",
            "ip": "104.6.79.145",
            "source_url": "https:\/\/example.com\/contact\/",
            "user_agent": "Mozilla\/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/106.0.0.0 Safari\/537.36",
            "currency": "USD",
            "payment_status": null,
            "payment_date": null,
            "payment_amount": null,
            "payment_method": null,
            "transaction_id": null,
            "is_fulfilled": null,
            "created_by": null,
            "transaction_type": null,
            "status": "active",
            "1": "[email protected]",
            "2": "Contractor\/GC",
            "3.3": "First",
            "3.6": "Lasr",
            "4": "The Piso Project",
            "5": "(111) 222-3333",
            "6": "Florida",
            "7": "Needing pricing and eta on certain materials",
            "is_approved": "3",
            "3.2": "",
            "3.4": "",
            "3.8": "",
            "8": "",
            "workflow_current_status_timestamp": false,
            "gpnf_entry_parent": false,
            "gpnf_entry_parent_form": false,
            "gpnf_entry_nested_form_field": false
        },

CodePudding user response：

It's probably a constraint in the database.

Don't use mysqli_multi_query, try using mysqli_query or PDO (better to prevent SQL injection) to insert one query at a time in the foreach loop, and see what triggers the error.

$con = new PDO('mysql:host=localhost;dbname=dbname', $username , $password);
....
foreach ($data['entries'] as $row)
{
     $query = $con->prepare("INSERT INTO contact VALUES (?, ?, ?, ?, ?, ?, ?)");
     $query->execute([$row["id"], $row["date_created"], $row["3.3"], $row["3.6"], $row["1"], $row["5"], $row["6"]]);
}