CODEWITHSUNDEEP
phpmysql
Shawn Gavaghan
Shawn Gavaghan

Reputation: 57

php help making more effective

i have a php script that that pulls data for 3 tables and does some math its not the best looking but it works the issue i am have is that their are over 35,000 times this need to be done as of now it only able to do a few 100 in 50 minutes here is the code

<?php
    //my sql
    ini_set('max_execution_time', 3000);
    $servername = "x.x.x.x";
    $username = "uaer";
    $password = "psass";
    $dbname = "evemur";
    $avgvar = "10";
    //mysql con string
    $con = mysqli_connect("127.0.0.1", "root", "", "evemur");
    $sql = "SELECT * FROM `industryactivityproducts` WHERE `activityID` = 1 ORDER BY `typeID` ASC ";
    $result = mysqli_query($con, $sql) or die(mysqli_error($con));
    $reret = mysqli_num_rows($result);
    for ($x = 0; $x <= $reret -1; $x++) {
        $row = mysqli_fetch_row($result);

        $blue = $row[0];

        $ter = $row[1];
        $fitem = $row[2];
        $dqut = $row[3];
        $cost = 0;
        $sql2 = "SELECT * FROM `industryactivitymaterials` WHERE `typeID` = $blue AND `activityID` = 1 ORDER BY `typeID` ASC ";
        $result2 = mysqli_query($con, $sql2) or die(mysqli_error($con));
        // retures the mat to make and qut
        $reret4 = mysqli_num_rows($result2);
        for ($s = 0; $s <= $reret4 -1; $s++) {
            $reret2 = mysqli_fetch_row($result2);
            $mat = $reret2[2];
            $matq = $reret2[3];
            for ($f = 0; $f <= 1 -1; $f++) {
                $avgprice = 0;
                $sql3 = "SELECT * FROM `items` WHERE `Buy` NOT LIKE '1' AND `Volume Entered` >1 AND `Type` = $mat ORDER BY `Price` ASC ";
                $result3 = mysqli_query($con, $sql3) or die(mysqli_error($con));
                for ($d = 0; $d <= $avgvar; $d++) {
                    $row3 = mysqli_fetch_row($result3);
                    $avgprice = $avgprice + $row3[2];
                }

                $avgprice = $avgprice / ($avgvar + 1);
                $cost = $cost + ($avgprice * $matq);
                $avgprice =  number_format($avgprice, 2, '.', ',');
                echo $avgprice . " ".$mat. "<br>";
            }

        }

        $sql5 = "SELECT * FROM `items` WHERE `Buy` NOT LIKE '' AND `Type` = $fitem ORDER BY `items`.`Price` DESC ";
        $result5 = mysqli_query($con, $sql5) or die(mysqli_error($con));
        $row5 = mysqli_fetch_row($result5);
        $pfit = $row5[2] * $dqut;
        $pfit1 = $pfit - $cost;
        $cost =  number_format($cost, 2, '.', ',');
        $pfit1 =  number_format($pfit1, 2, '.', ',');
        echo $fitem." cost:" . $cost ." ". "you make " . $pfit1 ;
    }
?>

Upvotes: 1

Views: 45

Answers (1)

Tom
Tom

Reputation: 589

You should load all of the data from the tables before entering the loops, rather than executing MYSQL functions in every loop. This should greatly increase the speed of your script.

Otherwise than that you're just performing basic mathematical operations so there's not much to improve in terms of speed.

Upvotes: 1

Related Questions