I have 3 tables about medication and stock. The first table is asimple id
and med_name
and it is called medication
.
The second table is about each med and how much do we have pills. The table called med_pharmacy
.
Third table is how much we give from each med. It is called consultation_med
.
Now, I created an AJAX request so when we want to give a patient a number of pills, to see if we still have or not before adding to database. If we still have, I will echo good
, if not I echoed exceeded
.
Here is the tables:
My problem is that on initialization, and by that I mean when we get a number of pills per example for medication that have a med_id = 16
, I will have a new row added to table 2, but not to table 3, because we still not given any pills to any patient.
So when I use the following scripts for the first time for each medication, the button id=add_more
will stay disabled, because the query is returning null, and table 3 does not have at least one record for this medication. So if I need to give out 20 pills to a patient for the first time, I will not be able to click the button, even I already have 100 pills from the med.
How can I solve that? Should I add an empty row filled with 0
to the field of given_quantity
on table 3, at each time a new pack of medication came so this problem would be solved ?
var calculate = function()
{
var quant = $('#medication_quantity').val();
var med_p_id = $("#medication_id").val();
console.log(med_p_id)
$.ajax({
url: '../php/ensureQuantity.php',
type: 'POST',
data: { quant: quant, mid: med_p_id},
dataType: 'TEXT',
success:function(resp)
{
console.log(resp);
if(resp=="exceed")
{
$("#add_more").prop('disabled', true);
$("#danger_message").show();
}
else
{
$("#add_more").prop('disabled', false);
$("#danger_message").hide();
}
},
error:function(resp)
{
console.log(resp);
}
})
}
With this PHP code:
$cid = $_SESSION['clinic_id'];
$mid = $_POST['mid'];
$quant = $_POST['quant'];
$still=0;
$ensureQuantity = "SELECT
t1.med_pharmacy_id, t1.med_id,
sum(t2.given_quantity) as given_pills,
t1.med_tablet - ((sum(t2.given_quantity)*t1.med_tablet)/t1.med_pill) as still_tablets,
(t1.med_pill-sum(t2.given_quantity)) as still_pills
FROM med_pharmacy t1, consultation_med t2, medication t3 WHERE (t1.med_pharmacy_id = t2.med_pharmacy_id AND t1.med_id=t3.med_id
AND t1.clinic_id=:cid AND t1.med_pharmacy_id = :mid)
GROUP BY t1.med_pharmacy_id, t1.med_id,t3.med_name, t1.med_expiry,t1.med_barcode,t1.med_tablet,t1.med_pill,t1.med_received";
$execEnsureQuantity = $conn->prepare($ensureQuantity);
$execEnsureQuantity->bindValue(':cid', $cid);
$execEnsureQuantity->bindValue(':mid', $mid);
$execEnsureQuantity->execute();
$res = $execEnsureQuantity->fetch();
if($res['still_pills']==null || $res['still_pills']=="")
{
$still = 0;
}
else
{
$still = $res['still_pills'];
}
if($quant>$still)
{
echo "exceed";
}
else
{
echo "good";
}