Insert and update different tables in database using php ajax

Question

I'm trying to insert data into tbl_stock and at the same time update tbl_product. I have so far written an ajax code below:

 <script>   
    $(document).ready(function() {
        $('#btn_stockin').click(function() {   
            event.preventDefault();

            /*Reading value from modal*/
            var newStock = $('#txt_addstock').val();
            var newPrice = $('#txt_addprice').val();
            if(newStock == '' && newPrice == ''){
                alert("Oops!, fill Text fields and try again.");
            }else{
                $.ajax({
                    method: 'POST', 
                    url: 'stock-in.php',
                    data:{stock_up: newStock, cost: newPrice,
                        <?php 
                            echo 'id: "'.$row->pid.'", oldstock: "'.$row->pstock.'", productcategory: "'.$row->pcategory.'", productname: "'.$row->pname.'", currentDate : "'.$savedate.'" '
                        ?>
                    },
                    success:function(data){
                        $('#add_stock_modal').modal('hide');
                        return data;
                    }

                });
            }

        });
    });
</script>

which calls stock-in.php and contains the following SQL codes below

<?php

include_once'connectdb.php';

if($_SESSION['useremail']=="" OR $_SESSION['role']=="Admin"){
    header('location:index.php');
}
if(isset($_POST['stock_up'])){
    $product_category   =   $_POST['productcategory'];
    $product_name       =   $_POST['productname'];
    $current_date       =   $_POST['currentDate'];
    $stockup            =   (int)$_POST['stock_up'];
    $newPrice           =   (int)$_POST['cost'];
    $id                 =   $_POST['id'];
    $oldstock           =   (int)$_POST['oldstock'];

    $new_stock          = $oldstock + $stockup;
    $amount_owed        = $newPrice * $stockup;
    
  try {
      //your stuff
  

    $query="insert into tbl_stock(category_name,product_name,stock_in,stock_price,total_cost,stocked_date) 
    values('$product_category','$product_name','$stockup','$newPrice','$amount_owed','$current_date')");
          $insert=$pdo->prepare($query);
          $stmt = $insert->execute();
          
          
          if($stmt){

                $sql="UPDATE `tbl_product` SET `pstock` = ?, `purchaseprice` = ? WHERE  pid= ? ";
                    $update=$pdo->prepare($sql);
                    $update->execute([$new_stock, $newPrice, $id]);

            }else{
              echo'Error in updating stock'; 
            }

          } catch(PDOException $e) {
            echo $e->getMessage();
        }
}
?>

debug window shows values The insert is not working and no error is displayed. I have spent a sleepless night trying to find out why. please I will appreciate your help.

Answer 1

You made the move to using PDO and creating Prepared Statements but then spoil the good work by embedding variables directly within the SQL commands. I couldn't test any of the following but I hope it helps.

<script>
    <?php
        $data=array(
            'id'                =>  $row->pid,
            'oldstock'          =>  $row->pstock,
            'productcategory'   =>  $row->pcategory,
            'productname'       =>  $row->pname,
            'currentDate'       =>  $savedate
        );
        printf('var json=%s;',json_encode($data));
    ?>
    
    $(document).ready(function() {
        $('#btn_stockin').click(function(event) {   
            event.preventDefault();

            /*Reading value from modal*/
            var newStock = $('#txt_addstock').val();
            var newPrice = $('#txt_addprice').val();
            
            if( newStock == '' && newPrice == '' ){
                alert("Oops!, fill Text fields and try again.");
            }else{
                let args={
                    stock_up:newStock,
                    cost: newPrice
                };
                
                let payload=Object.assign(args,json);
            
                $.ajax({
                    method:'POST', 
                    url:'stock-in.php',
                    data:payload,
                    success:function(data){
                        $('#add_stock_modal').modal('hide');
                        window.location.reload();
                    }
                });
            }
        });
    });
</script>

<?php
    
    #stock-in.php
    /*
        If you are using sessions you need to start a session!
    */
    error_reporting( E_ALL );
    session_start();

    
    
    if( empty( $_SESSION['useremail'] ) OR empty( $_SESSION['role'] ) OR $_SESSION['role']=="Admin" ){
        exit( header('Location: index.php') );
    }

    /*
        Check that all fields that are required in the sql have been submitted
    */
    if( isset( 
            $_POST['stock_up'],
            $_POST['productcategory'],
            $_POST['productname'],
            $_POST['currentDate'],
            $_POST['cost'],
            $_POST['id'],
            $_POST['oldstock']
        ) ){

        try{
            
            include_once 'connectdb.php';

            /*
                When inserting, updating multiple tables there is some sense in using a transaction
                so that if one part fails the db is not littered with orphan records
            */
            $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $pdo->beginTransaction();
            
            
            
            
            $product_category   =   $_POST['productcategory'];
            $product_name       =   $_POST['productname'];
            $current_date       =   $_POST['currentDate'];
            $stockup            =   (int)$_POST['stock_up'];
            $newPrice           =   (int)$_POST['cost'];
            $id                 =   $_POST['id'];
            $oldstock           =   (int)$_POST['oldstock'];

            $new_stock          = $oldstock + $stockup;
            $amount_owed        = $newPrice * $stockup;
            
            
            /*
                The sql command should use placeholders rather than embedded fariables - the names are arbitrary
            */
            $sql='insert into `tbl_stock` ( `category_name`, `product_name`, `stock_in`, `stock_price`, `total_cost`, `stocked_date` ) 
                values 
            ( :cat, :prod, :in, :price, :cost, :date )';
            $stmt=$pdo->prepare( $sql );
            $args=array(
                ':cat'      =>  $product_category,
                ':prod'     =>  $product_name,
                ':in'       =>  $stockup,
                ':price'    =>  $newPrice,
                ':cost'     =>  $amount_owed,
                ':date'     =>  $current_date
            );
            if( !$stmt->execute( $args )  )echo 'stmt#1 failed';
            
            
            
            $sql='update `tbl_product` set `pstock` =:stock, `purchaseprice`=:price where `pid`=:pid';
            $stmt=$pdo->prepare( $sql );
            $args=array(
                ':stock'    =>  $new_stock,
                ':price'    =>  $newPrice,
                ':pid'      =>  $id
            );
            if( !$stmt->execute( $args ) )echo 'stmt#2 failed';
            
            
            
            /*
                If it all went well, commit these statements to the db
            */
            if( !$pdo->commit() )echo 'commit failed';
            
            
        
        }catch( PDOException $e ){
            /*
                Any problems, rollback the transaction and report issues - 
                not necessarily with the full `getMessage()` ~ perhaps just
                'Error!' etc
            */
            $pdo->rollBack();
            
            echo $e->getMessage();
        }
    }
?>