Updating every entry in a table

Question

I am creating a running inventory system. I am looking for suggestions on how to speed up the processing time to populate my table that contains the daily inventories.

I have 4 database tables that I am using to do this:
1). Daily Usage table
2). Incoming Product table (product ordered to come in)
3). Physical Inventory Counts table (Monthly Physical Count totals held here)
4). Perpetual Running Inventory table (In a perfect world this is what inventories should be)

As of now I have each piece of data being updated in my perpetual table and it takes quite awhile for my code to run through, do the calculations, connect to the database and update the information.

My question is:
How can I speed this up? Is there a way that I can upload all info from an array at once so I am only updating the database once instead of a couple hundred times?

Sorry for dumping 75 lines of code below but I wanted to make sure to show everything so it can easily be seen what is going on.

My logic behind the code:
I start off by pulling info from all 4 tables into arrays and then cycling through the arrays. I use a date function to start from 2 weeks ago instead of the very beginning of the table (Beginning of 2016). Once the date quota is met, I pull the previous days perpetual running inventory, subtract the current dates daily usage, and add the current days incomming to get the current days perpetual. Once I have the current days perpetual I update the perpetual table with that piece of data.

If I simply just stored the information into to the array, could I basically write over the whole table at the end? Im assuming this would be much faster if it is possible?

Heres the code:

<?php

    include("connection.php");

    /*  $rowper below (Row Perpetual)  */
    $query= "SELECT * FROM perpetual";
    $result = mysqli_query($link, $query);

    /*  $rowdaily below     */
    $query2= "SELECT * FROM dailyusage";
    $result2 = mysqli_query($link, $query2);

    /*  $rowpo below (Row Purchase Order)  */
    $query3= "SELECT * FROM incomming";
    $result3 = mysqli_query($link, $query3);

    /*  $rowinv below (Row Inventory)  */
    $query4= "SELECT * FROM inventory";
    $result4 = mysqli_query($link, $query4);

    $checkdate = mktime(0, 0, 0, date('n'), date('d')-14, date('y'));
    $checkdate= date('Y-m-d', $checkdate);

    $b=1;

    while (($rowper=mysqli_fetch_array($result)) and ($rowdaily=mysqli_fetch_array($result2)) and ($rowpo=mysqli_fetch_array($result3)) and ($rowinv=mysqli_fetch_array($result4))){

        $a=2;

        if($b == 1){

            while($a< mysqli_num_fields($result)) {
                /* $it holds the item #s from the column */
                $it[$a]=$rowper[$a];

                $a++;

            }    
            $a=2;
        }

        if($rowper[1]>= $checkdate){    

            if($b>2){ 

                while($a< mysqli_num_fields($result)) {

                    $previnv[$a];

                    if($rowinv[$a]!=0){

                        $rowper[$a]=$rowinv[$a];

                        $previnv[$a]=$rowper[$a];

                        /*  the 'p' is because column name was made by item # + p at end to make valid colum name */
                        $query="UPDATE perpetual SET ".$it[$a]."p ='".$rowper[$a]."' WHERE date='".$rowper[1]."' LIMIT 1";

                        mysqli_query($link, $query);

                    }else{

                        $rowper[$a]=$previnv[$a] - $rowdaily[$a] + $rowpo[$a];

                        $previnv[$a]=$rowper[$a];

                        $query="UPDATE perpetual SET ".$it[$a]."p ='".$rowper[$a]."' WHERE date='".$rowper[1]."' LIMIT 1";

                        mysqli_query($link, $query);
                    }
                    $a++;    
                }   
            }
        }
        $b++;
    }
?>

Answer 1

Stored Procedures and User Defined Functions are what you are looking for. You can schedule them to run at certain times to perform operations on your tables.

Side note, remember the fundamentals behind ETL.

Extract - Transform - Load in that order.