Review: cascading updates with MySQL InnoDB tables - PHP

It’s quite possible that you haven’t yet had the chance to read the previous article of the series. It demonstrated how to use foreign key constraints within a couple of InnoDB tables to delete in cascade some rows within one of them. So, taking into account this possibility, below I included the entire source code for this example, starting with the definitions of the tables, which look like this:

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 DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

From the definition of these tables, it’s easy to see that both of them compose the database layer of a basic blog program, where the first one stores blog entries and the last one houses the comments that could have been made on each blog post.

Naturally, the most relevant thing to notice here is the SQL statement that specifies the cascading deletions of rows in the “comments” table, when the related blog posts in the parent table are removed.

So far, so good. Having shown the respective definitions of the above two InnoDB tables, here’s the complete signature of the MySQL abstraction class built in PHP 5, which is responsible for interacting with them:

<?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);

}

}

?>

Certainly, I’m not going to waste your time (and mine) explaining how the previous “MySQL” class functions, since its source code is extremely easy to follow. Instead, I will build a short script that uses the class to insert, first, a blog entry into the first “blogs” table, and second, a couple of comments into the second table.

Finally, and only for example purposes, the blog entry will be deleted, which also will automatically remove the comments associated with it. Having explained that, here’s the script that performs all those tasks:

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'; rows in 'comments' table are deleted automatically

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

See how easy it is to maintain the integrity of the relationship existing between the two sample MySQL tables? If you still don’t see the nitty-gritty of this process, then the previous code fragment should dissipate all of your doubts quickly. Removing a specified blog post along with its related comments is as simple as running one single DELETE command in the first table and nothing else. MySQL automatically takes care of removing in cascade the corresponding comments.

At this stage, you surely understood how the earlier example works, so it’s time to learn how to use the MySQL abstraction class for triggering both cascading updates and deletions in the “comments” table.

This process was shown in a previous tutorial of the series using plain SQL, but in this particular case it’d be pretty useful to recreate it directly with PHP 5. Therefore, in the section to come I’m going to modify the definition of the “comments” MySQL table to enable within it the support for cascading updates and deletions.

Now, click on the link below and read the following segment.