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
<?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();
}
}
?>
<?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);
}
}
?>
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.