php - 是否可以在 codeigniter 中仅使用一个查询来计算特定的 id 并从另一个表中减去？

Question

我想计算 sms_history 表中的 clinic_id 行，并且从 sms 的 sms total_available_sms 列中减去这些行一个查询中的表。

sms_history 表

短信表

用户表

我想像这样在表中显示记录:

Client Id  Client Name           Available sms   Total SMS
---------  -------------------   --------------  ---------
2          Krishna Dental Care   196             200

我正在尝试做这样的事情

$this->db->select('*');
$this->db->from('sms');
$query = $this->db->get();
$total_available = $query->result();


foreach ($total_available as $sms) {

//get clinic id
$clinic_id = $sms->clinic_id;

//get total available sms
$total_available = $sms->total_available;

$count = $this->db->where('clinic_id',$clinic_id)->from("sms_history")->count_all_results();

//get finaly count sms
$final_count_sms = $total_available - $count;


// get clinics from user table
$this->db->where('clinic_id', $clinic_id);
$q = $this->db->get('users');
$data = $q->result_array();

// get clinic name
$clinic_name = $data[0]['clinic_name'];

}

Answer 1

是的，在一个查询中是可能的:

尝试以下查询:

SELECT ( SELECT total_available FROM sms WHERE clinic_id = '1' ) - COUNT( sms_history.clinic_id) FROM sms_history JOIN sms ON sms_history.clinic_id= sms.clinic_id 

如果您有多个 ID，请执行以下操作，

您的 sms_history 中有 clinc_id 是 1,2。

所以你可以像这样制作它的数组:

$ids = array('1','2');
for($i = 0 ; $i <= $ids.length; $i++)
{

      $sql = "SELECT ( SELECT total_available FROM sms WHERE clinic_id = '".$ids[i]."' ) - COUNT( sms_history.clinic_id) FROM sms_history JOIN sms ON sms_history.clinic_id= sms.clinic_ids WHERE sms_history.clinic_id =  '".$ids[i]."'";
      /* after your generate your query you will get substration of it. so at same time you need to fire update query for "available_message" */

}

试试上面的代码，我希望这对你有用。