Dealing with Delete Implementation in PHP/MySQL

Posted by admin at January 1, 2017

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']; ?>"

  1. the GET parameter 'a' is the CRUD action to take, in this case 'd' for DELETE
  2. 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

Pages: 1 2 3 4 5


Suggested Read