A Framework for Persisting Data Relationships
(Page 1 of 4 )
This tutorial will show you how to create relationships in your databases. PHP classes are provided that will perform the job.
By : Jake Huffman
I have been working on the basic design for a php mysql web application lately and in the process of trying to "do it right" I have been taking a look at lots of code and lots of tutorials. What I am going to show in this tutorial is the way that I managed to solve one of the problems that I was having. Specifically, I had a good database model with several tables with relationships, including standard link tables with nothing more than the primary key from different tables in them, but I was trying to populate all of the tables with the same php script. The example code that is provided is a simple example where a form is used to enter a book title and a book author. When processed, the author information is entered into one database table, the book information is entered into another database table, and the primary keys from the book and the author are entered into a link table. I couldn't find any tutorials that dealt with this situation, so this is what I came up with. . Having said that, keep in mind that there are a few tweaks that could be made to improve the efficiency of how this works, and I haven't spent the time to really work through it well. So if you give me your improvements, I would greatly appreciate them.
This method comes, to a large degree, out of the basic concept laid down in the January 2003 issue of php|a in the article titled "Implementing Database Persistence Layers in PHP" by Shawn Bedard. The basic concept involves a little object-oriented programming and a direct correlation between your database and your objects. The first step for me was to establish a basic object that can be extended into anything that you want to persist in the database. In the code it is the class named Persistable. For the most part, it can't really be used by itself, but by doing a good job on the methods (functions) in the class; it can be easily extended to create anything that you want to store in the database. The real heart of what I'm dealing with is in the method PersistNew(). Basically, all it does it take the current values of the object, and create an SQL INSERT statement using those values. When the INSERT is complete, it retrieves the id from the database and sets the id for the object to the database id.
The real advantage of this method is that using one form to populate several tables in the database with linking tables only takes a few lines of code (assuming that the Persistable class was extended to create everything that you want to store in the database). As an example: Let's say that I want to use a single form to store information about a book, the author and link the book to the author. Don't worry about why, just say I need a many to many relationship between books and authors. But since I want someone to like the software, I don't want them to create each book, then the author, then link them all, I want it all done at once. So how does it all look?
<?php
$newBook = new Book;
$newBook->title = $_POST['title'];
$newBook->PersistNew();
$newAuthor = new Author;
$newAuthor->firstname = $_POST['firstname'];
$newAuthor->lastname = $_POST['lastname'];
$newAuthor->PersistNew();
$newBookAuthorLink = new BookAuthorLink;
$newBookAuthorLink->author = $newAuthor->id;
$newBookAuthorLink->book = $newBook->id;
$newBookAuthorLink->PersistNew();
?> |
And that is it. Now of course you would probably like to know what those classes look like, right? Keep in mind that the really important one is the Persistable class so lets take a look at the code.
Next: The Classes >>
More Database Articles Articles
More By Codewalkers
