Hello,
My purpose here is I want to show each transaction in one line in audit trail data
I want to show “personid” one for each transaction
I want to show “done_by” one for each transaction
I want to show “done_at” one for each transaction
I’ll do it for fifty pounds.
You must allow for the case of more than one data item by different users in a single second. For example, in your sample data, row id 4 and 5 could have occurred in the same second.
To produce the result you have stated, you would index/pivot the data when you retrieve it, using array indexes for personid, done_at, then done_by. To produce the output from that indexed/pivoted data, you would use four nested foreach() loops.
// array to hold the indexed/pivoted data
$data = [];
// assuming you are using the PDO extension, fetch the data, indexing/pivoting it using the pseronid, done_at, then done_by values
foreach($stmt as $row)
{
$data[ $row['personid'] ][ $row['done_at'] ][ $row['done_by'] ][] = $row;
}
// to produce the output -
foreach($data as $personid=>$arr1)
{
foreach($arr1 as $done_at=>$arr2)
{
foreach($arr2 as $done_by=>$arr3)
{
// at this point, you have a $personid, $done_at, and $done_by value.
// count($arr3) will tell you how many rows there are going to be in the output section, that can be used for the rowspan attribute in the personid, done_by, and done_at columns.
foreach($arr3 as $row)
{
}
}
}
}The markup you are trying to produce would look like (only the first 2 sections shown) -
<table>
<tr>
<th>person id</th>
<th>column name</th>
<th>old value</th>
<th>new value</th>
<th>done by</th>
<th>done at</th>
</tr>
<tr>
<td rowspan="3">1</td>
<td>fn</td>
<td>null</td>
<td>new fn</td>
<td rowspan="3">by1</td>
<td rowspan="3">at1</td>
</tr>
<tr>
<td>ln</td>
<td>null</td>
<td>new ln</td>
</tr>
<tr>
<td>age</td>
<td>null</td>
<td>37</td>
</tr>
<tr>
<td rowspan="1">1</td>
<td>age</td>
<td>37</td>
<td>20</td>
<td rowspan="1">by2</td>
<td rowspan="1">at2</td>
</tr>
</table>Thank you for the answer
I will try to do as you say
I made some changes.
Has it been added?
Has it been updated?
Has it been deleted?
$stmt = $urun->fetchAll(PDO::FETCH_ASSOC);
//I am using PDO
[id] => 1
[product_id] => 1
[product_code] => UCE-CT220L
[column_name] => product_code
[old_value] =>
[new_value] => UCE-CT220L
[created_by] => Adem GENÇ
[updated_by] =>
[done_at] => 2022-01-18 17:35:09
if(empty($row['updated_by'])){
echo "<td>".$row['created_by']."</td>";
echo "<td>Added</td>";
}else{
echo "<td>".$row['updated_by']."</td>";
echo "<td>Updated</td>";
}
echo "<td>".$row['product_code']."</td>";
echo "<td>".$row['column_name']."</td>";
echo "<td>".$row['old_value']."</td>";
echo "<td>".$row['new_value']."</td>";
echo "<td>".$row['done_at']."</td>";
echo "<td><input type=\"checkbox\" name=\"delete[]\" value=\"".$row['id']."\"></td>";
When you index/pivot the data, you would add a 4th ‘process’ index and you would place the relevant created_by, updated_by, and if present, deleted_by value into the existing done_by index.
foreach($stmt as $row)
{
if($row['created_by'])
{
$done_by = $row['created_by'];
$process = 'Added';
}
if($row['updated_by'])
{
$done_by = $row['updated_by'];
$process = 'Updated';
}
/*
if($row['deleted_by'])
{
$done_by = $row['deleted_by'];
$process = 'Deleted';
}
*/
$data[ $row['product_code'] ][ $row['done_at'] ][ $done_by ][ $process ][] = $row;
}In the code producing the output, you would add a 4th foreach(){} loop for the process index.
I think I understand a little
I’m trying to do it I will write the result
I will use the following code to understand that the data has been deleted
if($row['column_name'] == 'is_deleted' && $row['old_value'] == '0' && $row['new_value']== '1')
{
$done_by = $row['updated_by'];
$process = 'Deleted';
}
Thank you
Thank you very much, I did
I wanted to apply the date and checkbox rowspan on the right, but the date can change in one operation.
Example: I think it could be 2022-01-28 11:17:55 and 2022-01-28 11:17:56
Also, how to make one checkbox since the IDs are unique for each row?
What is your recommendation for these two columns? for “Date and Checkbox”
echo '
<table id="zero_config-" class="table table-striped table-bordered">
<colgroup span="8">
<col style="width:8%"></col>
<col style="width:5%"></col>
<col style="width:8%"></col>
<col style="width:10%"></col>
<col style="width:15%"></col>
<col style="width:15%"></col>
<col style="width:10%"></col>
<col style="width:1%"></col>
</colgroup>
<thead>
<tr>
<th>Yapan</td>
<th>İşlem</th>
<th>Ürün Kodu</th>
<th>Sutün Adı</th>
<th>Önceki veri</th>
<th>Sonraki veri</th>
<th>Tarih</th>
<th>
<label class="customcheckbox mb-3">
<input type="checkbox" id="mainCheckbox" />
<span class="checkmark"></span>
</label>
</th>
</tr>
</thead>
<tbody class="customtable">
';
// array to hold the indexed/pivoted data
$product_codes = $conn->prepare(" SELECT * FROM products_audit_trail ");
$product_codes->execute();
$stmt = $product_codes->fetchAll(PDO::FETCH_ASSOC);
$data = [];
// assuming you are using the PDO extension, fetch the data, indexing/pivoting it using the urun_id, done_at, then created_by values
foreach($stmt as $row)
{
if($row['created_by'])
{
$done_by = $row['created_by'];
$process = 'Added';
}
if($row['updated_by'])
{
$done_by = $row['updated_by'];
$process = 'Updated';
}
if($row['column_name'] == 'is_deleted' && $row['old_value'] == '0' && $row['new_value']== '1')
{
$done_by = $row['updated_by'];
$process = 'Deleted';
}
$data[ $row['product_code'] ][ $row['done_at'] ][ $done_by ][ $process ][] = $row;
}
// to produce the output -
foreach($data as $product_code=>$arr1)
{
foreach($arr1 as $done_at=>$arr2)
{
$uc = 0;
foreach($arr2 as $done_by=>$arr3)
{
foreach($arr3 as $process=>$arr4)
{
$rowspan = count($arr4);
echo "<tr>\n";
echo " <td rowspan=\"".$rowspan."\">".$done_by."</td>\n";
echo " <td rowspan=\"".$rowspan."\">".$process."</td>\n";
echo " <td rowspan=\"".$rowspan."\">".$product_code."</td>\n";
// at this point, you have a $urun_id, $done_at, and $created_by value.
// count($arr3) will tell you how many rows there are going to be in the output section, that can be used for the rowspan attribute in the urun_id, created_by, and done_at columns.
foreach($arr4 as $row)
{
echo " <td>".$row['column_name']."</td>\n";
echo " <td>".$row['old_value']."</td>\n";
echo " <td>".$row['new_value']."</td>\n";
echo " <td>".$row['done_at']."</td>\n";
echo " <td><input type=\"checkbox\"></td>\n";
echo "</tr>\n<tr>\n";
}
}
}
}
}
echo "
</tbody>\n
</table>
";
I would put the code for all the columns using the rowspan inside the inner-most foreach(){} loop, but only output the html for them on the first pass through the loop. To do this, define and set a variable to true, such as $first = true; before the start of the loop (at the same place where the $rowspan … line is at), then test if $first is true to output the html, inside the loop. At the end of the code inside the loop, set $first to false.
The logging code should use the same date/time value for all the rows that are inserted for a single operation. If not, altering the code so that it does would be the easiest way of dealing with this.
Set the check-box value to a comma separated list of the ids. You can get the id column values for the set of rows (see array_column()) and implode it with a comma between the values. This will work as expected for one or more rows, i.e. if there is only one row, you will get only that single id, if there are two or more rows, you will get a comma separated list of ids.
Thank you so much
I tried to do as much as I understood and as I could.
echo '
<table id="zero_config-" class="table table-striped table-bordered">
<colgroup span="8">
<col style="width:8%"></col>
<col style="width:5%"></col>
<col style="width:8%"></col>
<col style="width:10%"></col>
<col style="width:15%"></col>
<col style="width:15%"></col>
<col style="width:10%"></col>
<col style="width:1%"></col>
</colgroup>
<thead>
<tr>
<th>Yapan</td>
<th>İşlem</th>
<th>Ürün Kodu</th>
<th>Sutün Adı</th>
<th>Önceki veri</th>
<th>Sonraki veri</th>
<th>Tarih</th>
<th>
<label class="customcheckbox mb-3">
<input type="checkbox" id="mainCheckbox" />
<span class="checkmark"></span>
</label>
</th>
</tr>
</thead>
<tbody class="customtable">
';
// array to hold the indexed/pivoted data
$product_codes = $conn->prepare(" SELECT * FROM products_audit_trail ");
$product_codes->execute();
$stmt = $product_codes->fetchAll(PDO::FETCH_ASSOC);
$data = [];
// assuming you are using the PDO extension, fetch the data, indexing/pivoting it using the urun_id, done_at, then created_by values
foreach($stmt as $row)
{
if($row['created_by'])
{
$done_by = $row['created_by'];
$process = 'Added';
}
if($row['updated_by'])
{
$done_by = $row['updated_by'];
$process = 'Updated';
}
if($row['column_name'] == 'is_deleted' && $row['old_value'] == '0' && $row['new_value']== '1')
{
$done_by = $row['updated_by'];
$process = 'Deleted';
}
$data[ $row['product_code'] ][ $row['done_at'] ][ $done_by ][ $process ][] = $row;
}
// to produce the output -
foreach($data as $product_code=>$arr1)
{
foreach($arr1 as $done_at=>$arr2)
{
$uc = 0;
foreach($arr2 as $done_by=>$arr3)
{
foreach($arr3 as $process=>$arr4)
{
$rowspan = count($arr4);
$ids = array_column($arr4, 'id');
$comma_ids = implode(",", $ids);
echo "<tr>\n";
echo " <td rowspan=\"".$rowspan."\">".$done_by."</td>\n";
echo " <td rowspan=\"".$rowspan."\">".$process."</td>\n";
echo " <td rowspan=\"".$rowspan."\">".$product_code."</td>\n";
// at this point, you have a $urun_id, $done_at, and $created_by value.
// count($arr3) will tell you how many rows there are going to be in the output section, that can be used for the rowspan attribute in the urun_id, created_by, and done_at columns.
foreach($arr4 as $row)
{
echo " <td>".$row['column_name']."</td>\n";
echo " <td>".$row['old_value']."</td>\n";
echo " <td>".$row['new_value']."</td>\n";
if($i == 0){
echo " <td rowspan=\"".$rowspan."\">".$row['done_at']."</td>\n";
echo " <td rowspan=\"".$rowspan."\"><input type=\"checkbox\"></td>\n";
}
echo "</tr>\n<tr>\n";
$i++;
}
unset($i);
}
}
}
}
echo "
</tbody>\n
</table>
";
result
The table for the date is as follows, I am not doing anything with the date
TABLE `urunler_audit_trail` (
`done_at` timestamp NOT NULL DEFAULT current_timestamp()
The date/time can change for the rows in the audit table that correspond to a single query. This would result in more than one section in the reporting output. Given how infrequently this will occur, is this really a problem that needs solving?
I think there will be no problem with the script I am currently using.
I’m not doing any action on the php page about the audit table
If the solution is easy, I would like to know
The audit trail system was an excellent thing. Liked it very much
It should be fairly easy to use the same current_timestamp() value in all the queries. You would remove the DEFAULT current_timestamp() part from the table definition, and add code to each trigger. The following should (untested) work -
DECLARE tempVar varchar(19);SET tempVar = current_timestamp();
done_at column to each query and use tempVar as the value.In looking at what the (original) trigger code was doing, you can eliminate the above php logic by doing this in the triggers.
In the trigger code, you know when an insert, update, or update/delete is being executed. There should be done_by (what the trigger code originally had) and process columns in the logging table. You would use the appropriate created_by or updated_by value for the done_by column (what the trigger code originally had), and use 'Added', 'Updated', or 'Deleted' for the process column. This would give you the expected data in the query result, so, you won’t need the php conditional logic.
Another advantage of having a process column in the logging table is you can now write simple queries using that value, such as getting all the insert data for a product code, …
I think you mean inquiry by product code?
I am using DataTable for easy sorting of tables but dataTable does not support rowspan I guess
I couldn’t fully understand this.
I made some changes to separate what the process is.
The “created_by” column writes the name of the logged in member while adding a product, and the “updated_by” column remains blank.
With the “if” condition, if the “updated_by” column is empty, it means that the product has been added.
When the product is updated, it writes the name of the updated member in the “updated_by” column.
If the “updated_by” column is not empty, it means that the product has been updated.
In column “column_name”, if the column name is “is_deleted” and its new value is 1, it means it has been deleted.
For the audit trail record for deletion, I first update the is_deleted column from the default 0 value to 1 value by updating and then I delete the row completely
I did not fully understand
Can you show in the example below?
create table persons_audit_trail(id int NOT NULL AUTO_INCREMENT,
Personid int NOT NULL,
column_name varchar(255),
old_value varchar(255),
new_value varchar(255),
done_by varchar(255) NOT NULL,
done_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id));
DELIMITER $
CREATE TRIGGER persons_create
AFTER INSERT
ON persons FOR EACH ROW
BEGIN
insert into persons_audit_trail(Personid, column_name, new_value, done_by) values(NEW.Personid,'FirstName',NEW.FirstName,NEW.created_by);
insert into persons_audit_trail(Personid, column_name, new_value, done_by) values(NEW.Personid,'LastName',NEW.LastName,NEW.created_by);
insert into persons_audit_trail(Personid, column_name, new_value, done_by) values(NEW.Personid,'Age',NEW.Age,NEW.created_by);
END$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER persons_update
AFTER UPDATE
ON persons FOR EACH ROW
BEGIN
IF OLD.FirstName <> new.FirstName THEN
insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'FirstName',OLD.FirstName,NEW.FirstName,NEW.updated_by);
END IF;
IF OLD.LastName <> new.LastName THEN
insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'LastName',OLD.LastName,NEW.LastName,NEW.updated_by);
END IF;
IF OLD.Age <> new.Age THEN
insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'Age',OLD.Age,NEW.Age,NEW.updated_by);
END IF;
IF OLD.is_deleted <> new.is_deleted THEN
insert into persons_audit_trail(Personid, column_name, old_value, new_value, done_by) values(NEW.Personid,'is_deleted',OLD.is_deleted,NEW.is_deleted,NEW.updated_by);
END IF;
END$$
DELIMITER ;
Also I can’t sort by date “ORDER BY done_at DESC”
This is the date format: “2022-01-27 10:27:03”
Maybe now I understand
Create a column named “process”
“Added” when adding product
“Updated” when the product is updated
“Deleted” when the product is deleted
Easy to understand what is done with a single column