Skip to content

Instantly share code, notes, and snippets.

@xlanor

xlanor/importall.php

Last active July 12, 2017 03:34
Show Gist options
  • Save xlanor/2ff372de26b94ad990625298da66deaa to your computer and use it in GitHub Desktop.
Save xlanor/2ff372de26b94ad990625298da66deaa to your computer and use it in GitHub Desktop.
Download ZIP
PHP Script to pull 6e data from json strings into mySQL
Raw
importall.php
<?php
/**
* Created by PhpStorm.
* User: jk
* Date: 7/12/2017
* Time: 10:56 AM
* Every vendor has different variables so you need to factor them all in.
*/
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;
include 'connection.php';
set_time_limit(0); //this script takes forever to run due to the number of records to process so we are going to set time as unlimited.
session_start();
$rardir = 'C:\xampp\htdocs\test\rar';
$dir = scandir($rardir);
$path = 'C:\xampp\htdocs\test\rar\extract';
function Delete($path)
{
if (is_dir($path) === true) {
$files = array_diff(scandir($path), array('.', '..'));
foreach ($files as $file) {
Delete(realpath($path) . '/' . $file);
}
return rmdir($path);
} else if (is_file($path) === true) {
return unlink($path);
}
return false;
}
foreach($dir as $archive)
{
if ($archive != ".")//removes that random .
{
if ($archive != "..")//removes that second random ..
{
$stripgz = substr($archive,0,-3);
$p = new PharData('C:\xampp\htdocs\test\rar\\'.$archive);
$p->decompress(); // creates files.tar
$phar = new PharData('C:\xampp\htdocs\test\rar\\'.$stripgz);
$phar->extractTo('C:\xampp\htdocs\test\rar\extract');
$extractdir = 'C:\xampp\htdocs\test\rar\extract\public\data\beaut\data';
$edir = scandir($extractdir);
//removes the tar since we're done with it.
unset($p);
unset($phar);
Phar::unlinkArchive('C:\xampp\htdocs\test\rar\\'.$stripgz);
Phar::unlinkArchive('C:\xampp\htdocs\test\rar\\'.$archive);
foreach($edir as $file)
{
if ($file != ".")//removes that random .
{
if ($file != "..")//removes that second random ..
{
$test = file_get_contents($extractdir.'\\'.$file);
$json_a = json_decode($test, true);//decodes json.
if ($test != "[]")//if file is not empty
{
//if qoo10 product (strpos checks to ensure source:qoo10 exists and filename consists of product),
//reason why you need to hardcode in everything - because the array that you pull from qoo10, lazada, etc are all different
if ((strpos($test, 'source":"qoo10')) && (strpos($file,'product')))
{
$firstcounter = 0;
//foreach Json,
foreach ($json_a as $item)
{
$marketplace_item_id = $json_a[$firstcounter]['idstr'];
$shopname = $json_a[$firstcounter]['shop'];
$marketplacename = $json_a[$firstcounter]['source'];
$fetchtime = $json_a[$firstcounter]['fetch_time'];
$listingurl = $json_a[$firstcounter]['url'];
$secondcounter = 0;
$countitem = count($item["product"]);
$countimages = count($item["images"]);
$shoptitle = $json_a[$firstcounter]['title'];
$descriptiontext = "There is no description text avaliable for this vendor";
$brand = "There is no brand information avaliable for this vendor";
$SKU = "There is no SKU information avaliable for this vendor";
//qoo10 did not provide us with description text.
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//keywords
$countnokeywords = count($item["keywords"]);
$keywordscounter = 0;
foreach($item["keywords"] as $key)
{
$keywordn= $json_a[$firstcounter]['keywords'][$keywordscounter];
//check if keyword already exists for existing itemid
$checkkeywordexistq = "SELECT * FROM Keywords where keyIDStr = :itemid AND Keywords = :keywords";
try
{
$checkkeywordexist = $dbh->prepare($checkkeywordexistq);
$checkkeywordexist->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$checkkeywordexist->bindParam(':keywords',$keywordn,PDO::PARAM_STR);
$checkkeywordexist->execute();
if(!$checkkeywordexist->rowCount() > 0)
{
try
{
$insertkeywordqry = "INSERT INTO keywords VALUES('', :itemid, :Keywords)";
$insertkeywordqry = $dbh->prepare($insertkeywordqry);
$insertkeywordqry->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$insertkeywordqry->bindParam(':Keywords',$keywordn,PDO::PARAM_STR);
$insertkeywordqry->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
$keywordscounter++;
}
$imagecounter = 0;
foreach($item["images"]as $img)
{
$datatype= 'image';
$imgurl = $json_a[$firstcounter]['images'][$imagecounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :imgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgurl',$imgurl,PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype',$datatype,PDO::PARAM_STR);
$checkimgurlx->execute();
if(!$checkimgurlx->rowCount() > 0)
{
try
{
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:imgurl,:imagetype)";
$insertimgurl =$dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl',$imgurl,PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype',$datatype,PDO::PARAM_STR);
$insertimgurl->execute();
}
catch(PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
//by default, if > 0, it will not insert img at all.
$imagecounter++;
}
//qoo10 has a description image.
//It's currently all empty, but Im going to write a section for it too on the assumption that it is not empty
//if the section places the results within an array like lazadas, please take a look @ lazada's description img code
$datatype= 'descriptionimg';
$descimgurl = $json_a[$firstcounter]['description_image'];
if (!empty ($descimgurl))
{
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :descimgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkimgurlx->bindParam(':descimgurl', $descimgurl, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype', $datatype, PDO::PARAM_STR);
$checkimgurlx->execute();
if (!$checkimgurlx->rowCount() > 0)
{
try {
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:descimgurl,:imagetype)";
$insertimgurl = $dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':descimgurl', $descimgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype', $datatype, PDO::PARAM_STR);
$insertimgurl->execute();
}
catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
$imagecounter++;
}
//qoo10 is retarded because their products are listed within a listing. however,
//in the json file, there are some products that have 0 items ?!?!?!
//so you need to check if item = 0.
if($countitem == 0)
{
$productpriceraw = 0;
$productprice = number_format($productpriceraw, 2, '.', '');
$productname = "No Products found!";
try {
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:shoptitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':shoptitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $descriptiontext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $SKU, PDO::PARAM_STR);
$stmt->execute();
}
catch(PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
else //else, if multiple products
{
//json starts first item at 0, so I'm going to start at 0 and ++ from there.
$secondcounter = 0;
foreach($item["product"] as $product)
{
$productpriceraw = $json_a[$firstcounter]['product'][$secondcounter]['price'];
$productprice = number_format($productpriceraw, 2, '.', '');
//we do this to make sure that, ie: if output price is 2.9, it enters database as 2.90 - in case we ever want to
//retrieve, this makes it easier.
$productname = $json_a[$firstcounter]['product'][$secondcounter]['name'];
$secondcounter++;
try {
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:shoptitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':shoptitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $descriptiontext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $SKU, PDO::PARAM_STR);
$stmt->execute();
}
catch(PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
}
$firstcounter++; //you need your counters for your json array decode so please dont delete any of them!
}
}
//end qoo10 script.
//if lazada product
if ((strpos($test, 'source":"lazada')) && (strpos($file,'product')))
{
$firstcounter = 0;
//foreach Json,
foreach ($json_a as $item)
{
$marketplace_item_id = $json_a[$firstcounter]['idstr'];
$shopname = $json_a[$firstcounter]['seller'];
$productpriceraw = $json_a[$firstcounter]['price'];
$productprice = number_format($productpriceraw, 2, '.', '');
$productname = $json_a[$firstcounter]['title'];
$marketplacename = $json_a[$firstcounter]['source'];
$fetchtime = $json_a[$firstcounter]['fetch_time'];
$listingurl = $json_a[$firstcounter]['url'];
$shoptitle = $json_a[$firstcounter]['source']; //as lazada doesnt have shop titles as qoo10, we will use the sellers name as the shop title
$desctext = $json_a[$firstcounter]['description_text'];
$brand = "There is no brand information avaliable for this vendor";
$sku = $json_a[$firstcounter]['SKU'];
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
$keywordscounter = 0;
foreach($item["keywords"] as $key)
{
$keywordn= $json_a[$firstcounter]['keywords'][$keywordscounter];
//check if keyword already exists for existing itemid
$checkkeywordexistq = "SELECT * FROM Keywords where keyIDStr = :itemid AND Keywords = :keywords";
try
{
$checkkeywordexist = $dbh->prepare($checkkeywordexistq);
$checkkeywordexist->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$checkkeywordexist->bindParam(':keywords',$keywordn,PDO::PARAM_STR);
$checkkeywordexist->execute();
if(!$checkkeywordexist->rowCount() > 0)
{
try
{
$insertkeywordqry = "INSERT INTO keywords VALUES('', :itemid, :Keywords)";
$insertkeywordqry = $dbh->prepare($insertkeywordqry);
$insertkeywordqry->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$insertkeywordqry->bindParam(':Keywords',$keywordn,PDO::PARAM_STR);
$insertkeywordqry->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
$keywordscounter++;
}
//image import code and/or checking of duplicate image urls.
$imagecounter = 0;
foreach($item["product_images"]as $img)
{
$datatype= 'image';
$imgurl = $json_a[$firstcounter]['product_images'][$imagecounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :imgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgurl',$imgurl,PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype',$datatype,PDO::PARAM_STR);
$checkimgurlx->execute();
if(!$checkimgurlx->rowCount() > 0)
{
try
{
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:imgurl,:imagetype)";
$insertimgurl =$dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl',$imgurl,PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype',$datatype,PDO::PARAM_STR);
$insertimgurl->execute();
}
catch(PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
$imagecounter++;
}
//lazada has desc imgs.
//if its empty, it will not be an array, if its full it'll be an array.
//please do not delete any counters - you need them for your json as you go deeper into each level.
$descimgurl = $json_a[$firstcounter]['description_images'];
if(!empty($descimgurl))
{ //else if, there are items present in the description image,
//foreach item, check if image url is a duplicate for the item id
$descimgcounter = 0;
$datatype= 'descriptionimg';
foreach($item["description_images"] as $img)
{
$descimgurl = $json_a[$firstcounter]['description_images'][$descimgcounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :descimgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkimgurlx->bindParam(':descimgurl', $descimgurl, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype', $datatype, PDO::PARAM_STR);
$checkimgurlx->execute();
if ($checkimgurlx->rowCount() == 0)
{
try
{
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:descimgurl,:imagetype)";
$insertimgurl = $dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':descimgurl', $descimgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype', $datatype, PDO::PARAM_STR);
$insertimgurl->execute();
}
catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
$descimgcounter++;
}
}
//doing attributes code
//attributes can be empty, in which case its not placed in an array
$attcounter = 0;
if (is_array($item["attribute"])) {
foreach ($item["attribute"] as $img) {
$att = $json_a[$firstcounter]['attribute'][$attcounter];
$attexistq = "SELECT * FROM attributes WHERE attProd_ID = :itemid AND attProdAttribute = :attribute";
$checkattx = $dbh->prepare($attexistq);
$checkattx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkattx->bindParam(':attribute', $att, PDO::PARAM_STR);
$checkattx->execute();
if (!$checkattx->rowCount() > 0) {
try {
$insertattq = "INSERT INTO attributes VALUES ('', :itemid, :attribute)";
$insertattx = $dbh->prepare($insertattq);
$insertattx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertattx->bindParam(':attribute', $att, PDO::PARAM_STR);
$insertattx->execute();
}
catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
}
}
else
{
if(!empty($item["attribute"]))
{
$att = $json_a[$firstcounter]['attribute'];
$attexistq = "SELECT * FROM attributes WHERE attProd_ID = :itemid AND attProdAttribute = :attribute";
$checkattx = $dbh->prepare($attexistq);
$checkattx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkattx->bindParam(':attribute', $att, PDO::PARAM_STR);
$checkattx->execute();
if (!$checkattx->rowCount() > 0) {
try {
$insertattq = "INSERT INTO attributes VALUES ('', :itemid, :attribute)";
$insertattx = $dbh->prepare($insertattq);
$insertattx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertattx->bindParam(':attribute', $att, PDO::PARAM_STR);
$insertattx->execute();
}
catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
}
}
$attcounter ++;
try
{
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:shoptitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':shoptitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $desctext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$stmt->execute();
}
catch(PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
$firstcounter++;
}
}
//end lazada script
//if redmart product
if ((strpos($test, 'source":"redmart')) && (strpos($file,'product')))
{
$firstcounter = 0;
//foreach Json,
foreach ($json_a as $item)
{
$marketplace_item_id = $json_a[$firstcounter]['idstr'];
$shopname = "redmart"; //As Redmart has no sellers, we shall hardcode shopname to redmart.
$productpriceraw = $json_a[$firstcounter]['price'];
$productprice = number_format($productpriceraw, 2, '.', '');
$productname = $json_a[$firstcounter]['title'];
$marketplacename = $json_a[$firstcounter]['source'];
$fetchtime = $json_a[$firstcounter]['fetch_time'];
$listingurl = $json_a[$firstcounter]['url'];
$shoptitle = $json_a[$firstcounter]['title'];
$desctext = $json_a[$firstcounter]['description_text'];
$brand = "There is no brand information avaliable for this vendor";
$sku = "There is no SKU information avaliable for this vendor";
//no attribute aval.
//no product image aval.
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//redmart does not import images.
//I'm still going to write a script for it in case it ever decides to import images.
//image import code and/or checking of duplicate image urls.
$prodimgurl = $json_a[$firstcounter]['product_images'];
if(!empty($prodimgurl))
{
$imagecounter = 0;
foreach ($item["product_images"] as $img) {
$datatype = 'image';
$imgurl = $json_a[$firstcounter]['product_images'][$imagecounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :imgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype', $datatype, PDO::PARAM_STR);
$checkimgurlx->execute();
if ($checkimgurlx->rowCount() == 0)
{
try {
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:imgurl,:imagetype)";
$insertimgurl = $dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype', $datatype, PDO::PARAM_STR);
$insertimgurl->execute();
} catch (PDOException $e) { //else exception. self-explanatory
echo $e->getMessage();
}
}
$imagecounter++;
}
}
//keywords
$countnokeywords = count($item["keywords"]);
$keywordscounter = 0;
foreach($item["keywords"] as $key)
{
$keywordn= $json_a[$firstcounter]['keywords'][$keywordscounter];
//check if keyword already exists for existing itemid
$checkkeywordexistq = "SELECT * FROM Keywords where keyIDStr = :itemid AND Keywords = :keywords";
try
{
$checkkeywordexist = $dbh->prepare($checkkeywordexistq);
$checkkeywordexist->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$checkkeywordexist->bindParam(':keywords',$keywordn,PDO::PARAM_STR);
$checkkeywordexist->execute();
if(!$checkkeywordexist->rowCount() > 0)
{
try
{
$insertkeywordqry = "INSERT INTO keywords VALUES('', :itemid, :Keywords)";
$insertkeywordqry = $dbh->prepare($insertkeywordqry);
$insertkeywordqry->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$insertkeywordqry->bindParam(':Keywords',$keywordn,PDO::PARAM_STR);
$insertkeywordqry->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
$keywordscounter++;
}
try
{
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:stitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':stitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $desctext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$stmt->execute();
}
catch(PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
$firstcounter++;
}
}
//if guardian product
if ((strpos($test, 'source":"guardian')) && (strpos($file,'product')))
{
$firstcounter = 0;
//foreach Json,
foreach ($json_a as $item)
{
$marketplace_item_id = $json_a[$firstcounter]['idstr'];
$shopname = "Guardian"; //As Guardian has no sellers, we shall hardcode shopname to redmart.
$productpriceraw = $json_a[$firstcounter]['price'];
$productprice = number_format($productpriceraw, 2, '.', '');
$productname = $json_a[$firstcounter]['title'];
$marketplacename = $json_a[$firstcounter]['source'];
$fetchtime = $json_a[$firstcounter]['fetch_time'];
$listingurl = $json_a[$firstcounter]['url'];
$productpriceraw = $json_a[$firstcounter]['price'];
$productprice = number_format($productpriceraw, 2, '.', '');
$productname = $json_a[$firstcounter]['product'];
$shoptitle = $json_a[$firstcounter]['title'];
$desctext = $json_a[$firstcounter]['description_text'];
$brand = $json_a[$firstcounter]['brand'];
$sku = "There is no SKU information avaliable for this vendor";
//guardian doesnt even have an image string in the json from me to pull it from.
//thus we're not going to insert an image string for guardian.
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname', $marketplacename, PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
} catch (PDOException $e) {
echo $e->getMessage();
}
//you will have blanks in this field.
//blanks are in normal strings while if it snot blank it'll be set in an array
//screen the json file for arrays.
$att = $json_a[$firstcounter]['attribute'];
if (is_array($att)) {
$attcounter = 0;
foreach ($item["attribute"] as $img) {
$att = $json_a[$firstcounter]['attribute'][$attcounter];
$attexistq = "SELECT * FROM attributes WHERE attProd_ID = :itemid AND attProdAttribute = :attribute";
$checkattx = $dbh->prepare($attexistq);
$checkattx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkattx->bindParam(':attribute', $att, PDO::PARAM_STR);
$checkattx->execute();
if ($checkattx->rowCount() == 0) {
try {
$insertattq = "INSERT INTO attributes VALUES ('', :itemid, :attribute)";
$insertattx = $dbh->prepare($insertattq);
$insertattx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertattx->bindParam(':attribute', $att, PDO::PARAM_STR);
$insertattx->execute();
} catch (PDOException $e) { //else exception. self-explanatory
echo $e->getMessage();
}
}
$attcounter++;
}
} else {
if (!empty ($att)) {
try {
$insertattq = "INSERT INTO attributes VALUES ('', :itemid, :attribute)";
$insertattx = $dbh->prepare($insertattq);
$insertattx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertattx->bindParam(':attribute', $att, PDO::PARAM_STR);
$insertattx->execute();
} catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
}
//keywords
$countnokeywords = count($item["keywords"]);
$keywordscounter = 0;
foreach ($item["keywords"] as $key) {
$keywordn = $json_a[$firstcounter]['keywords'][$keywordscounter];
//check if keyword already exists for existing itemid
$checkkeywordexistq = "SELECT * FROM Keywords where keyIDStr = :itemid AND Keywords = :keywords";
try {
$checkkeywordexist = $dbh->prepare($checkkeywordexistq);
$checkkeywordexist->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkkeywordexist->bindParam(':keywords', $keywordn, PDO::PARAM_STR);
$checkkeywordexist->execute();
if (!$checkkeywordexist->rowCount() > 0) {
try {
$insertkeywordqry = "INSERT INTO keywords VALUES('', :itemid, :Keywords)";
$insertkeywordqry = $dbh->prepare($insertkeywordqry);
$insertkeywordqry->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertkeywordqry->bindParam(':Keywords', $keywordn, PDO::PARAM_STR);
$insertkeywordqry->execute();
} catch (PDOException $e) {
echo $e->getMessage();
}
}
} catch (PDOException $e) {
echo $e->getMessage();
}
$keywordscounter++;
}
try
{
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:stitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':stitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $desctext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$stmt->execute();
} catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
$firstcounter++;
}
}
//end guardian script.
//if t-mall product
if ((strpos($test, 'source":"tmall')) && (strpos($file,'product')))
{
$firstcounter = 0;
//foreach Json,
foreach ($json_a as $item)
{
$marketplace_item_id = $json_a[$firstcounter]['idstr'];
$shopname = "Tmall"; //As Tmall has no sellers, we shall hardcode shopname to Tmall
$productpriceraw = $json_a[$firstcounter]['price'];
$productname = $json_a[$firstcounter]['title'];
$marketplacename = $json_a[$firstcounter]['source'];
$fetchtime = $json_a[$firstcounter]['fetch_time'];
$listingurl = $json_a[$firstcounter]['url'];
$brandcheck = $json_a[$firstcounter]['brand'];
$shoptitle = $json_a[$firstcounter]['title'];
$descriptiontext = "There is no description text avaliable for this vendor";
$sku = "There is no SKU information avaliable for this vendor";
//tmall doesnt even have an attribute string, therefore, we are not going to insert one.
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//tmall has an unsurprisingly bad habit of leaking blanks brand.
//hardcoding it to read blank not provided if not provided.
if (empty ($brandcheck))
{
$brand = "No brand provided by tmall";
}
else
{
$brand = $json_a[$firstcounter]['brand'];
}
//tmall has an unsurprisingly bad habit of leaving price blank.
//instead of hardcoding a string we'll hardcode it as 0.00 in the event we ever need to do calculations.
if (empty($productpriceraw))
{
$productpriceraw = '0';
$productprice = number_format($productpriceraw, 2, '.', '');
}
else
{
$productprice = number_format($productpriceraw, 2, '.', '');
}
//keywords
$countnokeywords = count($item["keywords"]);
$keywordscounter = 0;
foreach($item["keywords"] as $key)
{
$keywordn= $json_a[$firstcounter]['keywords'][$keywordscounter];
//check if keyword already exists for existing itemid
$checkkeywordexistq = "SELECT * FROM Keywords where keyIDStr = :itemid AND Keywords = :keywords";
try
{
$checkkeywordexist = $dbh->prepare($checkkeywordexistq);
$checkkeywordexist->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$checkkeywordexist->bindParam(':keywords',$keywordn,PDO::PARAM_STR);
$checkkeywordexist->execute();
if(!$checkkeywordexist->rowCount() > 0)
{
try
{
$insertkeywordqry = "INSERT INTO keywords VALUES('', :itemid, :Keywords)";
$insertkeywordqry = $dbh->prepare($insertkeywordqry);
$insertkeywordqry->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$insertkeywordqry->bindParam(':Keywords',$keywordn,PDO::PARAM_STR);
$insertkeywordqry->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
$keywordscounter++;
}
//the images portion is going to be slightly complicated.
//For tmall, the way the images operate is slightly different.
//they have 1 main image which appears as a single line and another cluster of images in an array
//if they ever change this just repeat the cluster code for the single image.
//sometimes, both lines can be empty...
//first order of business - settle the regular image.
$imgurl = $json_a[$firstcounter]['image'];
if (!empty ($imgurl))
{
$imgtype= 'image';
$insertimgq = "INSERT INTO images VALUES ('', :itemid, :imgurl, :imgtype)";
$insertimgurl = $dbh->prepare($insertimgq);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgtype', $imgtype, PDO::PARAM_STR);
$insertimgurl->execute();
}
//now time to settle the second image array (if array)
$arrayimg = $json_a[$firstcounter]['images'];
if (is_array($arrayimg))
{
$imagecounter = 0;
foreach ($item["images"] as $img)
{
$datatype = 'image';
$imgurl = $json_a[$firstcounter]['images'][$imagecounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :imgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype', $datatype, PDO::PARAM_STR);
$checkimgurlx->execute();
if ($checkimgurlx->rowCount() == 0) {
try {
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:imgurl,:imagetype)";
$insertimgurl = $dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype', $datatype, PDO::PARAM_STR);
$insertimgurl->execute();
$imagecounter++;
} catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
} else
{
$imagecounter++;
}
}
}
else
{
if (!empty ($arrayimg))
{
//inserts the single line of images if its not empty
$imgtype= 'image';
$insertimgq = "INSERT INTO images VALUES ('', :itemid, :imgurl, :imgtype)";
$insertimgurl = $dbh->prepare($insertimgq);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $arrayimg, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgtype', $imgtype, PDO::PARAM_STR);
$insertimgurl->execute();
}
}
try {
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:shoptitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':shoptitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $descriptiontext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$stmt->execute();
} catch (PDOException $e) { //else exception. self-explanatory
echo $e->getMessage();
}
$firstcounter++;
}
}
//end tmall script.
//begin watsons script
if ((strpos($test, 'source":"watsons')) && (strpos($file,'product'))) {
$firstcounter = 0;
//foreach Json,
foreach ($json_a as $item) {
$marketplace_item_id = $json_a[$firstcounter]['idstr'];
$shopname = "watsons"; //As watsons has no sellers, we shall hardcode shopname to watsons
$productpriceraw = $json_a[$firstcounter]['price'];
$productname = $json_a[$firstcounter]['product'];
$marketplacename = $json_a[$firstcounter]['source'];
$fetchtime = $json_a[$firstcounter]['fetch_time'];
$listingurl = $json_a[$firstcounter]['url'];
$brandcheck = $json_a[$firstcounter]['brand'];
$shoptitle = $json_a[$firstcounter]['title'];
$descriptiontext = $json_a[$firstcounter]['description_text'];
$sku = "There is no SKU information avaliable for this vendor";
//watsons doesnt even have an attribute string, therefore, we do not insert att.
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname', $marketplacename, PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
} catch (PDOException $e) {
echo $e->getMessage();
}
//watsons doesnt normally leave brands blank, but Im just gonna leave this in in case.. because why not.
//hardcoding it to read blank not provided if not provided.
if (empty ($brandcheck)) {
$brand = "No brand provided by Watsons";
} else {
$brand = $json_a[$firstcounter]['brand'];
}
//Watsonshas an unsurprisingly bad habit of leaving price blank. Serious?!
//instead of hardcoding a string we'll hardcode it as 0.00 in the event we ever need to do calculations.
if (empty($productpriceraw)) {
$productpriceraw = '0';
$productprice = number_format($productpriceraw, 2, '.', '');
} else
{
$productprice = number_format($productpriceraw, 2, '.', '');
}
//keywords foreach loop.
$countnokeywords = count($item["keywords"]);
$keywordscounter = 0;
foreach ($item["keywords"] as $key) {
$keywordn = $json_a[$firstcounter]['keywords'][$keywordscounter];
//check if keyword already exists for existing itemid
$checkkeywordexistq = "SELECT * FROM Keywords where keyIDStr = :itemid AND Keywords = :keywords";
try
{
$checkkeywordexist = $dbh->prepare($checkkeywordexistq);
$checkkeywordexist->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkkeywordexist->bindParam(':keywords', $keywordn, PDO::PARAM_STR);
$checkkeywordexist->execute();
if (!$checkkeywordexist->rowCount() > 0)
{
try {
$insertkeywordqry = "INSERT INTO keywords VALUES('', :itemid, :Keywords)";
$insertkeywordqry = $dbh->prepare($insertkeywordqry);
$insertkeywordqry->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertkeywordqry->bindParam(':Keywords', $keywordn, PDO::PARAM_STR);
$insertkeywordqry->execute();
}
catch (PDOException $e)
{
echo $e->getMessage();
}
}
}
catch (PDOException $e)
{
echo $e->getMessage();
}
$keywordscounter++;
}
//watsons places everything in an array so we're just going to grab it out of the array
//now time to settle the second image array (if array)
$arrayimg = $json_a[$firstcounter]['product_images'];
$imagecounter = 0;
foreach ($item["product_images"] as $img)
{
$datatype = 'image';
$imgurl = $json_a[$firstcounter]['product_images'][$imagecounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :imgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype', $datatype, PDO::PARAM_STR);
$checkimgurlx->execute();
if (!$checkimgurlx->rowCount() > 0)
{
try
{
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:imgurl,:imagetype)";
$insertimgurl = $dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype', $datatype, PDO::PARAM_STR);
$insertimgurl->execute();
} catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
$imagecounter++;
}
try {
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:shoptitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':shoptitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $descriptiontext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$stmt->execute();
}
catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
$firstcounter++;
}
}
//end watsons script
//begin JD script
if ((strpos($test, 'source":"jd')) && (strpos($file,'product')))
{
$firstcounter = 0;
//foreach Json,
foreach ($json_a as $item)
{
$marketplace_item_id = $json_a[$firstcounter]['idstr'];
$shopname = "JD"; //As JD has no sellers, we shall hardcode shopname to JD
$productpriceraw = $json_a[$firstcounter]['price'];
$productname = $json_a[$firstcounter]['product_name'];
$marketplacename = $json_a[$firstcounter]['source'];
$fetchtime = $json_a[$firstcounter]['fetch_time'];
$listingurl = $json_a[$firstcounter]['url'];
$brandcheck = $json_a[$firstcounter]['brand'];
$shoptitle = $json_a[$firstcounter]['title'];
$descriptiontext = "There is no description text avaliable for this vendor";
$sku = "There is no SKU information avaliable for this vendor";
//no attribute avaliable for JD thus we shall not insert.
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//although jd doesnt normally leave blanks brand we're just going to leave this in just in case
//because why not?
if (empty ($brandcheck))
{
$brand = "No brand provided by jd";
}
else
{
$brand = $json_a[$firstcounter]['brand'];
}
//same as above, normally no blanks but why not?
//instead of hardcoding a string we'll hardcode it as 0.00 in the event we ever need to do calculations.
if (empty($productpriceraw))
{
$productpriceraw = '0';
$productprice = number_format($productpriceraw, 2, '.', '');
}
else
{
$productprice = number_format($productpriceraw, 2, '.', '');
}
//keywords
$countnokeywords = count($item["keywords"]);
$keywordscounter = 0;
foreach($item["keywords"] as $key)
{
$keywordn= $json_a[$firstcounter]['keywords'][$keywordscounter];
//check if keyword already exists for existing itemid
$checkkeywordexistq = "SELECT * FROM Keywords where keyIDStr = :itemid AND Keywords = :keywords";
try
{
$checkkeywordexist = $dbh->prepare($checkkeywordexistq);
$checkkeywordexist->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$checkkeywordexist->bindParam(':keywords',$keywordn,PDO::PARAM_STR);
$checkkeywordexist->execute();
if(!$checkkeywordexist->rowCount() > 0)
{
try
{
$insertkeywordqry = "INSERT INTO keywords VALUES('', :itemid, :Keywords)";
$insertkeywordqry = $dbh->prepare($insertkeywordqry);
$insertkeywordqry->bindParam(':itemid',$marketplace_item_id,PDO::PARAM_STR);
$insertkeywordqry->bindParam(':Keywords',$keywordn,PDO::PARAM_STR);
$insertkeywordqry->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
$keywordscounter++;
}
//the images portion is going to be slightly complicated.
//its even worse than tmall's image because you have to handle description images too
$imgurl = $json_a[$firstcounter]['image'];
if (!empty ($imgurl))
{
$imgtype= 'image';
$insertimgq = "INSERT INTO images VALUES ('', :itemid, :imgurl, :imgtype)";
$insertimgurl = $dbh->prepare($insertimgq);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgtype', $imgtype, PDO::PARAM_STR);
$insertimgurl->execute();
}
//now time to settle the second image array (if array)
$arrayimg = $json_a[$firstcounter]['images'];
if (is_array($arrayimg))
{
$imagecounter = 0;
foreach ($item["images"] as $img)
{
$datatype = 'image';
$imgurl = $json_a[$firstcounter]['images'][$imagecounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :imgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype', $datatype, PDO::PARAM_STR);
$checkimgurlx->execute();
if ($checkimgurlx->rowCount() == 0)
{
try {
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:imgurl,:imagetype)";
$insertimgurl = $dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype', $datatype, PDO::PARAM_STR);
$insertimgurl->execute();
} catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
$imagecounter++;
}
}
else //ifisnotarray (singleline)
{
if (!empty ($arrayimg))
{
//inserts the single line of images if its not empty
$imgtype= 'image';
$insertimgq = "INSERT INTO images VALUES ('', :itemid, :imgurl, :imgtype)";
$insertimgurl = $dbh->prepare($insertimgq);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $arrayimg, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgtype', $imgtype, PDO::PARAM_STR);
$insertimgurl->execute();
}
}
$descriptionimg = $json_a[$firstcounter]['description_images'];
if (is_array($descriptionimg))
{
$imagecounter = 0;
foreach ($item["description_images"] as $img)
{
$datatype = 'descriptionimg';
$imgurl = $json_a[$firstcounter]['description_images'][$imagecounter];
$imgurlexistq = "SELECT * FROM images WHERE imgProd_ID = :itemid AND imgURL = :imgurl AND imgImage_Type = :imgtype";
$checkimgurlx = $dbh->prepare($imgurlexistq);
$checkimgurlx->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$checkimgurlx->bindParam(':imgtype', $datatype, PDO::PARAM_STR);
$checkimgurlx->execute();
if ($checkimgurlx->rowCount() == 0)
{
try
{
$insertimgurlqry = "INSERT INTO images VALUES ('', :itemid,:imgurl,:imagetype)";
$insertimgurl = $dbh->prepare($insertimgurlqry);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imagetype', $datatype, PDO::PARAM_STR);
$insertimgurl->execute();
}
catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
}
$imagecounter++;
}
}
else //ifisnotarray (singleline)
{
if (!empty ($descriptionimg))
{
//inserts the single line of images if its not empty
$imgtype= 'image';
$insertimgq = "INSERT INTO images VALUES ('', :itemid, :imgurl, :imgtype)";
$insertimgurl = $dbh->prepare($insertimgq);
$insertimgurl->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgurl', $imgurl, PDO::PARAM_STR);
$insertimgurl->bindParam(':imgtype', $imgtype, PDO::PARAM_STR);
$insertimgurl->execute();
}
}
try
{
$insertproductqry = "INSERT INTO Products VALUES('', :itemid,:price,:title,:mktid,:url,:fetchtime,:seller,:shoptitle,:desctext,:brand,:sku)";
$stmt = $dbh->prepare($insertproductqry);
$stmt->bindParam(':itemid', $marketplace_item_id, PDO::PARAM_STR);
$stmt->bindParam(':price', $productprice, PDO::PARAM_STR);
$stmt->bindParam(':title', $productname, PDO::PARAM_STR);
$stmt->bindParam(':mktid', $marketplaceid, PDO::PARAM_INT);
$stmt->bindParam(':url', $listingurl, PDO::PARAM_STR);
$stmt->bindParam(':fetchtime', $fetchtime, PDO::PARAM_STR);
$stmt->bindParam(':seller', $shopname, PDO::PARAM_STR);
$stmt->bindParam(':shoptitle', $shoptitle, PDO::PARAM_STR);
$stmt->bindParam(':desctext', $descriptiontext, PDO::PARAM_STR);
$stmt->bindParam(':brand', $brand, PDO::PARAM_STR);
$stmt->bindParam(':sku', $sku, PDO::PARAM_STR);
$stmt->execute();
}
catch (PDOException $e)
{ //else exception. self-explanatory
echo $e->getMessage();
}
$firstcounter++;
}
}
//end product parsing
//begin review parsing.
//for reviews, we have no reviews from stores (watsons, guardian)
//limited reviews for lazada so there may be bugs due to the lack of formats avaliable to make a comparism
//lack of reviews for redmart (submitted json files, but json files are all empty, thus we cannot proceed as we do not have enough
//information.
if ((strpos($test, 'source":"qoo10')) && (strpos($file,'review')))
{
$firstcounter = 0;
foreach ($json_a as $item)
{
$idstr = $json_a[$firstcounter]['idstr'];
$prodid = $json_a[$firstcounter]['product_id'];
$title = $json_a[$firstcounter]['title'];//code for if empty.
$content = $json_a[$firstcounter]['content'];
$usn = $json_a[$firstcounter]['user_name'];
$sentiment = $json_a[$firstcounter]['sentiment'];//code for if empty
$score = $json_a[$firstcounter]['rating'];
$marketplacename = $json_a[$firstcounter]['source'];
$recommend = $json_a[$firstcounter]['recommend_point'];//code for if empty
$pubdate = $json_a[$firstcounter]['publish_time'];
$fetchdate= $json_a[$firstcounter]['fetch_time'];
$purchdate = "Purchase date is not provided by this vendor";
if (empty ($title))
{
$title = "no title submitted";
}
if (empty($sentiment))
{
$sentiment = "No sentiment submitted";
}
if (empty($reccomend))
{
$recommend = "No recommendation submitted";
}
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//check for duplicate review id
$checkreviewexitq = "SELECT * FROM review WHERE revIDString = :reviewid";
$checkreviewexist = $dbh->prepare($checkreviewexitq);
$checkreviewexist->bindParam(':reviewid',$idstr,PDO::PARAM_STR);
$checkreviewexist->execute();
if (!$checkreviewexist->rowCount() > 0)
{
try
{
$insertreviewq = "INSERT INTO review VALUES(:idstr,:prodid,:title,:content,:usn,:sentiment,:score,:recommend,:marid,:pubdate,:fetchdate,:purchdate)";
$insertreview = $dbh->prepare($insertreviewq);
$insertreview->bindParam(':idstr', $idstr, PDO::PARAM_STR);
$insertreview->bindParam(':prodid', $prodid, PDO::PARAM_STR);
$insertreview->bindParam(':title', $title, PDO::PARAM_STR);
$insertreview->bindParam(':content', $content, PDO::PARAM_STR);
$insertreview->bindParam(':usn', $usn, PDO::PARAM_STR);
$insertreview->bindParam(':sentiment', $sentiment, PDO::PARAM_STR);
$insertreview->bindParam(':score', $score, PDO::PARAM_STR);
$insertreview->bindParam(':recommend', $recommend, PDO::PARAM_STR);
$insertreview->bindParam(':marid', $marketplaceid, PDO::PARAM_STR);
$insertreview->bindParam(':pubdate', $pubdate, PDO::PARAM_STR);
$insertreview->bindParam(':fetchdate', $fetchdate, PDO::PARAM_STR);
$insertreview->bindParam(':purchdate', $purchdate, PDO::PARAM_STR);
$insertreview->execute();
}
catch (PDOException $e)
{
echo $e->getMessage();
}
}
$firstcounter++; //you need this first counter for your json.
}
}
//end qoo10review script
//begin t-mall review script
if ((strpos($test, 'source":"tmall')) && (strpos($file,'review')))
{
$firstcounter = 0;
foreach ($json_a as $item)
{
$idstr = $json_a[$firstcounter]['idstr'];
$prodid = $json_a[$firstcounter]['product_id'];
$title = "Review Title is not provided by this vendor";//code for if empty.
$content = $json_a[$firstcounter]['content'];
$usn = $json_a[$firstcounter]['user_name'];
$sentiment = $json_a[$firstcounter]['sentiment'];//code for if empty
$score = "Score is not provided by this vendor";
$marketplacename = $json_a[$firstcounter]['source'];
$recommend = "Recommendation points is not provided by this vendor";//code for if empty
$pubdate = $json_a[$firstcounter]['publish_time'];
$fetchdate= $json_a[$firstcounter]['fetch_time'];
$purchdate = "Purchase date is not provided by this vendor";
if (empty ($title))
{
$title = "no title submitted";
}
if (empty($sentiment))
{
$sentiment = "No sentiment submitted";
}
if (empty($recommend))
{
$recommend = "No recommendation submitted";
}
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//check for duplicate review id
$checkreviewexitq = "SELECT * FROM review WHERE revIDString = :reviewid";
$checkreviewexist = $dbh->prepare($checkreviewexitq);
$checkreviewexist->bindParam(':reviewid',$idstr,PDO::PARAM_STR);
$checkreviewexist->execute();
if (!$checkreviewexist->rowCount() > 0)
{
try {
$insertreviewq = "
INSERT INTO review VALUES(:idstr,:prodid,:title,:content,:usn,:sentiment,:score,:recommend,:marid,:pubdate,:fetchdate,:purchdate)";
$insertreview = $dbh->prepare($insertreviewq);
$insertreview->bindParam(':idstr', $idstr, PDO::PARAM_STR);
$insertreview->bindParam(':prodid', $prodid, PDO::PARAM_STR);
$insertreview->bindParam(':title', $title, PDO::PARAM_STR);
$insertreview->bindParam(':content', $content, PDO::PARAM_STR);
$insertreview->bindParam(':usn', $usn, PDO::PARAM_STR);
$insertreview->bindParam(':sentiment', $sentiment, PDO::PARAM_STR);
$insertreview->bindParam(':score', $score, PDO::PARAM_STR);
$insertreview->bindParam(':recommend', $recommend, PDO::PARAM_STR);
$insertreview->bindParam(':marid', $marketplaceid, PDO::PARAM_STR);
$insertreview->bindParam(':pubdate', $pubdate, PDO::PARAM_STR);
$insertreview->bindParam(':fetchdate', $fetchdate, PDO::PARAM_STR);
$insertreview->bindParam(':purchdate', $purchdate, PDO::PARAM_STR);
$insertreview->execute();
} catch (PDOException $e)
{
echo $e->getMessage();
}
}
$firstcounter++; //you need this first counter for your json.
}
}
//end tmall review script
//begin JD review script
if ((strpos($test, 'source":"jd')) && (strpos($file,'review')))
{
$firstcounter = 0;
foreach ($json_a as $item)
{
$idstr = $json_a[$firstcounter]['idstr'];
$prodid = $json_a[$firstcounter]['product_id'];
$title = "Review Title is not provided by this vendor";//code for if empty.
$content = $json_a[$firstcounter]['content'];
$usn = $json_a[$firstcounter]['user_name'];
$sentiment = $json_a[$firstcounter]['sentiment'];//code for if empty
$score = $json_a[$firstcounter]['score'];
$marketplacename = $json_a[$firstcounter]['source'];
$recommend = "Recommendation points is not provided by this vendor";//code for if empty
$pubdate = $json_a[$firstcounter]['publish_time'];
$fetchdate= $json_a[$firstcounter]['fetch_time'];
$purchdate = $json_a[$firstcounter]['purchsed_date'];
if (empty ($title))
{
$title = "no title submitted";
}
if (empty($sentiment))
{
$sentiment = "No sentiment submitted";
}
if (empty($recommend))
{
$recommend = "No recommendation submitted";
}
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//check for duplicate review id
$checkreviewexitq = "SELECT * FROM review WHERE revIDString = :reviewid";
$checkreviewexist = $dbh->prepare($checkreviewexitq);
$checkreviewexist->bindParam(':reviewid',$idstr,PDO::PARAM_STR);
$checkreviewexist->execute();
if (!$checkreviewexist->rowCount() > 0)
{
try {
$insertreviewq = "INSERT INTO review VALUES(:idstr,:prodid,:title,:content,:usn,:sentiment,:score,:recommend,:marid,:pubdate,:fetchdate,:purchdate)";
$insertreview = $dbh->prepare($insertreviewq);
$insertreview->bindParam(':idstr', $idstr, PDO::PARAM_STR);
$insertreview->bindParam(':prodid', $prodid, PDO::PARAM_STR);
$insertreview->bindParam(':title', $title, PDO::PARAM_STR);
$insertreview->bindParam(':content', $content, PDO::PARAM_STR);
$insertreview->bindParam(':usn', $usn, PDO::PARAM_STR);
$insertreview->bindParam(':sentiment', $sentiment, PDO::PARAM_STR);
$insertreview->bindParam(':score', $score, PDO::PARAM_STR);
$insertreview->bindParam(':recommend', $recommend, PDO::PARAM_STR);
$insertreview->bindParam(':marid', $marketplaceid, PDO::PARAM_STR);
$insertreview->bindParam(':pubdate', $pubdate, PDO::PARAM_STR);
$insertreview->bindParam(':fetchdate', $fetchdate, PDO::PARAM_STR);
$insertreview->bindParam(':purchdate', $purchdate, PDO::PARAM_STR);
$insertreview->execute();
} catch (PDOException $e) {
echo $e->getMessage();
}
}
$firstcounter++; //you need this first counter for your json.
}
}
//end JD Review script
//begin lazada review script
if ((strpos($test, 'source":"lazada')) && (strpos($file,'review')))
{
$firstcounter = 0;
foreach ($json_a as $item)
{
$idstr = $json_a[$firstcounter]['idstr'];
$prodid = $json_a[$firstcounter]['product_id'];
$title = "Review Title is not provided by this vendor";//code for if empty.
$content = $json_a[$firstcounter]['content'];
$usn = $json_a[$firstcounter]['user_name'];
$sentiment = $json_a[$firstcounter]['sentiment'];//code for if empty
$score = "Score is not provided by this vendor";//score not prov by lazada.
$marketplacename = $json_a[$firstcounter]['source'];
$recommend = "Recommendation points is not provided by this vendor";//code for if empty
$pubdate = $json_a[$firstcounter]['publish_time'];
$fetchdate= $json_a[$firstcounter]['fetch_time'];
$purchdate = "Purchase date is not provided by this vendor";
if (empty ($title))
{
$title = "no title submitted";
}
if (empty($sentiment))
{
$sentiment = "No sentiment submitted";
}
if (empty($recommend))
{
$recommend = "No recommendation submitted";
}
try //convert marketname to marketID from db.
{
$checkmarketnameqry = "SELECT marID FROM marketplace WHERE marName = :marname";
$checkmktstmt = $dbh->prepare($checkmarketnameqry);
$checkmktstmt->bindParam(':marname',$marketplacename,PDO::PARAM_STR);
$checkmktstmt->execute();
$idrow = $checkmktstmt->fetch();
$marketplaceid = $idrow["marID"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//check for duplicate review id
$checkreviewexitq = "SELECT * FROM review WHERE revIDString = :reviewid";
$checkreviewexist = $dbh->prepare($checkreviewexitq);
$checkreviewexist->bindParam(':reviewid',$idstr,PDO::PARAM_STR);
$checkreviewexist->execute();
if (!$checkreviewexist->rowCount() > 0)
{
try {
$insertreviewq = "
INSERT INTO review VALUES(:idstr,:prodid,:title,:content,:usn,:sentiment,:score,:recommend,:marid,:pubdate,:fetchdate,:purchdate)";
$insertreview = $dbh->prepare($insertreviewq);
$insertreview->bindParam(':idstr', $idstr, PDO::PARAM_STR);
$insertreview->bindParam(':prodid', $prodid, PDO::PARAM_STR);
$insertreview->bindParam(':title', $title, PDO::PARAM_STR);
$insertreview->bindParam(':content', $content, PDO::PARAM_STR);
$insertreview->bindParam(':usn', $usn, PDO::PARAM_STR);
$insertreview->bindParam(':sentiment', $sentiment, PDO::PARAM_STR);
$insertreview->bindParam(':score', $score, PDO::PARAM_STR);
$insertreview->bindParam(':recommend', $recommend, PDO::PARAM_STR);
$insertreview->bindParam(':marid', $marketplaceid, PDO::PARAM_STR);
$insertreview->bindParam(':pubdate', $pubdate, PDO::PARAM_STR);
$insertreview->bindParam(':fetchdate', $fetchdate, PDO::PARAM_STR);
$insertreview->bindParam(':purchdate', $purchdate, PDO::PARAM_STR);
$insertreview->execute();
} catch (PDOException $e) {
echo $e->getMessage();
}
}
$firstcounter++; //you need this first counter for your json.
}
}
//end lazada review script
}
}
}
}
Delete($path);
}
}
}
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 4);
echo 'Script completed in '.$total_time.' seconds.';
return true;
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment