Brett Power
My feedback
3 results found
-
27 votes
In planning.. MySQL only
An error occurred while saving the comment -
4 votesBrett Power
shared this idea
·
-
6 votes
An error occurred while saving the comment Brett Power
commented
I do this with database triggers in MySQL. For every critical table that I have there is an audit table that has the exact same structure with a few additional columns that log if it is a record update, delete or insert and the date/time that this action occurs. Additionally all of my tables have columns to log who modified the record last and the date/time that the modification occurred.
In my opinion it's best to leave these activities to the database directly and not to the PHP code. Using triggers is great because the database performs the actions automatically so there is no way that the system can fail.
This is my solution: complex SQL query to an array and then onto the grid. This allows for faster modifications of the query without re-writing a VIEW at the database-level.
$sql="SELECT
(CASE
WHEN (`aperto`.tickets.parent > 1)
THEN CONCAT(`aperto`.tickets.`parent`, `aperto`.tickets.child)
ELSE (CONCAT(`aperto`.tickets.id, 'A'))
END) AS 'alpha_id',
`aperto`.tickets.id AS `id`,
`aperto`.tickets.alternate_id AS `alternate_id`,
`aperto`.tickets.parent AS `parent_id`,
`aperto`.tickets.child AS `child_id`,
DATE_FORMAT(`aperto`.`tickets`.`date_entered`,'%m/%d/%Y') AS `date_entered`,
`aperto`.tickets.status AS `status`,
`aperto`.tickets.type AS `type`,
`aperto`.tickets.title AS `title`,
`aperto`.tickets.error_code AS `error_code`,
`login`.`users`.user_name AS `Technician`,
CONCAT('<b>Dealer:</b> ', `aperto`.dealer_account.name,'<BR /><b>Customer:</b> ',`aperto`.customer_account.name) AS `Accounts`
FROM `aperto`.tickets
LEFT JOIN `aperto`.accounts AS `dealer_account` ON `aperto`.`dealer_account`.id = `aperto`.tickets.dealer_account_id
LEFT JOIN `aperto`.accounts AS `customer_account` ON `aperto`.`customer_account`.id = `aperto`.tickets.customer_account_id
LEFT JOIN `login`.`users` ON `login`.`users`.user_id = `aperto`.`tickets`.user_id
";
$result = $db->query($sql);
$case_array = array();
while($row = $result -> fetch_array())
{
// Assign $Row array to $Rows array
$product_array[] = $row;
}
$product_dg = new C_DataGrid($product_array, "alpha_id", "product_array");