Check mysql DB on input using JS

Question

&I have a qty field for goods receiving. I want to check the receipts table against the orders table and make sure that value being entered does not exceed the total order value. I have this code in my field:

<div class="pure-control-group">       
    <label for="rec_qty">Qty Received:</label>
    <input type="text" name="rec_qty" id="rec_qty" onChange="receiptTotal()"></input>
</div>

Javascript Code:

function receiptTotal(){
    $.ajax({
        type: "GET",
        url: "receipts.php?do=checkrec&id="+row.pur_ID,
        //dataType: 'json', 
        success : function(data) { 
            // Here I want to do the comparisons of the two fields.
            // If data.rec_qty + form.rec_qty > pur_qty then throw error.
        }
    }); 
}

Finally PHP Code:

if($_GET['do'] == "checkrec") {
    $rec = [];
    //First get the receipts total for this ID
    $getRows = $db->query(sprintf("SELECT sum(rec_qty) as total_qty, pr.pur_qty from receipts inner join purchases pr on rec_purID = pr.pur_ID WHERE rec_purID = '$groupid' GROUP BY pur_ID")) or SQLError();
    while($rec = $getRows->fetch_assoc()) {
        $result[] = $rec;
    }
    echo json_encode($result);
 }

I am a complete novice just incase anyone was wondering. I would really appreciate the help!

where do you define $groupid?? and your url string wont work. You need & instead of $ — Kylie
– Kylie, Commented Mar 24, 2016 at 8:44
My json array looks like this: [{"total_qty":"323","pur_qty":"500"}] — Nic
– Nic, Commented Mar 24, 2016 at 8:56

Kylie · Accepted Answer · 2016-03-24 14:48:18Z

1

At first I thought you were asking how to do it in your database, but now I see you are already getting the data from your server and returning it. So I'll answer now, hopefully its what you meant.

function receiptTotal(){
  $.ajax({
    type: "GET",
    url: "receipts.php?do=checkrec&id="+row.pur_ID,
    //dataType: 'json', 
    success : function(data) { 
        var rec_qty = $('#rec_qty').val();
        if((data.rec_qty+rec_qty) > data.pur_qty){
           alert('Value is too high!!'); // or whatever error you want
        }
    }
  }); 
}

I will say this though.....you are basically firing a server request along with a database request, every time the field changes. Which is extremely inefficient. You should just load the data once, and then refer to it locally for the check. But I'll let you figure that out on your own :)

answered Mar 24, 2016 at 14:48

Kylie

11.8k11 gold badges51 silver badges81 bronze badges

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

Collectives™ on Stack Overflow

Check mysql DB on input using JS

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