Foreign key constraints for PHP to play with MySQL

In this article, we will learn how to use a basic abstract class in PHP 5 to update data in two InnoDB tables using foreign key constraints. The following example shows how to use the foreign key constraint in the script language on the server.

I. Update Mysql Data Using Foreign key constraints

Currently, the most popular open-source relational database management system is not mysql, and MySQL supports multiple storage engines. The default storage engine is MyISAM. For many readers, it may have been used for a long time before developing a database-driven web application.

However, sometimes our project may require additional features, such as foreign key constraints, so we need to use other types of MySQL storage engines. In this case, the InnoDB table will be very suitable for our requirements, although it may be slightly inferior to the MyISAM table in terms of performance. As you know, one of the main advantages of using the InnoDB table foreign key constraint is that it enables us to process and maintain the relationship between multiple tables at the database level, this task does not need to be pushed to certain modules or libraries of applications dealing with these tables.

Of course, in the previous articles, we have already introduced the foreign key constraints of the indodb table, but they are all manually operating the foreign key constraints. In this article, we will explain how to trigger cascade update and delete operations for corresponding sub-tables using the script language when updating and deleting data in the parent table.

Here, the data layer of our blog application consists of two tables. In the previous example, operations on these tables are performed by manually typing SQL commands. Now, we will introduce how to use the PHP programming language to complete these tasks. PHP is selected because it is currently the most common MySQL collocation language. Next we will take PHP 5 as an example to illustrate how to operate two InnoDB tables with foreign key constraints. By reading this article, you will learn more about the features of foreign key constraints.

Now, let's begin to witness the power brought by the combination of PHP 5 and foreign key constraints!

2. Update and delete data in the database in a cascading manner

Let's review what we have learned before. Previously, we introduced how to use foreign key constraints to cascade updates and delete data in the InnoDB table storing blog post comments. It doesn't matter if you haven't read the previous article. Let's review the content below.

The definitions of the two tables used in our example are as follows:

 

Code highlighting produced by actipro codehighlighter (freeware)
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 on update Cascade

) Engine = InnoDB default charset = utf8;
The code above defines two tables. Note the second one because it specifies a constraint for the "blog_id" field. Therefore, when the data in the post table is updated and deleted, the corresponding cascade operation is triggered.

To help you understand this process, we can fill in some data in the table. At this time, we can use the SQL statement insert, as shown below:

 

Code highlighting produced by actipro codehighlighter (freeware)
Insert into blogs (ID, title, content, author) values (null, 'title of the first blog entry ', 'content of the first blog entry', 'Ian ')

Insert into comments (ID, blog_id, comment, author) values (null, 1, 'commenting first blog entry ', 'Tom'), (null, 1, 'commenting first blog entry ', 'Rose ')
Now, the only blog data has two comment data. If you need to update the blog and comment data for any reason, run the following command:

 

Code highlighting produced by actipro codehighlighter (freeware)
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
This seems very simple, but if I say it is easier to delete blog data together with the corresponding comment data, can you believe it? If you do not believe it, see the following SQL statement:

 

Code highlighting produced by actipro codehighlighter (freeware)
Delete from blogs where id = 2
As you can see, this is all the SQL code required to delete the specified blog and its comments. This proves how convenient it is to maintain the integrity of two InnoDB tables through foreign key constraints.

So far, we have briefly reviewed what we have learned. Next, we will continue to explore the advantages of the constraints of these tables. As described in the beginning of this article, we will begin to explain how to generate cascade updates to our example table through the MySQL abstract class built in PHP 5.

Read about wordend:
Foreign key constraints in MySQL
MySQL foreign key constraint cascading Deletion
Difference between MSSQL Server and MySQL
Iii. Use PHP 5 to update databases in a cascade manner

Now we will introduce in detail how to use the popular server-side scripting language PHP 5 to update our example table in cascade mode. Therefore, we need to write code that allows us to access the InnoDB table defined above. In this example, we use the MySQL abstract class of PHP 5 to achieve this purpose. The code below is as follows:

 

Code highlighting produced by actipro codehighlighter (freeware)
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 ());

}

}

 

// Execute the 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 making query '. $ query. 'error message:'. mysqli_error ($ this-> link ));

}

}

}

 

// Return data from the result set

Public Function fetch ()

{

If (false ===( $ ROW = mysqli_fetch_object ($ this-> result )))

{

Mysqli_free_result ($ this-> result );

Return false;

}

Return $ row;

}

 

// Obtain the insert ID

Public Function getinsertid ()

{

Return mysqli_insert_id ($ this-> link );

}

// Number of rows in the result set

Public Function countrows ()

{

If ($ this-> result! = NULL)

{

Return mysqli_num_rows ($ this-> result );

}

}

// Close the database connection

Function _ destruct ()

{

Mysqli_close ($ this-> link );

}

}
As shown above, the MySQL abstract class defined above is very simple. It provides many common methods for executing queries, counting the number of rows in the result set, and obtaining the insert ID. It should be noted that this class uses PHP extension mysqli to deal with MySQL internally, so it seems easy to understand as a whole.

Now, we have defined a PHP Class 5 that can be used to interact with MySQL databases. Now we need to use its API to perform cascade update on the InnoDB table defined above.

Read about wordend:
Foreign key constraints in MySQL
MySQL foreign key constraint cascading Deletion
Difference between MSSQL Server and MySQL
Iv. MySQL abstract class

To demonstrate how to use the MySQL class cascade to update the data table in the previous article, we need to redefine the two tables so that they can only perform these update operations. Here are their definitions. These two tables will serve as the data layer of our sample blog application:

 

Code highlighting produced by actipro codehighlighter (freeware)
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;
As described above, by specifying a foreign key constraint for the blog_id field in the last table, we have linked the two tables, next, let's use the MySQL abstract class defined above to fill them with necessary data. Suppose this class is put into a file named mysqlclass. php separately. The following script inserts a single blog article into the blog table and inserts two comments into the comments table:

 

Code highlighting produced by actipro codehighlighter (freeware)
Require_once 'mysqlclass. php ';

$ Db = new MySQL ('host', 'user', 'Password', 'test ');

// Insert new data into the 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 ', 'Ian ')");

$ Insid = $ db-> getinsertid ();

// Insert a new comment in the comments database table

$ Db-> query ("insert into comments (ID, blog_id, comment, author) values (null, $ insid, 'commenting first blog entry ', 'Tom '), (null, $ insid, 'commenting first blog entry ', 'Rose ')");
Although our MySQL class can access the database in an abstract way, the corresponding SQL query must be manually written. At the same time, it allows us to demonstrate how to use this class to update comments related to the first data table whenever the data in the first table is updated.

The code snippet for performing this cascade update operation is as follows:

 

Code highlighting produced by actipro codehighlighter (freeware)
// Update the data in the blogs table (the related data in the comments table will be automatically updated)

$ 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 ");
Although an abstract class is added to deal with the above InnoDB table, the SQL code required to trigger cascading update remains highly concise. This shows that the integrity of the relationships between tables is maintained at the database level, rather than maintained by the PHP 5 application.

V. Summary

So far, we have explained in detail how to update data in two InnoDB tables by using the foreign key constraint in the built-in abstract class of PHP 5. We hope this article will give you some inspiration when using foreign key constraints using server scripts. In the subsequent articles, we will continue to explore content related to foreign key constraints.