MySQL query within loop and recursive timing out

Question

I am trying to do the following in the below code:

Take "est_no, wo_no, wt_no" from the wrnt table.
get "itemcode" from the matest table for each est_no got from wrnt table
if the "itemcode" has a prefix of SE, then i call the function recursively, until there is no prefix
after getting the "itemcode", I get the relevant "marate" from itemast table for each itemcode
finally insert them all (wo_no, wt_no, itemcode, marate)

You see that there is a lot of looping and recursive, resulting in a much running time.

Is there any way i can avoid loops in this? or any other way to optimize the script?

<?php
set_time_limit(200);
Class Matewr{
    private $dbhost;
    private $dbuser;
    private $dbpass;
    private $dbname;
    private $conn;
    private $dsn;
    private $pdo;
    private $insertquery='replace into compcost.matewr (workord,warrant,itemcode,marate)';
    function MateWr(){
        $this->dbhost = 'localhost';
        $this->dbuser = 'root';
        $this->dbpass='';
        $this->dbname = 'compcost';
        $this->dsn="mysql:host=$this->dbhost;dbname=$this->dbname";
        $this->pdo= new PDO ($this->dsn, $this->dbuser, $this->dbpass);

    }
    function readWrnt(){
        $idresult = $this->pdo->query("SELECT est_no, wo_no, wt_no from compcost.wrnt");
        while($row= $idresult->fetch())
        {
            $est_no=$row['est_no'];
            $wo_no= $row["wo_no"];
            $wt_no= $row["wt_no"];
            $this->getItemCodesRecursive($est_no, $wo_no, $wt_no);
            $this->pdo->query($this->insertquery);
        }
    }
    function getItemCodesRecursive($est_no, $wo_no, $wt_no){
        #$sql = "select itemcode, reccd from compcost.matest where est_no='".$est_no."'";
        $sql =$this->pdo->prepare("select itemcode from compcost.matest where est_no=?");
        $sql->execute(array($est_no));
        $rows = $sql->fetchAll();
        foreach($rows as $row){
            $itemcode = $row["itemcode"];
            if(strcasecmp(substr($itemcode, 0, 2),"se") == 0){
                $this->getItemCodesRecursive(substr($itemcode, 2), $wo_no, $wt_no);
            }
            else{
                $sql_marate=$this->pdo->prepare("select MARATE from compcost.itemmast where itemcode=?");
                $sql_marate->execute(array($itemcode));
                #$result_marate = $this->pdo->query($sql_marate);
                #if($row_marate=$result_marate->fetch(PDO::FETCH_LAZY)){
                $row_marate=$sql_marate->fetchAll();
                foreach($row_marate as $row){
                    $marate=$row["MARATE"];                         
                    $this->insertquery .= " values ('$wo_no','$wt_no','$itemcode','$marate'),";
                }                       
            }   
        }       
    }
}
$matewr = new MateWr();
$matewr->readWrnt();

?>

Thanks & Regards

Is there a reason you want to select 1 by 1 instead of all record base on e.g a item_code? — Someone Special
– Someone Special, Commented Dec 9, 2016 at 6:41
@SomeoneSpecial if the item code has a prefix "SE", then it represents another est_no, that is why I do a recursive call. Its chaos — naeem a
– naeem a, Commented Dec 9, 2016 at 10:11
Prefix with SE you can do sql statement to match the prefix too — Someone Special
– Someone Special, Commented Dec 11, 2016 at 2:52

Someone Special · Accepted Answer · 2016-12-09 06:49:46Z

1

Why not do a combine select statement like this?

Select MARATE from compcost.itemmast a, compcost.matest b, compcost.wrn c WHERE c.wt_no = b.wt_no AND a.item_code = b.item_code

Not clear on your table structure but you get the gist... Select item from your last table (a) where a field in a = b and a field in b = c.

That way you can get all the record without looping that much.

You may need a few other MySQL statement but by working on your sql statements many of your loops can be avoided.

answered Dec 9, 2016 at 6:49

Someone Special

13.7k8 gold badges54 silver badges80 bronze badges

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

Collectives™ on Stack Overflow

MySQL query within loop and recursive timing out

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related