delete not working properly in while loop

Question

here i am trying to delete from detail table here is one problem with my code if detail table have three records than only delete one record from detail table.and also it does not effect other two records in stock table.. Only one first record this code work properly after that it does not work in for delete query from detail table and not effected in stock table...

<?php
include("include/config.inc.php");


$purchaseMasterId = isset($_REQUEST['purchaseMasterId']) ? $_REQUEST['purchaseMasterId'] : 0;

if($purchaseMasterId > 0) {
    $k = 0;
    $selectMaster = "SELECT purchasedetail.purchaseMasterId, colorId, 
                            totalkg, purchaseDetailId, partyId
                    FROM purchasedetail  
                        JOIN purchasemaster ON 
                            purchaseMaster.purchaseMasterId = purchasedetail.purchaseMasterId
                    WHERE purchasedetail.purchaseMasterId  = ".$_REQUEST['purchaseMasterId'];

    $selectMasterRes = mysql_query($selectMaster);
    while($purRow = mysql_fetch_array($selectMasterRes)) {
        $purchaseDetailId   = $purRow['purchaseDetailId'];
        $purchaseMasterId   = $purRow['purchaseMasterId'];
        $colorId            = $purRow['colorId'];
        $totalkg            = $purRow['totalkg'];
        $partyId            = $purRow['partyId'];

        $select = "SELECT qty
                    FROM stock
                    WHERE partyId = ".$partyId."
                    AND colorId = ".$colorId;

        $selectRes = mysql_query($select);
        if($stockRow = mysql_fetch_array($selectRes)) {
            $current = $stockRow['qty'];
        }

        $updateStock = "UPDATE stock
                        SET qty = ".$current." - ".$totalkg."
                    WHERE partyId = ".$partyId."
                      AND colorId = ".$colorId;

        $updateStockRes = mysql_query($updateStock) or die(mysql_error());
        if($updateStockRes) {
            $deleteDt = "DELETE FROM purchasedetail 
                        WHERE purchaseDetailId  = ".$purchaseDetailId;

            $deleteRes = mysql_query($deleteDt);
            if($deleteRes){
                $deleteMst = "DELETE FROM purchasemaster 
                             WHERE purchaseMasterId  = ".$_REQUEST['purchaseMasterId'];

                $deleteMstRes = mysql_query($deleteMst) or die(mysql_error());
                if(!$deleteMstRes) {
                    echo "Purchase Master Delete Fail";
                } else {
                    header("Location:purdetail.php");
                    exit();
                }
            }
        }
    }
} 
?>

C) Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Jan 20 '17 at 15:12
@FélixGagnon-Grenier **SAVE THE KITTENS** :) _Scrap the code_ — RiggsFolly, Jan 20 '17 at 15:13
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jan 20 '17 at 15:14
@FélixGagnon-Grenier if u dont want to answer my question do not waste ur time to comment on my question — om technoplus, Jan 20 '17 at 15:15
This code should really be run in a transaction to ensure database integrity. To do that you will have to move to either the MYSQLI ir PDO database extensions. Oh and a INNODB database — RiggsFolly, Jan 20 '17 at 15:17
That wont solve the error, the `DELETE FROM purchasedetail` must have failed or something earlier. I note you dont show any errors when/if thay happen. Maybe if you add some `echo mysql_error()` in appropriate places you would find out what the error was — RiggsFolly, Jan 20 '17 at 15:20
@RiggsFolly i am trying to echo error but it's not shows me any error.. — om technoplus, Jan 20 '17 at 15:23

score 0 · Answer 1 · answered Jan 21 '17 at 16:54

Here is my updated code with perfect code working

   <?php
    include("include/config.inc.php");

    $purchaseMasterId = isset($_REQUEST['purchaseMasterId']) ? $_REQUEST['purchaseMasterId'] : 0;

    if($purchaseMasterId > 0)
    {
        $k = 0;
        $selectMaster = "SELECT purchasedetail.purchaseMasterId,colorId,totalkg,purchaseDetailId,partyId
                            FROM purchasedetail  
                        JOIN purchasemaster ON purchasemaster.purchaseMasterId = purchasedetail.purchaseMasterId
                             WHERE purchasedetail.purchaseMasterId  = ".$_REQUEST['purchaseMasterId'];
        $selectMasterRes = mysql_query($selectMaster) or die(mysql_error());
        while($purRow = mysql_fetch_array($selectMasterRes))
        {
          $purchaseDetailId   = $purRow['purchaseDetailId'];
          $purchaseMasterId   = $purRow['purchaseMasterId'];
          $colorId            = $purRow['colorId'];
          $totalkg            = $purRow['totalkg'];
          $partyId            = $purRow['partyId'];



            $select = "SELECT qty
                         FROM stock
                       WHERE partyId = ".$partyId."
                       AND colorId = ".$colorId;
            $selectRes = mysql_query($select);
            if($stockRow = mysql_fetch_array($selectRes))
            {
              $current = $stockRow['qty'];
            }

            $updateStock = "UPDATE stock
                              SET qty = ".$current." - ".$totalkg."
                              WHERE partyId = ".$partyId."
                            AND colorId = ".$colorId;
            $updateStockRes = mysql_query($updateStock) or die(mysql_error());
            if($updateStockRes)
            {
              $deleteDt = "DELETE FROM purchasedetail 
                             WHERE purchaseDetailId  = ".$purchaseDetailId;
              $deleteRes = mysql_query($deleteDt);
              if($deleteRes)
              {
                $selectid = "SELECT purchaseDetailId
                                FROM purchasedetail
                             WHERE purchaseMasterId  = ".$purchaseMasterId;
                $selectidRes = mysql_query($selectid);
                if(mysql_affected_rows() == 0)
                {
                  $mst = "DELETE FROM purchasemaster
                             WHERE purchaseMasterId  = ".$purchaseMasterId;
                  $mstRes = mysql_query($mst) or die(mysql_error());;   
                  if($mstRes)
                  {
                    header("Location:purdetail.php");
                    exit();
                  }
                  else
                  {

                  }
                }
              }
            }
        }
    } 
    ?>

delete not working properly in while loop

1 Answers1