Background
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.

Source: http://codewalkers.com/tutorials/37/1.html

The Classes
<?php
// test_classes.php

// A database persitable object, pretty useless unless you extend it.  
class Persistable {

      // Unique database identifier.
      var $id;
      // Table name that is used in the database
      var $table;
      // Map of column labels in the database to object variables
      var $columns = array();

      // Persist a new object in the database.
      function PersistNew() {
        mysql_connect('localhost', 'username', 'password')
                  or die("Could not connect: " . mysql_error());
        mysql_select_db('databasename')
              or die ("Unable to select database");
        
        $sql = "INSERT INTO `".$this->table."` ";
        $labels = "(`id` ";
        $values = ") VALUES ('' ";

        foreach ($this->columns as $column) {
          $labels .= ", `".$column[0]."` ";
          $temp = '$this->'.$column[1];
          $values .= ", '"."$temp"."' ";    
          unset($temp);
        }

        $sql .= $labels.$values.")";
        eval ("\$sql = \"$sql\";");
        $result = mysql_query($sql);
        $this->id = mysql_insert_id();
      }
}// End Class Persistable

class Book extends Persistable {

      var $title;//String of book title.

      function Book() { //Constructor for Book.
        $this->table = 'test_books';
        $this->columns = array( array('title','title')
                              );
      }
} // End Class Book

class Author extends Persistable {
  
      var $fname; // String of author first name.  
      var $lname; // String of author last name.
    
      function Author() { //Constructor for Author.
        $this->table = 'test_authors';
        $this->columns = array( array('firstname','fname'),
                                array('lastname','lname')
                              );
      }
} // End Class Author

class BookAuthorLink extends Persistable {
  
      var $book; // integer book unique id.  
      var $author; //  integer author unique id.
    
      function BookAuthorLink() { //Constructor for Author.
        $this->table = 'test_bookauthorlink';
        $this->columns = array( array('book','book'),
                                array('author','author')
                              );
      }
} // End Class BookAuthorLink
?>  


If you are not familiar with object oriented programming, a real advantage of the technique really shines through in this example. By working with a base class (in this case the class Persistable) I can create functions (called methods if they are contained in classes) that can be used for any object that I extend from the base class. In this case I create three classes that extend the Persistable class. By extending the Persistable class, all of the variables and functions (methods) that are defined in Persistable are available to each of the three extended classes. As a result, the PersistNew() function (method) that is used in the Persistable class is available for all of the other three classes, therefore, I didn't have to recode it for each class extended from it. All I had to do for each of the three extended classes was: (1) set the table variable for each class to the table in the database, (2) set the columns array to map the database column labels to the class variable names, and (3) add whatever variables I wanted for each new object.

Taking a look back at the code, the part that I think is the most difficult to dissect is the PersistNew() function, so lets look at that in some detail. I start out by creating the first part of the SQL statement :

"INSERT into $this->table (id, "  


Then I loop through each array in the array columns using foreach. The first column (column[0]) is the database labels, so I build up the SQL statement with the labels that follow the id. During the same foreach, I build the values from the second column (actually I am building the $this->"") portion of the VALUES part of the SQL. When I'm done, I get something that looks like:

INSERT into example_books (id, title) VALUES ('', '$this->title').  


Then I evaluate the object variables in the SQL using the eval to get:

INSERT into example_books (id, title) VALUES ('' , 'PHP is Great')  


Then I execute the mysql query. Now the part that I think is pretty neat is the next line of code. Basically all it does is set the id of the current object to mysql_insert_id(); Which is the latest value that mysql has generated using an auto-increment in the current connection. A common misconception is that the ids can get mixed up when two people hit your page at the same time. However, according to the MySQL website: The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client..


Source: http://codewalkers.com/tutorials/37/2.html

Trying it Out
Requirements
In order for this technique to work, you must work with a mysql database (most other databases do not have the autoincrementing primary key). The tables that you are using must have an autoincrement primary key with the column name "id", unless you change the Persistable class around a little. And you need a webserver with php support, and a mysql database.

Implementing
First create the database stuff:

CREATE TABLE test_books (
  id int(4) unsigned NOT NULL auto_increment,
  title varchar(15) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM COMMENT='book title list';

CREATE TABLE test_authors (
  id int(4) unsigned NOT NULL auto_increment,
  firstname varchar(15) NOT NULL default '',
  lastname varchar(15) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM COMMENT='author list';

CREATE TABLE test_bookauthorlink (
  id int(4) unsigned NOT NULL auto_increment,
  book int(4) NOT NULL default '',
  author int(4) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM COMMENT='book author link table';  


Then edit the database connection stuff in db_classes.php in the PersistNew() function (username, password, databasename). Located in the test_classes.php file.

Load the test_classes.php (from above) and test.php (below) files.

<?php
//test.php
if (isset($_POST['submit'])) {
echo "submitting <br>";
include_once("test_classes.php");

// new Book:;
$newBook = new Book;
$newBook->title = ($_POST['title']);
$newBook->PersistNew();

// new Author
$newAuthor = new Author;
$newAuthor ->fname = ($_POST['firstname']);
$newAuthor ->lname = ($_POST['lastname']);
$newAuthor->PersistNew();

// new Link
$newBookAuthorLink = new BookAuthorLink;
$newBookAuthorLink ->author = $newAuthor->id;
$newBookAuthorLink ->book = $newBook->id;
$newBookAuthorLink->PersistNew();

echo "done, check out the database";
} else {
?>
<html>
<form method="POST" action="test.php">
<table>
  <tr>
    <td>Book Title:</td>
    <td><input name="title" /></td>
  </tr>
  <tr>
    <td>Author First Name:</td>
    <td><input name="firstname" /></td>
  </tr>
  <tr>
    <td>Author Last Name:</td>
    <td><input name="lastname" /></td>
  </tr>
  <tr>
    <td><input type="submit" name="submit" value="submit" /></td>
    <td><input type="reset" /></td>
  </tr>
</table>
</form>
</html>
<?php
}
?>  


Run the test.php file, enter some data in the form, and check out the database, see if it worked.


Source: http://codewalkers.com/tutorials/37/3.html