weixin_33737134 2017-07-19 06:01 采纳率: 0%
浏览 56

查询返回空值

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:

enter image description here

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";
}
  • 写回答

1条回答 默认 最新

  • weixin_33714884 2017-07-19 08:09
    关注

    I already found the solution as the following:

    SELECT t1.med_id, 
    t1.med_pharmacy_id,
    t3.med_name,
    t1.med_expiry, 
    t1.med_barcode, 
    t1.med_tablet, 
    t1.med_pill, 
    t1.med_received,
    sum(t2.given_quantity) as given_pills,
    t1.med_tablet - ((ifnull(sum(t2.given_quantity),0)*t1.med_tablet)/t1.med_pill) as still_tablets,
    (t1.med_pill-sum(t2.given_quantity)) as still_pills
    FROM med_pharmacy t1
    LEFT JOIN consultation_med t2 USING (med_pharmacy_id,clinic_id)
    LEFT JOIN medication t3 USING (med_id,clinic_id)
    WHERE t1.clinic_id=:cid and t1.med_pharmacy_id=:mid  GROUP BY t1.med_pharmacy_id, t1.med_expiry,t1.med_barcode,t1.med_tablet,t1.med_pill,t1.med_received
    

    And changing the jQuery script to:

    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: 'JSON',
        success:function(result)
        {
    
          var remaining = result['still_pills'];
          if(remaining == null)
          {
            remaining = result['med_pill'];
          }
          if(quant>parseInt(remaining))
          {
            //console.log('1* Quant:'+quant+'_'+remaining);
            $("#add_more").prop('disabled', true);
            $("#danger_message").show();
          }
          else
          {
            console.log('2* Quant:'+quant+'_'+remaining);
            $("#add_more").prop('disabled', false);
            $("#danger_message").hide();
          }
          // if(resp=="exceed")
          // {
          //   $("#add_more").prop('disabled', true);
          //   $("#danger_message").show();
          // }
          // else
          // {
          //   $("#add_more").prop('disabled', false);
          //   $("#danger_message").hide();
          // }
        },
        error:function(result)
        {
          console.log(result);
        }
      })
    }
    
    $(document).ready(function()
    {
      $('#medication_quantity').on('keyup', calculate);
      $('#medication_id').on('change', calculate);
    })
    
    评论

报告相同问题?

悬赏问题

  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥50 汇编语言除法溢出问题
  • ¥65 C++实现删除N个数据列表共有的元素
  • ¥15 Visual Studio问题
  • ¥15 state显示变量是字符串形式,但是仍然红色,无法引用,并显示类型不匹配
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波