save data to SQL and also display them according to json array count

Question

First of all pardon me, because my language and coding skills are not that great.

I am trying to create a coupon code website, it has a products section too. I was trying to copy data from Flipkart API and save it into SQL database. It works, but the problem is the Json array count/value is different for all products. For example, if a "gadget1" has "3" offers, it will have "3" arrays under json value "offers". if a "gadget2" has "5" offers, it will have "5" arrays under json value "offers".

I am not able to save these data to databse, because of the different count. Here what I tried and it will give you an idea about my codes.

    $url = 'https://affiliate-api.flipkart.net/affiliate/1.0/product.json?id=' . $flipkartProductId;
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Fk-Affiliate-Id: ID', 'Fk-Affiliate-Token: TOKEEN'));
    $json = curl_exec($ch);
    curl_close($ch);
    $json = json_decode($json, true);
    $productid = $flipkartProductId;
    $title = $json['productBaseInfoV1']['title'];
    $price = $json['productBaseInfoV1']['flipkartSpecialPrice']['amount'];
    $cod = $json['productBaseInfoV1']['codAvailable'];
    $stock = $json['productBaseInfoV1']['inStock'];
    $imgurl200 = $json['productBaseInfoV1']['imageUrls']['200x200'];
    $sql = "UPDATE Flipdata SET title='$title', price='$price', time='$timestamp', image1='$imgurl200',  WHERE id='$id'";
    if ($conn->query($sql) === TRUE) { 
    } else {}

The above code is saving data to database, no issues, I am also able to count arrays using

    $offerz = $json['productBaseInfoV1']['offers'];
    $offersize = sizeof($offerx);

The above code does display exactly how many numbers of arrays are inside the "offers" section. It goes like the following

    [offers] => Array ( [0] => No Cost EMI on Bajaj Finserv EMI Card [1] => No Cost EMI with HDFC Bank Credit Cards [2] => 10% Instant Discount* with Axis Bank Cards [3] => Extra 5% off* with Axis Bank Buzz Credit Card )

I just want to save all available offers in SQL database, and also show an php echo off the individual offers.

Only the "Create new Data/Updation" has problem. I am able to use the following code to read data from the database, and it will show nothing if there is no data.

    if (!empty($row['offer1'])) {$offer1= $row["offer1"];} else {$offer1="";}
    if (!empty($row['offer2'])) {$offer2= $row["offer2"];} else {$offer2= "";}
    if (!empty($row['offer3'])) {$offer3= $row["offer3"];} else {$offer3= "";}
    if (!empty($row['offer4'])) {$offer4= $row["offer4"];} else {$offer4= "";}
    if (!empty($row['offer5'])) {$offer5= $row["offer5"];} else {$offer5= "";}

I have even tried the following, it saves data to database, but I am getting error about undefined value "offerx" in php echo.

        $offerx = $json['productBaseInfoV1']['offers'];

            $offersize = sizeof($offerx);

            if($offersize == 0 ) {


            $sql = "UPDATE Flipkart SET title='$title', price='$price', time='$timestamp', image1='$imgurl200' WHERE id='$idd'";
            if ($conn->query($sql) === TRUE) {


        } else {

        }} 
        else if($offersize == 1 ) {
        $offer0 = $json['productBaseInfoV1']['offers']['0'];
        $sql = "UPDATE Flipkart SET title='$title', price='$price', time='$timestamp', image1='$imgurl200',  offer1='$offer0' WHERE id='$id'";
        if ($conn->query($sql) === TRUE) {

        } else {

    }} 
    else if($offersize == 2 ) {
    $offer0 = $json['productBaseInfoV1']['offers']['0'];
    $offer1 = $json['productBaseInfoV1']['offers']['1'];
    $sql = "UPDATE Flipkart SET title='$title', price='$price', time='$timestamp', image1='$imgurl200', offer1='$offer0', offer2='$offer1' WHERE id='$id'";
    if ($conn->query($sql) === TRUE) {

    } else {... and so on...

table "Flipkart", offer 1 will go into "offer1", you can see the" UPDATE Flipkart SET title='$title', price='$price', time='$timestamp', ima..." code in the end... — Shijil, Feb 19 '19 at 05:07
Please fix your [SQL Injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). It could be the cause of your problem. — danblack, Feb 19 '19 at 05:11

score 0 · Answer 1 · answered Feb 19 '19 at 05:57

0

I did not get the exact issue. but i can give you following suggestion by review your code.

1.check key offers is exist using array_key_exist before calling $json['productBaseInfoV1']['offers'] 2.Instead of checking offer size and insert one by one, you can use a foreach loop.

and one more thing first you have initialized a variable as

$offerz and you are getting count of $offerx.

see below image that i have copy from your code.

answered Feb 19 '19 at 05:57

Nasik Ahd

778
1
9
22

$offerz and $offerx are different because, I edited it before posting here. Original code was OK. Thanks for the reply. I will test your suggestions. – Shijil Feb 19 '19 at 07:15

score 0 · Accepted Answer · answered Feb 19 '19 at 06:06

You can do the following, no need to check the count of the offers coming from the api.

$offerx = $json['productBaseInfoV1']['offers'];

$offer0 = empty($offerx[0]) ? '' : $offerx[0];
$offer1 = empty($offerx[1]) ? '' : $offerx[1];
$offer2 = empty($offerx[2]) ? '' : $offerx[2];
$offer3 = empty($offerx[3]) ? '' : $offerx[3];
$offer4 = empty($offerx[4]) ? '' : $offerx[4]; 

$sql = "UPDATE Flipkart SET title='$title', price='$price', time='$timestamp', image1='$imgurl200', offer1='$offer0', offer2='$offer1', offer3='$offer2', offer4='$offer3', offer5='$offer4' WHERE id='$id'";
if ($conn->query($sql) === TRUE) {

} else {

}

Just define the 5 variables for store offers and assign the values to it if available. Set null value otherwise.

Very Important: Please fix SQL injection in your code.

Thank You, that will work. This was my first coding with SQL connection, I copied it straight from W3school. I will make it better. Thanks again. — Shijil, Feb 19 '19 at 07:13

save data to SQL and also display them according to json array count

2 Answers2