php - How to Multiple Select by Data Description
问题描述
I having trouble to selecting datas from my DB, could you please help me to solve these ;
these are my data in database :
id_abs photo date time desc id_user
------------------------------------------------------------
1 p.png 2021-02-03 07.10 in 13
2 q.png 2021-02-03 18.20 out 13
3 r.png 2021-04-03 07.04 in 15
4 s.png 2021-04-03 18.30 out 15
5 t.png 2021-05-03 07.05 in 13
6 u.png 2021-05-03 18.10 out 13
and this is the table of output from DB that i want to select :
Nb : i want to select where id_user = 13
DATE PHOTO_IN PHOTO_OUT IN OUT
----------------------------------------------------------
2021-02-03 p.png q.png 07.10 18.20
2021-02-05 t.png u.png 07.05 18.10
解决方案
You can use subquery inside query select in your Model. You can try this instead
- Example Subquery in Model
<?php
namespace App\Models;
use CodeIgniter\Database\BaseBuilder;
use CodeIgniter\Model;
class M_user extends Model {
protected $table = 'user_absen as UB';
protected $primaryKey = 'id';
public function getData($user_id = 0) {
$this->select('
(SELECT
UBX.date
FROM
user_absen UBX
WHERE
UBX.user_id = UB.user_id
AND UBX.date = UB.date
GROUP BY
UBX.user_id,
UBX.date) AS `DATE`,
(SELECT
UBX.photo
FROM
user_absen UBX
WHERE
UBX.user_id = UB.user_id
AND UBX.date = UB.date
AND UBX.desc = 'in'
GROUP BY
UBX.user_id,
UBX.date) AS `PHOTO_IN`,
(SELECT
UBX.photo
FROM
user_absen UBX
WHERE
UBX.user_id = UB.user_id
AND UBX.date = UB.date
AND UBX.desc = 'out'
GROUP BY
UBX.user_id,
UBX.date) AS `PHOTO_OUT`,
(SELECT
UBX.time
FROM
user_absen UBX
WHERE
UBX.user_id = UB.user_id
AND UBX.date = UB.date
AND UBX.desc = 'in'
GROUP BY
UBX.user_id,
UBX.date) AS `IN`,
(SELECT
UBX.time
FROM
user_absen UBX
WHERE
UBX.user_id = UB.user_id
AND UBX.date = UB.date
AND UBX.desc = 'in'
GROUP BY
UBX.user_id,
UBX.date) AS `OUT`
');
$this->groupBy('UB.user_id, UB.date');
if (isset($user_id) && $user_id != 0) {
$this->where('UB.user_id', $user_id);
}
// Default output as Array
$this->get()->getResult();
}
}
?>
- Controller
<?php
namespace App\Controllers;
use App\Controllers\BaseController;
use App\Models\M_user;
class ControllerPengadaan extends BaseController {
protected $m_user;
public function __construct() {
$this->m_user = new M_user();
}
public function index() {
$data['data_user'] = $this->m_user->getData(13);
echo view('v_index', $data);
}
}
?>
- View
v_index.php
<table>
<tr>
<th>DATE</th>
<th>PHOTO_IN</th>
<th>PHOTO_OUT</th>
<th>IN</th>
<th>OUT</th>
</tr>
<?php
foreach($data_user as $data) {
echo '
<tr>
<td>'.$data['DATE'].'</td>
<td>'.$data['PHOTO_IN'].'</td>
<td>'.$data['PHOTO_OUT'].'</td>
<td>'.$data['IN'].'</td>
<td>'.$data['OUT'].'</td>
</tr>
';
}
?>
</table>
I hope you are helped.
推荐阅读
- docker - 通过 kubectl 暴露服务时,从节点主机上没有监听端口
- javascript - 在循环 ForEach 之前等待确认
- html - 导航项下方带有子菜单的全宽下拉菜单
- selenium - 范围报告仅保存 Jenkins 中最后一项作业的最后一项结果
- c# - 如何计算 3d 扫描的腹部周长?
- java - 当二维数组 [][] 和数组 [] 之间的赋值是合法的?
- powershell - 删除一个txt文件除第一行以外的内容
- javascript - 如何修复一个简单的 js 计算器(返回未定义)
- elasticsearch - 如何在弹性搜索中加载数据?
- python - 如何暂停 Python 脚本并让用户在 Linux 终端中输入