I am trying to work on a API to import data to MySQL Database. I have it working on a small scale.
I have now altered it so its more in line with the current database. Its a large table and been poorly structured, But for simplicity and maintaining am trying to keep MySQL database and the Applications database inline. MSQL = Windows based application: MySQL = The Server database, Am extracting Data out of MSQL hosted locally and importing it to a MySQL Database hosted externally.
As i have expanded the code, its no longer working, this could be a number of factors like the table needs structuring / indexing / primary, or i am passing too much data via a query. The application is starting to look very messy.
The question is, is it necessary and better practice me listing out each column and matching up the data sets apposed to trying to utilize the MySQL INFORMATION_SCHEMA to list the columns instead.
Here is the code so far, I have already removed a number of columns.
We have a Server and Host:
Server Code
Class file: Api.php
class API
{
private $connect = '';
function __construct()
{
$this->database_connection();
}
function database_connection()
{
$this->connect = new PDO("mysql:host=$Host;dbname=$DatabaseName", $DatabaseUsername, $DatabasePassword);
}
function insert() // Insert Function
{
if(isset($_POST["Branch"]))
{
$form_data = array(
':Branch'=>$_POST['Branch'],
':Date'=>$_POST['Date'],
':Week'=>$_POST['Week'],
':Period'=>$_POST['Period'],
':Invoice_No'=>$_POST['Invoice_No'],
':Invoice_Reference'=>$_POST['Invoice_Reference'],
':Line_No'=>$_POST['Line_No'],
':Till_No'=>$_POST['Till_No'],
':Operator'=>$_POST['Operator'],
':Stock_Code'=>$_POST['Stock_Code'],
':Barcode'=>$_POST['Barcode'],
':Line_Quantity'=>$_POST['Line_Quantity'],
':Weight'=>$_POST['Weight'],
':Weight_Unit'=>$_POST['Weight_Unit'],
':Man_Weighed'=>$_POST['Man_Weighed'],
':Unit_ID'=>$_POST['Unit_ID'],
':Line_Price_Band'=>$_POST['Line_Price_Band'],
':Original_Sell'=>$_POST['Original_Sell'],
':Actual_Sell'=>$_POST['Actual_Sell'],
':Cost'=>$_POST['Cost'],
':Vat_Rate'=>$_POST['Vat_Rate'],
':Discount_Rate'=>$_POST['Discount_Rate'],
':Value_Goods'=>$_POST['Value_Goods'],
':Value_VAT'=>$_POST['Value_VAT'],
':Value_Sale'=>$_POST['Value_Sale'],
':Value_Cost'=>$_POST['Value_Cost'],
':Price_Override_Amount'=>$_POST['Price_Override_Amount'],
':Price_Overrided'=>$_POST['Price_Overrided'],
':Discounted'=>$_POST['Discounted'],
':Account_No'=>$_POST['Account_No'],
':Sub_Account_No'=>$_POST['Sub_Account_No'],
':Customer_Account'=>$_POST['Customer_Account'],
':Sub_Customer_Account'=>$_POST['Sub_Customer_Account'],
':Cust_Type_ID'=>$_POST['Cust_Type_ID'],
':Super_Department_ID'=>$_POST['Super_Department_ID'],
':Department_ID'=>$_POST['Department_ID'],
':Group_ID'=>$_POST['Group_ID'],
':Sub_Group_ID'=>$_POST['Sub_Group_ID'],
':Retail_Location_ID'=>$_POST['Retail_Location_ID'],
':Retail_Sub_Location_ID'=>$_POST['Retail_Sub_Location_ID'],
':Entry_Method'=>$_POST['Entry_Method'],
':End_Sale_Discount_Rate'=>$_POST['End_Sale_Discount_Rate'],
':Loyalty_Discount_Rate'=>$_POST['Loyalty_Discount_Rate'],
':Date_Time_Stamp'=>$_POST['Date_Time_Stamp']
);
$query = "
INSERT INTO Lines
(Branch, Date, Week, Period, Invoice_No, Invoice_Reference, Line_No, Till_No, Operator, Stock_Code, Barcode, Line_Quantity, Weight, Weight_Unit, Man_Weighed, Unit_ID, Line_Price_Band, Original_Sell, Actual_Sell, Cost, Vat_Rate, Discount_Rate, Value_Goods, Value_VAT, Value_Sale, Value_Cost, Price_Override_Amount, Price_Overrided, Discounted, Account_No, Sub_Account_No, Customer_Account, Sub_Customer_Account, Cust_Type_ID, Super_Department_ID, Department_ID, Group_ID, Sub_Group_ID, Retail_Location_ID, Retail_Sub_Location_ID, Entry_Method, End_Sale_Discount_Rate, Loyalty_Discount_Rate, Date_Time_Stamp) VALUES
(:Branch, :Date, :Week, :Period, :Invoice_No, :Invoice_Reference, :Line_No, :Till_No, :Operator, :Stock_Code, :Barcode, :Line_Quantity, :Weight, :Weight_Unit, :Man_Weighed, :Unit_ID, :Line_Price_Band, :Original_Sell, :Actual_Sell, :Cost, :Vat_Rate, :Discount_Rate, :Value_Goods, :Value_VAT, :Value_Sale, :Value_Cost, :Price_Override_Amount, :Price_Overrided, :Discounted, :Account_No, :Sub_Account_No, :Customer_Account, :Sub_Customer_Account, :Cust_Type_ID, :Super_Department_ID, :Department_ID, :Group_ID, :Sub_Group_ID, :Retail_Location_ID, :Retail_Sub_Location_ID, :Entry_Method, :End_Sale_Discount_Rate, :Loyalty_Discount_Rate, :Date_Time_Stamp)
";
$statement = $this->connect->prepare($query);
if($statement->execute($form_data))
{
echo $statement ;
$data[] = array(
'success' => '1'
);
}
else
{
var_dump($statement) ;
$data[] = array(
'success' => '0'
);
}
}
else
{
$data[] = array(
'success' => '0'
);
}
return $data;
}
}
Calling the Function: Handler test_api.php
// This does have an include for Api.php
if($_GET["action"] == 'insert')
{
$data = $api_object->insert();
}
Host code
This wont be the final thing, as ill be extracting from MSQL building the array and posting this way, This is just laid out like so for testing
<?php
$form_data = array(
'Branch' => '1',
'Date' => '8/11/2018 13:42:00',
'Week' => '1',
'Period' => '1',
'Invoice_No' => '9999998',
'Invoice_Reference' => '99999998',
'Line_No' => '1',
'Till_No' => '1',
'Operator' => '99',
'Stock_Code' => '123456',
'Barcode' => '654321',
'Line_Quantity' => '99',
'Weight' => '',
'Weight_Unit' => '',
'Man_Weighed' => '',
'Unit_ID' => '',
'Line_Price_Band' => '1',
'Original_Sell' => '99.99',
'Actual_Sell' => '99.99',
'Cost' => '9.99',
'Vat_Rate' => '1',
'Discount_Rate' => '',
'Value_Goods' => '',
'Value_VAT' => '',
'Value_Sale' => '',
'Value_Cost' => '',
'Price_Override_Amount' => '',
'Price_Overrided' => '',
'Discounted' => '',
'Account_No' => '',
'Sub_Account_No' => '',
'Customer_Account' => '',
'Sub_Customer_Account' => '',
'Cust_Type_ID' => '',
'Super_Department_ID' => '',
'Department_ID' => '',
'Group_ID' => '',
'Sub_Group_ID' => '',
'Retail_Location_ID' => '',
'Retail_Sub_Location_ID' => '',
'Entry_Method' => '',
'End_Sale_Discount_Rate' => '',
'Loyalty_Discount_Rate' => '',
'Date_Time_Stamp' => ''
);
$api_url = "localhost/Modules/API/Server/test_api.php?action=insert"; //change this url as per your folder path for api folder
$client = curl_init($api_url);
curl_setopt($client, CURLOPT_POST, true);
curl_setopt($client, CURLOPT_POSTFIELDS, $form_data);
curl_setopt($client, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($client);
// var_dump($response);
curl_close($client);
$result = json_decode($response, true);
echo $response ;
foreach($result as $keys => $values)
{
if($result[$keys]['success'] == '1')
{
echo 'update';
}
else
{
echo 'error';
echo $response ;
}
}
At the moment its not working, I am not sure why, As it was working fine with a much smaller dataset. And my IDE hasnt reported of any errors or typos, But thats something else, if you do spot a error, please do say.
The question is, is there a better way to work with large number of columns.
In previous API's i have basically just dumped the data to txt and imported from a deliminator. So this is all very new to me.
