Home arrow PHP arrow Page 2 - Triggering Cascading Updates and Deletions with Foreign Key Constraints

Review: cascading updates with MySQL InnoDB tables - PHP

Welcome to the final installment of a series on working with foreign key constraints in MySQL. With a respectable number of code samples, this series walks you through the basics of using foreign key constraints in MySQL tables and teaches you how to use them specifically for maintaining the integrity of relational databases.

TABLE OF CONTENTS:
  1. Triggering Cascading Updates and Deletions with Foreign Key Constraints
  2. Review: cascading updates with MySQL InnoDB tables
  3. Redefining the previous InnoDB tables
  4. Updating and deleting records in cascade within the same MySQL table
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 23, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> 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: