PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
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', '');
Copy Clear
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>&nbsp;&nbsp;</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>&nbsp;&nbsp;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>&nbsp;&nbsp;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>&nbsp;&nbsp;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>"; ?>
Show:  
Copy Clear