PHP: Cant save the remeaning Data to the database

Question

I am trying to insert the data to the database by fetching them first, doing some additions and then setting a condition in the loop, if the value exceeds over > 2200. Inside this if condition, I have a for each loop where it will take all the records fetched and insert into the 2nd table. I am getting it right so far, now the problem is the remaining value from the table fetched, does not insert into the new tables. Please find the screenshot attached (yellow cells). I want to also make them save and inserted in both the tables and assign a value to it.

Code

    if  (isset($_POST["genRun"]))  {
  $total_weight = 0; 
  $i = 1;
  $arr = array();
  $excess = 0;





                mysql_select_db($database_callmtlc_SalmatDB, $callmtlc_SalmatDB);


                while($row_FetchRecordRS = mysql_fetch_assoc($FetchRecordRS))   {

                 $id = $row_FetchRecordRS['ID'];
                $carr_ID = $row_FetchRecordRS['CarrierID'];
                $address = $row_FetchRecordRS['DeliveryAddress'];
                $potzone = $row_FetchRecordRS['Postzone'];
                $instruction = $row_FetchRecordRS['DeliveryInstruction'];
                $quantity = $row_FetchRecordRS['Quantity'];
                $jobID = $row_FetchRecordRS['JobID'];
                $jobName = $row_FetchRecordRS['JobName'];
                $bundlesize = $row_FetchRecordRS['Bundlesize'];
                $bundle = $row_FetchRecordRS['Bundles'];
                $items = $row_FetchRecordRS['Items'];
                $weight = $row_FetchRecordRS['WeightKgs'];
                $suburb = $row_FetchRecordRS['Suburb'];
                $num = $row_FetchRecordRS['TotalWeightKgs'];

               //$num = $row_FetchRecordRS['TotalWeightKgs'];





                $arr[] = array('CarrierID' => $carr_ID, 'DeliveryAddress' => $address, 'Postzone' => $potzone, 'DeliveryInstruction' => $instruction, 'Quantity' => $quantity, 'JobID' => $jobID, 'JobName' => $jobName, 'Bundlesize' => $bundlesize, 'Bundles' => $bundle, 'Items' => $items, 'WeightKgs' => $weight, 'Suburb' => $suburb, 'TotalWeightKgs' => $num);



                      if ($num + $total > 2200) {
                            $sqltransitlist = "INSERT INTO TransitList(genID, total) Values ('$i','$total')";
                            $ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);

                            foreach ($arr as $data) {
                                $sqlquerytest = "INSERT INTO GenerateRun(CarrierID, DeliveryAddress, Postzone,DeliveryInstruction, Quantity, JobID, JobName, 
                     Bundlesize, Bundles, Items, WeightKgs,  Suburb, TotalWeightKgs,LodingZoneID) VALUES('" 
                     . $data['CarrierID'] ."','"  . $data['DeliveryAddress'] ."','" . $data['Postzone'] . "','"  .  $data['DeliveryInstruction']. "','" .$data['Quantity']  . "','"  . $data['JobID'] . "','"  . $data['JobName'] . "','" . $data['Bundlesize']. "','"  .$data['Bundles'] . "','"    . $data['Items'] . "','"  .$data['WeightKgs']. "','" . $data['Suburb']."','"  .$num."','" .$i ."')";
                                $ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB); 
                             }

        $arr = array();  
        $i++;
        $total = 0;   
  } else {
        $total += $num;
  }
}
// after the loop check if there are some data was not inserted and insert it after the loop is over
if ($total > 0) {
   $sqltransitlist = "INSERT INTO TransitList(genID, total) Values ('$i','$total')";
   $ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);
   foreach ($arr as $data) {
       $sqlquerytest = "INSERT INTO GenerateRun(CarrierID, DeliveryAddress, Postzone,DeliveryInstruction, Quantity, JobID, JobName, 
                     Bundlesize, Bundles, Items, WeightKgs,  Suburb, TotalWeightKgs,LodingZoneID) VALUES('" 
                     . $data['CarrierID'] ."','"  . $data['DeliveryAddress'] ."','" . $data['Postzone'] . "','"  .  $data['DeliveryInstruction']. "','" .$data['Quantity']  . "','"  . $data['JobID'] . "','"  . $data['JobName'] . "','" . $data['Bundlesize']. "','"  .$data['Bundles'] . "','"    . $data['Items'] . "','"  .$data['WeightKgs']. "','" . $data['Suburb']."','"  .$num."','" .$i ."')";

                     echo "$i , $total <br>";
       $ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB); 
   }
}

Output which I am getting it now: (When we add these total, the total is less than the required output)

Required Output: (when we do the loop < 2200, its not saving the value over 2200)

SQL" and thats the sql :

