Home arrow PHP arrow Page 4 - Deleting Database Records with an Abstraction and Foreign Key Constraints

The MySQL abstraction class in action - PHP

In this fifth article of a six-part series, you will learn how to use foreign key constraints in MySQL to produce the deletion in cascade of rows in a child table when the related rows of the corresponding parent are deleted as well. We'll use a simple MySQL abstraction class to do the trick.

TABLE OF CONTENTS:
  1. Deleting Database Records with an Abstraction and Foreign Key Constraints
  2. Review: updating database rows in cascade through a MySQL abstraction class
  3. Enabling support for cascading row deletions
  4. The MySQL abstraction class in action
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 21, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In the previous section I redefined one of the two sample InnoDB tables (the one that stores comments) to enable the support for deletion in cascade of its rows. So the only thing that remains undone is building a script that first populates those tables with some fictional data, and then removes a particular blog post and its corresponding comments in one single step. First I’m going to save the earlier “MySQL” class to a file called “mysqlclass.php,” which will look like this:

<?php

 

class MySQL

{

private $result = NULL;

private $link = NULL;

 

// connect to MySQL

public function __construct($host, $user, $password, $database)

{

if (FALSE === ($this->link = mysqli_connect($host, $user, $password, $database)))

{

throw new Exception('Error : ' . mysqli_connect_error());

}

}

 

// perform query

public function query($query)

{

if (is_string($query) AND empty($query) === FALSE)

{

if (FALSE === ($this->result = mysqli_query($this->link, $query)))

{

throw new Exception('Error performing query ' . $query . ' Error message :' .mysqli_error($this->link));

}

}

}

 

// fetch row from result set

public function fetch()

{

if (FALSE === ($row = mysqli_fetch_object($this->result)))

{

mysqli_free_result($this->result);

return FALSE;

}

return $row;

}

 

// get insertion ID

public function getInsertID()

{

return mysqli_insert_id($this->link);

}

// count rows in result set

public function countRows()

{

if ($this->result !== NULL)

{

return mysqli_num_rows($this->result);

}

}

// implement destructor to close the database connection

function __destruct()

{

mysqli_close($this->link);

}

}

 

?>

Now, moving forward, here’s the script that fills the tables with some data and  deletes the first (and only) blog post, along with all of its comments:

require_once 'mysqlclass.php';

$db = new MySQL('host', 'user', 'password', 'test');

// insert new entry into 'blogs' database table

$db->query("INSERT INTO blogs (id, title, content, author) VALUES (NULL,'Title of the first blog entry', 'Content of the first blog entry', 'Alejandro Gervasio')");

 

$insid = $db->getInsertID();

// insert new comments into 'comments' database table

$db->query("INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, $insid, 'Commenting first blog entry', 'Susan Norton'), (NULL, $insid, 'Commenting first blog entry', 'Mary Wilson')");

 

// delete row in 'blogs' table; rows in 'comments' table are deleted automatically

 

$db->query("DELETE FROM blogs WHERE id = '$insid'");

There you have it. As the above code fragments show, it’s ridiculously easy (if the phrase is applicable) to maintain the consistency of the relationship established between the “blogs” and the “comments” tables, even when they’re accessed via a basic MySQL abstraction class.

Also, aside from testing the previous example, I encourage you to develop your own scripts, either by using PHP 5 or the programming language of your choice, so you can see the actual power of using foreign key constraints with MySQL.

Final thoughts

In this fifth episode of the series, you learned how to take advantage of foreign key constraints in MySQL to produce the deletion in cascade of rows in a child table, when the related rows of the corresponding parent are deleted. And best of all, this was carried out with a simple MySQL abstraction class. What more can you ask for?  

Well, actually you can ask for much more, and hopefully you won’t be disappointed; in the last tutorial I’m going to explain how to perform cascading updates and deletions in the same InnoDB table, using the aforementioned MySQL class.

Don’t miss the last article!



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: