Review: updating database rows in cascade through a MySQL abstraction class - PHP

As you probably remember, the previous tutorial explained how to use foreign key constraints to perform cascading updates on the rows of the tables that comprised a sample blog application. Below I reintroduced the definitions for those tables, along with the MySQL abstraction class built with PHP 5, responsible for interacting with them.

So first we'll take a look at the way the sample tables were created. Here they are:

DROP TABLE IF EXISTS `test`.`blogs`;

CREATE TABLE `test`.`blogs` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`title` TEXT,

`content` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`comments`;

CREATE TABLE `test`.`comments` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`blog_id` INT(10) UNSIGNED DEFAULT NULL,

`comment` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `blog_ind` (`blog_id`),

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Aside from defining a few basic fields in both MySQL tables for storing some blog posts and the comments that could be made on them, the second table defines a foreign key constraint that will automatically update those comments if the ID of its parent blog entry is also updated.

Nonetheless, as I said in the introduction, the entire updating process will be performed via a simple MySQL abstraction class created in PHP 5, whose definition looked like this:

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, things are becoming a bit more interesting, right? Suppose for a moment that an instance of the above “MySQL” class is used to insert a blog entry into the corresponding “blogs” table, along with a couple of comments into the other table. This would be done through the following code segment:

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')");

So far, nothing unexpected is going on here. But, bear with me for a moment and let me show you what happens when the first blog post is updated with the following data:

// update row in 'blogs' (rows in 'comments' table are updated automatically)

$db->query("UPDATE blogs SET id = 2, title = 'Title of the first blog entry', content = 'Content of the first blog entry', author = 'John Doe' WHERE id = '$insid'");

Well, here’s where the magic of using foreign key constraints takes place. When the ID corresponding to the first blog entry is updated, its related comments will be updated as well. If you're not convinced of this, try out the above example on your own web server and see how well it works.

Now that you've grasped the underlying logic of the previous code sample, it’s time to explore other advantages to using foreign key constraints with InnoDB tables in MySQL. So, as I mentioned at the beginning of this article, foreign key constraints can be used to produce a similar cascading effect in a child table, when associated rows of a parent table are deleted.

As you may recall, this situation was represented in a previous article by using plain SQL, but it would be useful to recreate it by utilizing the MySQL abstraction class coded before. So, in the next section I’m going to redefine the two InnoDB tables that make up the data layer of this blog program, so they can seamlessly support the deletion of rows in cascade.

Now, click on the link that appears below and keep reading, please.