I've written a script where i am fetching data from an excel file and on the basis of data creating / updating customers on bigcommerce using api. The problem i am having is that the file size is too large. Although the api rate limit is 20,000 per hour but my script breaks in between 2500 - 3500 api calls. It neither give me an error from api nor a timeout error from browser It just stops. Here is the code.
foreach($rows as $row) {
//print_r($row);continue;
//sleep(2);
$emails = array();
if(isset($row[4]) && $row[4] != '') {
$emails[] = $row[4];
}
if(isset($row[5]) && $row[5] != '') {
$emails[] = $row[5];
}
if(isset($row[6]) && $row[6] != '') {
$emails[] = $row[6];
}
if(isset($row[7]) && $row[7] != '') {
$emails[] = $row[7];
}
echo count($emails)."<br/>";
if($row[8] == 'Lapsed' || $row[8] == 'Dropped') {
//echo "Update customer group id to 6";
for($i=0;$i<count($emails);$i++) {
$query = "SELECT * FROM customers WHERE email='".$emails[$i]."'";
$result = mysqli_query($Conn_db, $query);
if(mysqli_num_rows($result) > 0) {
$record = mysqli_fetch_array($result);
if($record['customer_group_id'] != 6) {
//updating the Customer Group ID Value to “6” for each email
$api_url = $store_url.'customers/'.$record['customer_id'];
//$data = array("first_name" => "Andrew");
$data = array("customer_group_id" => 6);
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json', 'Accept: application/json'));
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'PUT');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,json_encode($data));
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
$query = "UPDATE customers SET customer_group_id=6 WHERE customer_id=".$record['customer_id'];
$result = mysqli_query($Conn_db, $query);
$log .= "Customer group of customer id ".$record['customer_id']." has been set to 6. <br/>";
}
}
}
$query = "SELECT * FROM customers WHERE notes=".$row[1];
$result = mysqli_query($Conn_db, $query);
if(mysqli_num_rows($result) > 0) {
while($record = mysqli_fetch_array($result)) {
if($record['custoemr_group_id'] != 6) {
//updating the Customer Group ID Value to “6” for each email
$api_url = $store_url.'customers/'.$record['customer_id'];
//$data = array("first_name" => "Andrew");
$data = array("customer_group_id" => 6);
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json', 'Accept: application/json'));
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'PUT');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,json_encode($data));
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
$query1 = "UPDATE customers SET customer_group_id=6 WHERE customer_id=".$record['customer_id'];
$result1 = mysqli_query($Conn_db, $query);
$log .= "Customer group of customer id ".$record['customer_id']." has been set to 6. <br/>";
}
}
}
} else if($row[8] == 'Active') { // if standing = Active
if($row[9] == 'Joined') {
for($i=0;$i<count($emails);$i++) {
$query = "SELECT * FROM customers WHERE email='".$emails[$i]."'";
$result = mysqli_query($Conn_db, $query);
if(mysqli_num_rows($result) > 0) {
$record = mysqli_fetch_array($result);
if($record['customer_group_id'] != 5 || $record['notes'] != $row[1] ) {
//updating the Customer Group ID Value to “5” and notes with id for each email
$api_url = $store_url.'customers/'.$record['customer_id'];
//$data = array("first_name" => "Andrew");
$data = array("customer_group_id" => 5, "notes" => $row[1]);
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json', 'Accept: application/json'));
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'PUT');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,json_encode($data));
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
$query = "UPDATE customers SET customer_group_id=5, notes=".$row[1]." WHERE customer_id=".$record['customer_id'];
$result = mysqli_query($Conn_db, $query);
$log .= "Customer group of customer id ".$record['customer_id']." has been set to 5 and notes updated with ".$row[1].". <br/>";
}
} else {
// CREATE CUSTOMER ON BIGCOMMERCE MAKING API CALL
$api_url = $store_url.'customers';
$data = array(
"first_name" => $row[2],
"last_name" => $row[3],
"email" => $emails[$i],
"customer_group_id" => 6,
"notes" => $row[1],
"_authentication" => array(
"password" => $row[1],
"password_confirmation" => $row[1]
)
);
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json', 'Accept: application/json'));
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,json_encode($data));
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
// FETCH LATEST INSERTED CUSTOMER ON BC AND STORE IN OUR DB
$api_url = $store_url.'customers?email='.$emails[$i];
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt( $ch, CURLOPT_HTTPHEADER, array ('Accept: application/json', 'Content-Length: 0') );
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'GET');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
$customers = json_decode($response);
//echo "<pre>";
//print_r($customers);
if(!isset($customers[0]->status)) {
foreach($customers as $customer) {
$query = "INSERT INTO customers (customer_id, company, first_name, last_name, email, phone, date_created, date_modified, store_credit, registration_ip_address, customer_group_id, notes, tax_exempt_category, addresses_url, addresses_resource)
VALUES(".$customer->id.",'".$customer->company."','".$customer->first_name."','".$customer->last_name."','".$customer->email."','".$customer->phone."','".$customer->date_created."','".$customer->date_modified."','".$customer->store_credit."','".$customer->registration_ip_address."','".$customer->customer_group_id."','".$customer->notes."','".$customer->tax_exempt_category."','".$customer->addresses->url."','".$customer->addresses->resource."')";
$result = mysqli_query($Conn_db, $query);
}
}
$log .= "A customer with id ".$customer->id." has been created. <br/>";
}
}
$query = "SELECT * FROM customers WHERE notes=".$row[1];
$result = mysqli_query($Conn_db, $query);
if(mysqli_num_rows($result) > 0) {
while($record = mysqli_fetch_array($result)) {
if(in_array($record['email'], $emails)) {
continue;
} else {
if($record['customer_group_id'] != 5) {
//UPDATE RECORD ON BIGCOMMERCE USING API CALL
$api_url = $store_url.'customers/'.$record['customer_id'];
//$data = array("first_name" => "Andrew");
$data = array("customer_group_id" => 5);
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json', 'Accept: application/json'));
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'PUT');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,json_encode($data));
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
$query1 = "UPDATE customers SET customer_group_id=5 WHERE customer_id=".$record['customer_id'];
$result1 = mysqli_query($Conn_db, $query);
$log .= "Customer group of customer id ".$record['customer_id']." has been set to 5. <br/>";
}
}
}
}
} else if($row[9] == 'Renewal' || $row[9] == 'Rejoined' || $row[9] == 'Upgraded' || $row[9] == 'Downgraded') {
for($i=0; $i<count($emails);$i++) {
$query = "SELECT * FROM customers WHERE email='".$emails[$i]."'";
$result = mysqli_query($Conn_db, $query);
if(mysqli_num_rows($result) > 0) {
$record = mysqli_fetch_array($result);
if($record['customer_group_id'] != 5 || $record['notes'] != $row[1]) {
//UPDATE RECDORD IN LOCAL DATABASE
$q = "UPDATE customers SET notes = ".$row[1].", customer_group_id=5
WHERE customer_id=".$record['customer_id'];
$r = mysqli_query($Conn_db, $query);
//UPDATE RECORD ON BIGCOMMERCE USING API CALL
$api_url = $store_url.'customers/'.$record['customer_id'];
//$data = array("first_name" => "Andrew");
$data = array("customer_group_id" => 5, "notes" => $row[1]);
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json', 'Accept: application/json'));
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'PUT');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,json_encode($data));
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
$log .= "Customer group of customer id ".$record['customer_id']." has been set to 6 and the customer notes has been updated to ".$row[1].". <br/>";
}
} else {
// CREATE CUSTOMER ON BIGCOMMERCE MAKING API CALL
$api_url = $store_url.'customers';
//$data = array("first_name" => "Andrew");
$data = array(
"first_name" => $row[2],
"last_name" => $row[3],
"email" => $emails[$i],
"customer_group_id" => 6,
"notes" => $row[1],
"_authentication" => array(
"password" => $row[1],
"password_confirmation" => $row[1]
)
);
//$data = array("customer_group_id" => 5, "notes" => 'test');
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json', 'Accept: application/json'));
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,json_encode($data));
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
// FETCH LATEST INSERTED CUSTOMER ON BC AND STORE IN OUR DB
$api_url = 'https://store-h9bfkoe.mybigcommerce.com/api/v2/customers?email='.$emails[$i];
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $api_url );
curl_setopt( $ch, CURLOPT_HTTPHEADER, array ('Accept: application/json', 'Content-Length: 0') );
curl_setopt( $ch, CURLOPT_VERBOSE, 0 );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, 'GET');
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, 0 );
curl_setopt( $ch, CURLOPT_USERPWD, "dev:c8780bee7ad7f80da4664d5d2f189b4f7bf9a64c" );
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
$response = curl_exec( $ch );
var_dump($response);
$api_call++;
echo "API CALL # ".$api_call." <br/>";
$customers = json_decode($response);
//echo "<pre>";
//print_r($customers);
if(!isset($customers[0]->status)) {
foreach($customers as $customer) {
$query = "INSERT INTO customers (customer_id, company, first_name, last_name, email, phone, date_created, date_modified, store_credit, registration_ip_address, customer_group_id, notes, tax_exempt_category, addresses_url, addresses_resource)
VALUES(".$customer->id.",'".$customer->company."','".$customer->first_name."','".$customer->last_name."','".$customer->email."','".$customer->phone."','".$customer->date_created."','".$customer->date_modified."','".$customer->store_credit."','".$customer->registration_ip_address."','".$customer->customer_group_id."','".$customer->notes."','".$customer->tax_exempt_category."','".$customer->addresses->url."','".$customer->addresses->resource."')";
$result = mysqli_query($Conn_db, $query);
$log .= "A customer with customer id ".$customer->id." has been created. <br/>";
}
}
}
}
}
}
}
Can someone please tell me what could be the possible reason of this anomaly?
