*************

SQL code: Upload Copy Format Clear

CREATE TABLE `asset_audit` ( `audit_id` int(16) NOT NULL, `audit_asset_id` varchar(16) NOT NULL, `audit_date` date NOT NULL, `audit_present` varchar(16) NOT NULL, `audit_repairs` varchar(16) NOT NULL, `audit_comment` varchar(256) NOT NULL ) ENGINE = MyISAM DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci; CREATE TABLE `asset` ( `asset_id` int(32) NOT NULL, `type` varchar(64) NOT NULL, `supplier` varchar(16) NOT NULL, `asset_name` varchar(64) NOT NULL, `asset_desc` varchar(128) NOT NULL, `qty` int(32) NOT NULL, `serial` varchar(32) NOT NULL, `gcyo_id` varchar(32) NOT NULL, `purch_date` date NOT NULL, `purc_price` varchar(32) NOT NULL, `invoice_num` varchar(32) NOT NULL, `asset_image` varchar(256) NOT NULL ) ENGINE = MyISAM DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci; INSERT INTO `asset_audit` (`audit_id`, `audit_asset_id`, `audit_date`, `audit_present`, `audit_repairs`, `audit_comment`) VALUES (8, '1', '2023-03-01', '1', '0', 'this was the second audit of this piano, what a fine piano it is'), (1, '1', '2023-02-01', '1', '0', 'this is the first audit date'), (5, '1', '2023-04-04', '1', '0', 'these are the comments'), (6, '1', '2023-04-04', '1', '1', 'repairs required'), (7, '1', '2023-04-04', '0', '0', 'not in stock'), (9, '3', '2023-04-02', '1', '0', 'this is the second asset'); INSERT INTO `asset` (`asset_id`, `type`, `supplier`, `asset_name`, `asset_desc`, `qty`, `serial`, `gcyo_id`, `purch_date`, `purc_price`, `invoice_num`, `asset_image`) VALUES (1, '1', '1', 'Grand Piano', 'big piano on wheels', 1, '222333', '1001', '2023-04-03', '13,000.65', '1420TY445', 'images/1_Grand Piano.png'), (3, '9', '3', 'Camera', 'avkans camera', 1, '22233333', '1004', '2023-03-28', '1,200.00', '1420TY44533', '');

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?php echo" <!DOCTYPE html> <html lang='en'> <head> </head> <body> <div class='container-fluid mt-5'> <div class='row'> <div class='col-sm-8 border-end border-success'> <h5 class='text-success'>Asset Administration</h5> "; $sql="SELECT * FROM asset LEFT JOIN asset_audit ON asset.asset_id = asset_audit.audit_asset_id WHERE asset_audit.audit_id = ( SELECT MAX(audit_id) FROM asset_audit WHERE asset.asset_id = asset_audit.audit_asset_id ) ORDER BY asset.type ASC, asset.asset_name ASC"; $result = mysqli_query($mysqli, $sql); print_r($sql); if($result->num_rows == 0){ echo " <div class='container mt-3'> <h5>Sorry, there are no asset's registered in the database</h5> <div> "; } if($result->num_rows > 0){ // display asset results echo " <div class='container mt-5'> <div class='row'> <div class='col-sm-6'> <div class='input-group'> <div class='input-group-append'> <span class='input-group-text'><i class='fa fa-search'></i>  </span> </div> <input class='form-control' id='searchInput' type='text' placeholder='Use quick search to find an asset......just start typing' /> </div> <font color='#008000'>Present and not in need of repair at last audit</font></br> <font color='#FF0000'>Present but in need of repair at the last audit</font></br> <font color='#FFA500'>Not present at the last audit</font></br> </div> <div class='col-sm-2 text-center ps-2'> <form method='POST' action=''> <button type='submit' name='add_asset' class='btn btn-sm btn-success' title='Add a new asset'><i class='fa-solid fa-file-circle-plus'></i>  Add a New Asset</button> </form> </div> <div class='col-sm-2 text-center ps-2'> <form method='POST' action=''> <button type='submit' name='manage_asset_type' class='btn btn-sm btn-success' title='Manage Asset Types'><i class='fa-solid fa-cog fa-spin' style='--fa-animation-duration: 15s;'></i>  Manage Asset Types</button> </form> </div> <div class='col-sm-2 text-ceter ps-2'> <form method='POST' action=''> <button type='submit' name='manage_supplier' class='btn btn-sm btn-success' title='Manage Suppliers'><i class='fa-solid fa-cog fa-spin' style='--fa-animation-duration: 15s;'></i>  Manage Suppliers</button> </form> </div> </div> </div> </br> <div> <table id='assets' class='table table-hover table-sm table-responsive header-fixed'> <thead> <tr> <th>Asset #</th> <th>Asset Type</th> <th>Name</th> <th>Serial #</th> <th>Supplier</th> <th>Phone</th> <th>Last Audit</th> <th></th> <th></th> <th></th> <th></th> </tr> </thead> "; // output data of each row while($row = mysqli_fetch_assoc($result)) { $asset_id = $row['asset_id']; $type1 = $row['type']; $asset_type = $row['asset_type']; $asset_sub_type = $row['asset_sub_type']; $type= $asset_type . " - " . $asset_sub_type; $asset_name = $row['asset_name']; $asset_desc = $row['asset_desc']; $qty = $row['qty']; $serial = $row['serial']; $gcyo_id = $row['gcyo_id']; $purch_date1 = $row['purch_date']; $purch_date2 = strtotime($purch_date1); $purch_date = date('d-m-Y',$purch_date2); $purc_price = $row['purc_price']; $invoice_num = $row['invoice_num']; $asset_image = $row['asset_image']; $supplier_name = $row['supplier_name']; $supplier_add_line_1 = $row['supplier_add_line_1']; $supplier_add_line_2 = $row['supplier_add_line_2']; $supplier_suburb = $row['supplier_suburb']; $supplier_state = $row['supplier_state']; $supplier_postcode = $row['supplier_postcode']; $supplier_phone = $row['supplier_phone']; $audit_date1 = $row['audit_date']; $audit_date2 = strtotime($audit_date1); $audit_date = date('d-m-Y',$audit_date2); $audit_present = $row['audit_present']; $audit_repairs = $row['audit_repairs']; if(($audit_present == "1") & ($audit_repairs == "0") ) { $font_color='#008000'; } if ($audit_present == "0"){ $font_color='#FFA500'; } if ($audit_repairs == "1"){ $font_color='#FF0000'; } $audit_comment = $row['audit_comment']; echo " <tbody> <tr> <td><font color='$font_color'>$gcyo_id</font></td> <td><font color='$font_color'>$type</font></td> <td><font color='$font_color'>$asset_name</font></td> <td><font color='$font_color'>$serial</font></td> <td><font color='$font_color'>$supplier_name</font></td> <td><font color='$font_color'>$supplier_phone</font></td> <td><font color='$font_color'>$audit_date</font></td> <td> <button type='button' class='btn btn-success btn-sm' data-bs-toggle='modal' title='Edit details for Asset ID: $gcyo_id' data-bs-target='#edit_asset$asset_id'><span><i class='fa fa-edit'></i></span> </td> <td> <button type='button' class='btn btn-success btn-sm' data-bs-toggle='modal' title='Add new audit details for Asset ID: $gcyo_id' data-bs-target='#add_new_audit$asset_id'><span><i class='fa-solid fa-file-circle-plus'></i></span> </td> <td> <button type='button' class='btn btn-success btn-sm' data-bs-toggle='modal' title='Read last audit details for Asset ID: $gcyo_id' data-bs-target='#read_last_audit$asset_id'><span><i class='fa-solid fa-book-open-reader'></i></span> </td> <td>"; if(isset($asset_image) & !empty($asset_image)){ echo " <button type='button' class='btn btn-success btn-sm' data-bs-toggle='modal' title='Asset image is available for $asset_name' data-bs-target='#asset_image$asset_id'><span><i class='fa-regular fa-file-image'></i></span> </td>"; } else{ echo " <form method='POST' action='upload_image.php'> <input type='hidden' name='asset_id' value='$asset_id' /> <input type='hidden' name='asset_name' value='$asset_name' /> <input type='hidden' name='gcyo_id' value='$gcyo_id' /> <button type='submit' name='asset_image' class='btn btn-sm btn-success' title='Upload asset image for $asset_name'><i class='fa-solid fa-upload'></i></button> </form> </td> "; } echo " </tr> </tbody> "; } echo " </table> </div> "; } echo" </div> </div> </body> </html>"; ?>

PHP version:

Show: Warnings Notice

Result: Copy Clear

Like the site? Share it!

Have a questions? Ask in our telegram chat!

PHP versions: PHP 5.6, PHP 7.4, PHP 8.0, PHP 8.1, PHP 8.2.13
RDBMS versions: MariaDB 11.4, MariaDB 11.5, MariaDB 10, MariaDB 10 Sakila (ReadOnly), MySQL 5.7, MySQL 5.7 Sakila (ReadOnly), MySQL 8.0, MySQL 8.0 Sakila (ReadOnly), SQLite 3, PostgreSQL 10 Bookings (ReadOnly), PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, PostgreSQL 14, PostgreSQL 15, MS SQL Server 2017, MS SQL Server 2019, MS SQL Server 2022, MS SQL Server 2022 AdventureWorks (ReadOnly), Firebird 4.0, Firebird 4.0 (Employee), Oracle Database 19c (HR), Oracle Database 21c, Oracle Database 23c Free, SOQOL

PHPize Online / SQLize Online / SQLtest Online