SELECT UpdatedCsvFiles.ID, UpdatedCsvFiles.CarrierID, UpdatedCsvFiles.DeliveryAddress, UpdatedCsvFiles.Postzone, UpdatedCsvFiles.DeliveryInstruction, UpdatedCsvFiles.Quantity, UpdatedCsvFiles.JobID, UpdatedCsvFiles.JobName, UpdatedCsvFiles.Bundlesize, UpdatedCsvFiles.Bundles, UpdatedCsvFiles.Items, UpdatedCsvFiles.WeightKgs, SuburbPostZone.Suburb, UpdatedCsvFiles.TotalWeightKgs FROM UpdatedCsvFiles LEFT JOIN SuburbPostZone on SuburbPostZone.areaID = UpdatedCsvFiles.CarrierID Where UpdatedCsvFiles.DeliveryAddress != 'PLEASE LEAVE IN WAREHOUSE' GROUP by  UpdatedCsvFiles.CarrierID, UpdatedCsvFiles.DeliveryAddress ORDER by UpdatedCsvFiles.CarrierID , SuburbPostZone.Suburb, UpdatedCsvFiles.ID  ASC

Your explanation is mess. But I guess the problem is `do{...}while()` loop you do. Instead of usual way just `while(){...}` — Alex, Jul 26 '18 at 01:06
I completely understand that my code is in mess, as i was trying to many things. Initially, i had while () but that also didnt work — Kunal Parekh, Jul 26 '18 at 01:10
my last set of data which are less then 2200 will not go in the loop — Kunal Parekh, Jul 26 '18 at 01:11
You have your 5 records inserted now according to the screenshot you added. What is wrong with those 5 records? please provide expected set of records you want to get. It is not clear how 9528.49 is related to your post. that is sum of all records I guess, but what do you want to do with that amount? to insert extra record into the table? why? what will be values for the rest of columns i the table then? — Alex, Jul 27 '18 at 12:23

Alex · Accepted Answer · 2018-07-27T13:52:26.213

1

Here is kind pseudo code:

// select db just once before the loop, you don't need to select db every time in the loop
mysql_select_db($database_callmtlc_SalmatDB, $callmtlc_SalmatDB);
// use while loop
while($row_FetchRecordRS = mysql_fetch_assoc($FetchRecordRS)) {
  $id = $row_FetchRecordRS['ID'];
  ...
  if ($num + $total > 2200) {
        $sqltransitlist = "INSERT INTO TransitList(genID, total) ...";
        $ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);

        foreach ($arr as $data) {
            $sqlquerytest = "INSERT INTO GenerateRun(CarrierID ...";
            $ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB); 
        }

        $arr = array();  
        $i++;
        $total = 0;   
  } 

  $total += $num;
  $arr[] = array('CarrierID' => $carr_ID, 'DeliveryAddress'...);
}
// after the loop check if there are some data was not inserted and insert it after the loop is over
if ($total > 0) {
   $sqltransitlist = "INSERT INTO TransitList(genID, total) ...";
   $ResultUpd3 = mysql_query($sqltransitlist, $callmtlc_SalmatDB);
   foreach ($arr as $data) {
       $sqlquerytest = "INSERT INTO GenerateRun(CarrierID ...";
       $ResultUpd1 = mysql_query($sqlquerytest, $callmtlc_SalmatDB); 
   }
}

NOTE You should stop using deprecated mysql_* functions. And you should use prepared statements with mysqli or PDO functions to avoid sql injections

How can I prevent SQL injection in PHP?

NOTE After chat some pseudocode to keep here: https://ideone.com/vRr5rA

edited Jul 27 '18 at 13:52

answered Jul 26 '18 at 01:36

Alex

16,739
1
28
51

Thanks Alex. Just one question, if i am defining `$id = $row_FetchRecordRS['ID'];` for example, will that be just inside the while loop starts or I can define it just before the while loop starts? I know its bit dumb to ask but have dont have great skill yet – Kunal Parekh Jul 26 '18 at 01:49
You don't have `$row_FetchRecordRS` var until you start your loop with `while($row_FetchRecordRS = mysql_fetch_assoc($FetchRecordRS)) {` so you can't set your variables before the loop starts. It is inside the loop. that part is ok. – Alex Jul 26 '18 at 01:55
hey alex, I just realized that the second conditon >$total, does not take all the values. In each while loop there will be some value > 2200, but it is only taking that value what left at the end. When I checked the total, it is different from what I want. I want to take all the values and add them as well. Its not adding all values. can you please suggest what is wrong here – Kunal Parekh Jul 27 '18 at 10:49
@SarahMalik please provide data sample in text format the result you have and expected result. – Alex Jul 27 '18 at 10:54
I have just uploaded the screenshot – Kunal Parekh Jul 27 '18 at 11:13
is there any way I can get the required output in the loop? – Kunal Parekh Jul 27 '18 at 11:49
@SarahMalik text is much more convenient, we should not work for you, but just help, please provide data in text format. And you current code. It seem that you 've lost the part after the loop again. – Alex Jul 27 '18 at 12:17
what data you required in the text? code or just the database table? – Kunal Parekh Jul 27 '18 at 12:28
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/176868/discussion-between-sarah-malik-and-alex). – Kunal Parekh Jul 27 '18 at 12:29

PHP: Cant save the remeaning Data to the database

1 Answers1