The MySQL abstraction class in action - PHP

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!