+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Modifying data from database before building the result array (/showthread.php?tid=80178)
Modifying data from database before building the result array - Sinclaire - 09-23-2021
I'm planning to create an API for CRUD operations. Retrieving data from the database works flawlessly as usual and also returning is no issue. But when it comes to dates, I'm facing issues with the format. The dates are stored as DATETIME in the database and are automatically created at insert, but I want to deliver an unix timestamp when accessing via API. I've stripped down my code to the crucial parts:
PHP Code:
<?php
// Controllers/Config.php
namespace App\Controllers\API;
use CodeIgniter\RESTful\ResourceController;
use CodeIgniter\API\ResponseTrait;
use App\Models\ConfigModel;
class Config extends ResourceController
{
use ResponseTrait;
public function index()
{
$model = new ConfigModel();
return $this->respond($model->getConfig());
}
}
// Models/ConfigModel.php
namespace App\Models;
use CodeIgniter\Model;
class ConfigModel extends Model
{
protected $table = 'configuration';
protected $primaryKey = 'id';
protected $allowedFields = ['name','value','is_default'];
protected function getCurrentConfig()
{
$query = $this->query('SELECT * FROM configuration WHERE id IN (SELECT MAX(id) FROM configuration GROUP BY name)');
return $query->getResult();
}
public function getConfig()
{
return $this->getCurrentConfig();
}
}
Code:
// Result
[
{
"id": "1",
"name": "ticket_number_format",
"value": "default",
"is_default": "1",
"create_timestamp": "2021-09-22 09:11:07" // need: 1632294667
},
{
"id": "3",
"name": "ticket_number_format_default_alphanumeric",
"value": "true",
"is_default": "1",
"create_timestamp": "2021-09-22 09:21:40" // need: 1632295300
},
// ...
}I know that I could simply select each field in separate and enclose UNIX_TIMESTAMP(create_timestamp) but I'm planning to operate on tables with much more columns in the future as well. Also I may use the query builder instead of custom SQL for more basic selects. I've red about the getCustomResultObject(), but I can't figure out how to use it.
RE: Modifying data from database before building the result array - Sinclaire - 09-24-2021
Okay nevermind, I managed to solve it by myself:
I figured out I was on the right path. The key to this issue are those entity classes. I added following entity class:
PHP Code:
// Entities/Config.php
namespace App\Entities;
use CodeIgniter\Entity\Entity;
use CodeIgniter\I18n\Time;
class Config extends Entity
{
public function getCreateTimestamp()
{
$this->attributes['create_timestamp'] = $this->mutateDate($this->attributes['create_timestamp']);
return $this->attributes['create_timestamp']->getTimestamp();
}
}
PHP Code:
// Models/ConfigModel.php
namespace App\Models;
use CodeIgniter\Model;
class ConfigModel extends Model
{
protected $table = 'configuration';
protected $primaryKey = 'id';
protected $allowedFields = ['name','value','is_default'];
// I think this is only needed for those
// built-in CRUD function like findAll()
protected $returnType = 'App\Entities\Config';
protected function getCurrentConfig()
{
$query = $this->query('SELECT * FROM configuration WHERE id IN (SELECT MAX(id) FROM configuration GROUP BY name)');
// Use the entity here
return $query->getResult('App\Entities\Config');
}
public function getConfig()
{
return $this->getCurrentConfig();
}
}
Code:
// Result
[
{
"id": "1",
"name": "ticket_number_format",
"value": "default",
"is_default": "1",
"create_timestamp": 1632319867
},
{
"id": "3",
"name": "ticket_number_format_default_alphanumeric",
"value": "true",
"is_default": "1",
"create_timestamp": 1632320500
},
// ...
}RE: Modifying data from database before building the result array - InsiteFX - 09-24-2021
Here are a couple of methods that should do what you want, NOT TESTED.
PHP Code:
/**
* convertToTimestamp ()
* -----------------------------------------------------------------------
*/
if (! function_exists('convertToTimestamp'))
{
/**
* convertToTimestamp ()
* -------------------------------------------------------------------
*
* convert datetime object to timestamp
*
* @param string $datetime
* @return string
* @throws Exception
*/
function convertToTimestamp(string $datetime): string
{
$date = new DateTime($datetime);
return $date->getTimestamp();
}
}
/**
* convertToDatetime ()
* -----------------------------------------------------------------------
*/
if (! function_exists('convertToDatetime'))
{
/**
* convertToDatetime ()
* -------------------------------------------------------------------
*
* convert timestamp to datetime
*
* @param string $timestamp
* @return string
*/
function convertToDatetime(string $timestamp): string
{
$date = new DateTime();
$date->setTimestamp($timestamp);
return $date->format('Y-m-d H:i:s');
}
}
Let me know if they work for you.
RE: Modifying data from database before building the result array - Sinclaire - 09-24-2021
(09-24-2021, 01:42 AM)InsiteFX Wrote: Here are a couple of methods that should do what you want, NOT TESTED.
PHP Code:/**
* convertToTimestamp ()
* -----------------------------------------------------------------------
*/
if (! function_exists('convertToTimestamp'))
{
/**
* convertToTimestamp ()
* -------------------------------------------------------------------
*
* convert datetime object to timestamp
*
* @param string $datetime
* @return string
* @throws Exception
*/
function convertToTimestamp(string $datetime): string
{
$date = new DateTime($datetime);
return $date->getTimestamp();
}
}
/**
* convertToDatetime ()
* -----------------------------------------------------------------------
*/
if (! function_exists('convertToDatetime'))
{
/**
* convertToDatetime ()
* -------------------------------------------------------------------
*
* convert timestamp to datetime
*
* @param string $timestamp
* @return string
*/
function convertToDatetime(string $timestamp): string
{
$date = new DateTime();
$date->setTimestamp($timestamp);
return $date->format('Y-m-d H:i:s');
}
}
Let me know if they work for you.
Thank you for your fast answer! I'm not sure where to put those functions (do they belong in the entity?), but I managed to solve the issue by myself.
The Post #2 just needs to be approved.