I have two tables: parts (with codice, pezzi, durata) and magazzino (with codiceM, pezziM, durataM)

I want to add or update some records from parts to magazzino. What I would Like to do is:

1. check if codice is already present in the table magazzino, if not INSERT a record with codice, pezzi and durata.

2. if codice is already present in magazzino, sum and UPDATE pezzi and durata associated with codice.

I use phprunner to create database and insert a button that executes the code after selecting a record in parts.

Here is my code that execute with no errors but it gaves me no results.

$record = $button->getNextSelectedRecord();    
$cod=$record["codice"];     //variable assignments
$qnty=$record["pezzi"];
$time=$record["durata"];
$control=0;   //control variable

$con = new mysqli("localhost","root","","provaMagazzino") or die("sorry     not connected");
$sql = "SELECT * FROM magazzino";    
$resultq = $con->query($sql); 


 while($row = mysql_fetch_array($resultq)){     // check and update records in magazzino table
 echo($row['codice']);
 if ($row['codice']==$cod) {
 $row['pezziM'] =$qnty;
 $row['durataM'] =$time;
echo('durataM');
 $control=1;
 break;
 }

}

if ($control=0) {  //add new records al Magazzino if control variable is zero
$resultq->codiceM = $record["codice"];
$resultq->durataM = $record["durata"];
$resultq->pezziM = $record["pezzi"];
$resultq->descrizioneM = $record["descrizione"];
$resultq->Add();
}

CodePudding user response：

You can calculate your data with mysql only

SELECT p.codice, IFNULL(p.pezzi, 0)   IFNULL(m.pezziM,0) AS updatedPezzi,IFNULL(p.durata,0)    IFNULL(m.durataM,0) AS updatedDurata FROM parts AS p
left join magazzino m on p.codice = m.codiceM

IFNULL is added just to be sure that your data will not be lost if the record does not exist in magazzino table. The result will be data that need to be inserted into magazzino table and you can customize it with WHERE condition to calculate specific rows After that you can insert this data from php to mysql again if INSERT ON DUPLICATE is not good for you. There are a lot of cases. As I see the codice column should be unique so for me insert on duplicate key update is the best choice here

CodePudding user response：

Is this correct?

  $record = $button->getNextSelectedRecord();    
   $cod=$record["codice"];     //variable assignments
   $qnty=$record["pezzi"];
   $time=$record["durata"];    
   
   INSERT INTO magazzino(codiceM, pezziM, durataM)
   VALUES ('$cod', '$qnty', '$time')
   ON DUPLICATE KEY UPDATE
   codiceM= values($cod)
   pezziM= pezziM  values($qnty)
   durataM= durataM  values($time)