Adding Delete:
Next we add the functionality to ‘delete’ or archive our records.
Adding the status field
We modified the SQL of the table to add a new column called `status`. This column will be used to determine the show or hide status of the individual records. So the `tbl_airports` SQL will look like this:
ALTER TABLE `tbl_airports` ADD `status` INT( 11 ) DEFAULT '0' AFTER `airport_country_id`
The code above adds an additional column named `status` which will hold only numeric indices representing record status. We need an additional table to define these indices: 1 for Published, 2 Archive, 3 Delete
--
-- Table structure for table `tbl_status`
--
DROP TABLE IF EXISTS `tbl_status`;
CREATE TABLE IF NOT EXISTS `tbl_status` (
`status_id` int(11) NOT NULL AUTO_INCREMENT,
`status_code` varchar(5) DEFAULT NULL,
`status_title` varchar(255) DEFAULT NULL,
PRIMARY KEY (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `tbl_status`
--
INSERT INTO `tbl_status` (`status_id`, `status_code`, `status_title`) VALUES
('1', 'P', 'Published'),
('2', 'A', 'Archive'),
('3', 'D', 'Delete');
Adding the Delete link
Next, we will modify the table HTML to include an Actions column and delete links for each row.
<?php else if ($action == 'r') { ?>
<?php //*********************************************READ ?>
<hr>
<strong>Read Airports</strong><br><br>
<?php
// build the read query
$sql = get_read_sql('tbl_airports')
// arrange by code
.' ORDER BY `airport_code` ASC'
;
// process the sql for table
$mysql_res = send_sql($sql, $conn);
// get the first row
$row = $mysql_res['first_row'];
?>
<table width="100%">
<thead>
<tr>
<td>SN</td>
<td>Code</td>
<td>IATA</td>
<td>Airport Name</td>
<td>City</td>
<td>Country</td>
<td>Actions</td>
</tr>
</thead>
<tbody>
<?php
// display the table
for ($i=0; $i<$total_rows; $i++)
{
?>
<tr>
<td><?php echo $i+1; ?></td>
<td><?php echo $row['airport_code']; ?></td>
<td><?php echo $row['airport_iata_code']; ?></td>
<td><?php echo $row['airport_desc']; ?></td>
<td><?php echo $row['airport_city']; ?></td>
<td><?php echo $row['airport_notes']; ?></td>
<td>
<a href="index.php?a=d&rid=<?php echo $row['airport_id']; ?>">delete</a>
</td>
</tr>
<?php } ?>
</tbody>
</table>
<?php //*********************************************READ ?>
<?php } ?>
Lets take a look at the line
<td>
<a href="index.php?a=d&rid=<?php echo $row['airport_id']; ?>">delete</a>
</td>
For the href property:
href="index.php?a=d&rid=<?php echo $row['airport_id']; ?>"
- the GET parameter 'a' is the CRUD action to take, in this case 'd' for DELETE
- the GET parameter 'rid' is the record id set to the current airport
Adding the Delete Confirmation Page:
We insert this code below to confirm the deletion of the record
<?php else if ($action == 'd') { ?>
<?php //*********************************************DELETE ?>
<hr>
<?php
// build the read query
$sql = get_read_sql('tbl_airports')
// the current record
.' AND `airport_id` = '.$rid
;
// process the sql for table
$mysql_res = send_sql($sql, $conn);
// get the first row
$row = $mysql_res['first_row'];
?>
<strong>Confirm Deletion</strong><br><br>
Are you sure you wish to delete the record <strong>(<?php echo $row['airport_desc'] .' ['. $row['airport_code'].']'; ?>)</strong>?<br><br>
<a href="?a=d&rid=<?php echo $rid; ?>&cfm=yes">Yes, delete the record</a>
<a href="#">No, do not delete</a><br><br>
<?php //*********************************************DELETE ?>
<?php } ?>
Adding the Delete Code:
Next we add a modifications to the Delete Confirmation Page to ‘delete’ the record on confirmation.
<?php else if ($action == 'd') { ?>
<?php //*********************************************DELETE ?>
<hr>
<?php
// get the confirmation GET parameter
$get_cfm = mysql_real_escape_string($_GET['cfm']);
// if the confirmation is yes
if ($get_cfm == 'yes')
{
// process the DELETE
delete_record('tbl_airports', $rid, $conn);
}
?>
<?php
// build the read query
$sql = get_read_sql('tbl_airports')
// the current record
.' AND `airport_id` = '.$rid
;
// process the sql for table
$mysql_res = send_sql($sql, $conn);
// get the first row
$row = $mysql_res['first_row'];
?>
<strong>Confirm Deletion</strong><br><br>
Are you sure you wish to delete the record <strong>(<?php echo $row['airport_desc'] .' ['. $row['airport_code'].']'; ?>)</strong>?<br><br>
<a href="?a=d&rid=<?php echo $rid; ?>&cfm=yes">Yes, delete the record</a>
<a href="#">No, do not delete</a><br><br>
<?php //*********************************************DELETE ?>
<?php } ?>
So what we did was we added the code segment below to process the delete after the user confirms by clicking on the ‘yes’ link.
<?php
// get the confirmation GET parameter
$get_cfm = mysql_real_escape_string($_GET['cfm']);
// if the confirmation is yes
if ($get_cfm == 'yes')
{
// process the DELETE
delete_record('tbl_airports', $rid, $conn);
}
?>
To define the delete_record(); function we add the following code segment to the crud.php file
<?php
function get_delete_sql($tbl, $rid, $conn)
{
// get the columns in the table
{
$sql_col = 'SHOW COLUMNS FROM '.$tbl;
$rs_col = mysql_query($sql_col, $conn) or die(mysql_error());
$row_col = mysql_fetch_assoc($rs_col);
// $total_cols = mysql_num_rows($rs_col);
}
// the first column is (should be) the table primary key field; so we save it
$key_fld = $row_col['Field'];
// build the delete sql
$sql = 'UPDATE'
.' `'.$tbl.'` SET'
// set the status to 3 : DELETE
.' `'.$tbl.'`.`status` = "3"'
.' WHERE 1'
// for the selected record
.' AND `'.$tbl.'`.`'.$key_fld.'` = "'.mysql_real_escape_string($rid).'"'
;
// return the sql
return $sql;
}
function delete_record($tbl, $rid, $conn)
{
// process the DELETE
$sql = get_delete_sql('tbl_airports', $rid, $conn);
// process the sql for table
$mysql_res = send_sql($sql, $conn);
// get the first row
$row = $mysql_res['first_row'];
// the message
$msg = 'Error deleting record!';
if ($row)
{
$msg = 'Record successfully deleted!';
}
// redirect to the home page
redir('index.php?&msg='.$msg);
}
?>
Finally we develop the Recycle Bin
0 likes