Home arrow PHP arrow Page 4 - Performing Record Updates with Foreign Key Constraints in MySQL

The MySQL abstraction class in action - PHP

In this fourth installment of a six-part series, I explain how to use foreign key constraints to update rows of two InnoDB tables via a basic abstraction class built into PHP 5. This example will help you start utilizing foreign key constraints with a particular server-side scripting language.

TABLE OF CONTENTS:
  1. Performing Record Updates with Foreign Key Constraints in MySQL
  2. Review: updating and deleting database rows in cascade
  3. Updating database records in cascade with PHP 5
  4. The MySQL abstraction class
By: Alejandro Gervasio
Rating: starstarstarstarstar / 3
December 17, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

At this point, everything has been properly set up to demonstrate how to use the previous MySQL class to perform cascaded updates of records on the tables shown in the first section of this article, right? Well, not quite; it’s necessary to redefine the tables so they can perform only these updating operations. 

So, with that requirement in mind, here are the definitions of these tables, which  comprise the data layer of a simple blog program:

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;

Now that the above tables have being related by specifying a foreign key constraint for the “blog_id” field of the last one, it’s time to use the earlier MySQL abstraction class to populate them with some sample data. Assuming that the class has been coded separately on a file called “mysqlclass.php,” the following script will insert a single blog post into the “blog” table and a couple of comments into the “comments” table:

require_once 'mysqlclass.php';

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

// insert new entry in '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 in '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')");

Certainly, I have to admit that the MySQL class does a decent job of abstracting database accesses, even though the corresponding SQL queries must still be coded manually. On the other hand this is propitious, since it permits me to show how to use the class to update the comments related to the first blog entry, when this entry is updated.

The code snippet that performs this cascading operation is the following:

// 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 = 1");

Even though I added an additional abstraction class to interact with the two previous InnoDB tables, the SQL code required to trigger the cascading updates remains extremely simple. This demonstrates that the integrity of the relationship between those tables is always maintained at the database level, and not by the PHP 5 application.

Finally, with this example I’m finishing the fourth tutorial of this series. As always, feel free to edit all of the code samples included in this article, so you can sharpen your skills in using foreign key constraints in MySQL.

Final thoughts

Over this fourth installment of the series, I explained how to use foreign key constraints to update rows of two InnoDB tables via a basic abstraction class built in PHP 5. Hopefully, this example will provide you with the right information to start utilizing foreign key constraints with a particular server-side scripting language.

Now, in the upcoming tutorial I’m going to discuss how to use those constraints to delete the rows of the sample MySQL tables that you saw before.

Don’t miss the next part!



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