How can I insert or update records from different tables

Question

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:

check if codice is already present in the table magazzino, if not INSERT a record with codice, pezzi and durata.
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();
}

ok i will try. I have doubts how to use it....sorry but it is first code for me — sentenza, Oct 27 '21 at 20:15
Also, do not mix `mysql_` calls and `mysqli_` calls in your script. In fact, you should not be calling any `mysql_` functions in any modern code. — mickmackusa, Oct 27 '21 at 20:33
https://stackoverflow.com/questions/61160742/insert-on-duplicate-key-update-with-sum-or-minus-mysql this should show you how to add in the UPDATE clause. And https://stackoverflow.com/a/11235549/2943403 — mickmackusa, Oct 27 '21 at 21:27
And https://stackoverflow.com/q/6107752/2943403 and https://stackoverflow.com/q/13466647/2943403 — mickmackusa, Oct 27 '21 at 22:15
I find your question Unclear. How does the `parts` table fit in? You should probably set up a fiddle and express your desired outcome as part of your [mcve]. — mickmackusa, Oct 27 '21 at 22:29

Roman Krut · Answer 1 · 2021-10-27T20:35:42.947

0

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

edited Oct 27 '21 at 20:35

answered Oct 27 '21 at 20:29

Roman Krut

176
8

1

Thanks for the advice, but i want to create new records independent from first table. I can do this operation by grouping and sum, but if i modify first table i will loose these "groups" – sentenza Oct 27 '21 at 20:35
1

It will create new records if the records does not exist in magazzino table. And values in new records will be the same as in parts table. The same thing you write on php. Just try to run select I provided and you will get my idea. thanks – Roman Krut Oct 27 '21 at 20:38

score 0 · Answer 2 · edited Oct 27 '21 at 23:14

0

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)

edited Oct 27 '21 at 23:14

desertnaut

57,590
26
140
166

answered Oct 27 '21 at 21:49

sentenza

5
5

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 27 '21 at 21:50
Is it correct? Probably. Is it insecure? Yes. Is it a duplicate? Probably. – mickmackusa Oct 27 '21 at 22:23

How can I insert or update records from different tables

2 Answers2