PHP Code to Process Faster

Question

I have a running PHP program which handles supplies inventory.

I want this code to process faster to update supplies' balances when opening the page.

SUPPLIES contains the inventory of supplies and their balances.

TRANSACTIONS contains the transactions of receive, return and issued.

The program below retrieves tables' SUPPLIES and TRANSACTIONS and computes the balances from all TRANSACTIONS which updates the MYSQL table of SUPPLIES.

<?php
//Update supplies balance starts here
$supplies = DB::getInstance()->query("SELECT * FROM supplies ORDER BY id DESC");
foreach($supplies->results() as $supply) {
    $balance = 0;
    $transactions = DB::getInstance()->query("SELECT * FROM transactions ORDER BY id ASC");
    foreach($transactions->results() as $transaction) {
        if($transaction->code === $supply->id){
            if($transaction->transaction_type === "1"){
                $balance = $balance + $transaction->quantity; 
            } else if($transaction->transaction_type === "2"){
                $balance = $balance - $transaction->quantity; 
            } else if($transaction->transaction_type === "3"){
                $balance = $balance + $transaction->quantity; 
            }
        }
    }
    $supplied = new Supply();
    $supplied->find($supply->id);
    try {
        $supplied->update(array(
            'balance' => $balance,
        ));
    } catch (Exception $e) {
        die($e->getMessage());
    }
}
//Update supplies balance ends here
?>

Please indent the code properly so it's easier to read. And then think what happens when you have so many loops inside each other and why you need them. Why don't you select from the database based on the conditions rather than looping in PHP code? — Sami Kuhmonen
– Sami Kuhmonen, Commented Aug 10, 2017 at 7:38
You are selecting the entire transactions table once for every single entry in the supplies table. Your biggest speed improvement would be to set $transactions outside of your $supplies->results() loop. — Phylogenesis
– Phylogenesis, Commented Aug 10, 2017 at 7:40
@Sami Kuhmonen I know how to select from the database based on the conditions. My problem here is how to regularly update the SUPPLIES table using the date in the TRANSACTIONS table whenever a user opens the page. :( — chuybenchuy
– chuybenchuy, Commented Aug 10, 2017 at 7:44
As mentioned, your problem is the queries. Add a condition to run it properly. That will make a huge difference. Meaning at the moment if you had 1000 supplies and 1000 transactions you'd be running 1000000 loops and 2000 queries. With the condition you'd run 1000 loops and 2000 queries returning a lot less data. — Sami Kuhmonen
– Sami Kuhmonen, Commented Aug 10, 2017 at 7:48

Łukasz Jakubek · Accepted Answer · 2017-08-11 07:03:44Z

1

As said @Phylogenesis change in Transaction should trigger update of Supplies.

BEGIN TRANSACTION;
UPDATE transactions SET quantity = ..., code = ..., ....;
UPDATE supplies SET balance = balance +/- ....;
COMMIT;

So you do not need to update supplies every time the page was opened, and do not need to recompute entire table. It is important to do this in one transaction. Ideally it should be placed in database in transaction table feature TRIGGER ON UPDATE if possible.

If you can't, try to compute updated value of supplies on database side, example:

UPDATE supplies s
    SET balance = 
        (SELECT SUM(quantity) FROM transactions t WHERE t.code = s.id AND transaction_type IN (1, 3)) - 
        (SELECT SUM(quantity) FROM transactions t WHERE t.code = s.id AND transaction_type = 2);

It is still slower but much faster then PHP.

EDITED: I update the query example to meet question.

edited Aug 11, 2017 at 7:03

answered Aug 10, 2017 at 8:21

Łukasz Jakubek

1,0136 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

chuybenchuy Over a year ago

Dunno how to code this: UPDATE supplies s SET balance = (SELECT SUM(...) FROM transactions t WHERE t.code = s.id);

Łukasz Jakubek Over a year ago

I edited query to give the same results as question code.

chuybenchuy Over a year ago

Thank you very much for the effort. Really appreciated it very much. But I don't know yet how to run codes on database side. My program runs locally on a localhost. Whatever codes I put in is accessed directly by both user and admin.

chuybenchuy Over a year ago

I've successfully did what @Phylogenesis had suggested. Whenever there is an update on the transactions. The balance is then immediately updated. But I have a new problem related to this.

chuybenchuy Over a year ago

My program creates a table of supplies with its beginning balances, followed by transactions and then ending balances based on the selected period (e.g. July 1, 2017 to July 31, 2017). Even when the user enters the period, June 1, 2017 to June 30, 2017, it can still generate the table just by computing the $transactions data from a specified period. But the problem is it's taking too long to produce the table and I'm always facing an error when creating a PDF file of the table.

chuybenchuy · Accepted Answer · 2017-08-17 06:10:50Z

I did what @Phylogenesis had suggested to include updating $balance, straight to the database of $supplies based on '$transactions'.

To avoid delays on the client side to update all $balance of `$supplies', I used cron jobs to run scripts based on desired schedule.

Installed cronjob for Centos 7 by following steps from this link:

https://www.rosehosting.com/blog/automate-system-tasks-using-cron-on-centos-7/

To check if cron's installed, type this: systemctl status crond.service

Then used https://crontab.guru/ to generate expressions to schedule automatically running of PHP scripts.

If still confused on how it works, check out this link for examples:

https://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/

To install or create or edit your own cron jobs:

Type crontab -e then ENTER
Press i, then start typing your expression
Type :wq! then ENTER to save your cron job.
Type crontab -l to check for running cron jobs.

That's it! Hope this would help others!

Collectives™ on Stack Overflow

PHP Code to Process Faster

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related