updating the data using implode in php

Question

please help me out and sorry for my bad English, I have fetch data , on basis of that data I want to update the rows, Follows my code

I fetched data to connect API parameters

<?php 


 $stmt = $db->stmt_init();
                /* publish store for icube*/
  $stmt->prepare( "SELECT id,offer_id,name,net_provider,date,visible,apikey,networkid FROM " ."affilate_offer_findall_icube WHERE visible='1' ");
  $stmt->execute();
  mysqli_stmt_execute($stmt); // <--------- currently missing!!!
mysqli_stmt_store_result($stmt); 
 $rows = mysqli_stmt_num_rows($stmt);
   $stmt->bind_result( $id, $offer_id, $name, $net_provider, $date, $visible,$apikey,$networkid);
    $sql = array();
   if($rows>0)
   {

    while($info = $stmt->fetch() ) {
 $jsondataicube = file_get_contents('filename/json?NetworkId='.$networkid.'&Target=Affiliate_Offer&Method=getThumbnail&api_key='.$apikey.'&ids%5B%5D='.$offer_id.'');
            $dataicube = json_decode($jsondataicube, true);
foreach($dataicube['response']['data'][0]['Thumbnail'] as $key=>$val)
            {
                 $offer_id  = $dataicube['response']['data'][0]['Thumbnail']["$key"]['offer_id'];
                $display = $dataicube['response']['data'][0]['Thumbnail']["$key"]['display'];
                 $filename = $dataicube['response']['data'][0]['Thumbnail']["$key"]['filename'];
                $url  = $dataicube['response']['data'][0]['Thumbnail']["$key"]['url'];
             $thumbnail  = $dataicube['response']['data'][0]['Thumbnail']["$key"]['thumbnail'];
             $_filename = mysqli_real_escape_string($db,$filename);
                  $_url = mysqli_real_escape_string($db,$url);
                $_thumbnail  = mysqli_real_escape_string($db,$thumbnail);
 $sql[] = '("'.$offer_id.'","icube","'.$_thumbnail.'","'.$_url.'")';

            }
        }   

As I store values which have to be inserted in 'sql' now

$stmt->prepare( "SELECT offer_id FROM " ."affilate_offer_getthumbnail_icube ORDER BY 'offer_id' ASC");
$stmt->execute();
mysqli_stmt_execute($stmt); // <--------- currently missing!!!
mysqli_stmt_store_result($stmt); 
$rows = mysqli_stmt_num_rows($stmt);
$stmt->bind_result($offer_id);
$sqlimplode = implode(',', $sql);
if($rows>0)
   { 

        $query = "UPDATE affilate_offer_getthumbnail_icube WHERE offer_id='".$offer_id."' SET '".$sqlimplode."'"; 
         $stmt->prepare( $query);
 $execute = $stmt->execute();
   }
   else
   {
        $query= "INSERT INTO affilate_offer_getthumbnail_icube(offer_id, net_provider,logo2020,logo100) VALUES".$sqlimplode;
         $stmt->prepare( $query);
 $execute = $stmt->execute();
  }`
`

Insert query working well,but how can I update all the data like insert query ?

Answer 1

My Answer is refering to a "set and forget"-strategy. I dont want to look for an existing row first - probably using PHP. I just want to create the right SQL-Command and send it.

There are several ways to update data which already had been entered (or are missing). First you should alter your table to set a problem-specific UNIQUE-Key. This is setting up a little more intelligence for your table to check on already inserted data by its own. The following change would mean there can be no second row with the same value twice in this UNIQUE-set column. If that would occur, you would get some error or special behaviour.

Instead of using PHPMyAdmin you can use this command to set a column unique:

ALTER TABLE `TestTable` ADD UNIQUE(`tablecolumn`);

After setting up your table with this additional intelligence, you alter your Insert-Command a little bit:

Instead of Insert you can drop and overwrite your Datarow with REPLACE:

$query= "REPLACE INTO affilate_offer_getthumbnail_icube 
(offer_id, net_provider,logo2020,logo100) VALUES (".$sqlimplode.")";

See: Replace Into Query Syntax

Secondly you can do this with the "On Duplicate Key"-Commando.

https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

$query= "INSERT INTO affilate_offer_getthumbnail_icube 
(offer_id, net_provider,logo2020,logo100) 
VALUES (".$sqlimplode.") 
ON DUPLICATE KEY UPDATE net_provider = ".$newnetprovider.", 
                        logo2020 = ".$newlogo2020.", 
                        logo100 = ".$newlogo100.";";

Note: I think you missed some ( and ) around your $sqlimplode. I always put them around your implode. Maybe you are missing ' ' around strings as well.

Answer 2

Syntax of UPDATE query is

UPDATE table SET field1 = value1, field2 = value2 ...

So, you cannot pass your imploded array $sql to UPDATE query. You have to generate another sql-string for UPDATE query.

Answer 3

This is clearly incorrect:

 $query = "UPDATE affilate_offer_getthumbnail_icube 
 WHERE offer_id='".$offer_id."' SET '".$sqlimplode."'"; 

If the intention is to INSERT offer_id='".$offer_id."' and then UPDATE ... SET offer_id = '".$sqlimplode."'";

You have to use two separate queries, one for INSERT and then another one for UPDATE

An Example:

 $query = "INSERT INTO affilate_offer_getthumbnail_icube 
 (col_name) VALUES('".$col_Value."')";
 //(execute it first);

$query2 = "UPDATE affilate_offer_getthumbnail_icube SET 
     col_name= '".$col_Value."'" WHERE if_any_col = 'if_any_Value';
 //(execute this next);

Answer 4

Try this:

$sqlimplode = implode(',', $sql);
if($rows>0)
   { 
        /*$fields_values = explode(',',trim(array_shift($sql), "()"));
        $combined_arr = array_combine(['offer_id','net_provider','logo2020','logo100'],$fields_values);
        $sqlimplode = implode(', ', array_map(function ($v, $k) { return $k . '=' . $v; }, $combined_arr, array_keys($combined_arr))); */
        $query = "INSERT INTO affilate_offer_getthumbnail_icube(offer_id, net_provider,logo2020,logo100) VALUES".$sqlimplode." ON duplicate key update net_provider = values(net_provider),logo2020 = values(logo2020),logo100 = values(logo100)"; 
         $stmt->prepare( $query);
 $execute = $stmt->execute();
   }
   else
   {
        $sqlimplode = implode(',', $sql);
        $query= "INSERT INTO affilate_offer_getthumbnail_icube(offer_id, net_provider,logo2020,logo100) VALUES".$sqlimplode;
         $stmt->prepare( $query);
 $execute = $stmt->execute();
  }