Creating A Scaffold-like Class in PHP or An Automatic CMS For a Table

on Sat Jul 05 08:36:35 GMT 2008 in PHP and viewed 20627 times

If you’ve ever seen the Creating a Weblog In 15 Minutes movie, you were undoubtedly startled at how useful and quick the scaffold command could be. But if you’re still using PHP, and you love that command, then you could have a problem. Luckily, through the use of Object Oriented Programming, an equivalent solution is available, creating a full create, read, update, and delete CMS for a single table through one command.

Introduction

The end result will look something like this: The end result of the scaffold class and some extra style

And as always you’ll need your database schema. So here’s the single table you’ll be using:


CREATE TABLE `scaffold` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `body` text,
  PRIMARY KEY  (`id`)
)

Starting The Class

This class, scaffold, needs only one class variable, which is the table name. This will be defined as soon as you instantiate the class when you want to use it.


<?php

class Scaffold {

var $table = "";

}

?>

List (and index)
New Row (Form)
Create the new row
Edit Row (Form)
Update the row
Delete Row

Only 6 major functions. Should not be too hard. But before we go defining all these functions, let’s think how they’re going to be called. You don’t want any extra work in the file you are going to be using to actually scaffold the table, all the work should be done here, so the easiest way is to use a switch method to choose the action through the url.

And besides just that, all this (the switching, the function calling, the database connecting) should all be done in the constructor function. If you don’t remember what a constructor function is, it’s a function that has the same name as the class and is called as soon as the class is instantiated. So knowing this, you can start on the class.

scaffold.php


class Scaffold {

var $table = "";

function Scaffold($table){
    $this->table = $table;

    $connection = mysql_connect("localhost",
             "username",
             "password");
    mysql_select_db("database", $connection);

    switch($_GET["action"]){
        default:
            $this->listtable();
        break;

        case "list":
            $this->listtable();
        break;

        case "new":
            $this->newrow();
        break;

        case "postnew":
            $this->postnew();
        break;

        case "edit":
            $this->editrow();
        break;

        case "postedit":
            $this->postedit();
        break;

        case "delete":
            $this->deleterow();
        break;
    }
}

function listtable(){

}

function newrow(){

}

function postnew(){

}

function editrow(){

}

function postedit(){

}

function deleterow(){

}

}

Allright, now you see what you’re up against.

The function Scaffold takes one argument, which is the table you’re going to be scaffolding. It then sets the class variable to that using $this->table. Then it’s a very simple, very generic database connection. The rest of the function, like I said, is just one big switch function. The cases within the switch don’t match the functions they call because the names new and list are taken by php. So the links will be prettier, but the functions should be obvious to you to know what each does.

Listing the Rows

The first function you should deal with is listtable(). The goal of the function is to output the data from the table in a table. Shouldn’t be too hard. All you need is a select, then use a while loop with a mysql_fetch_array().


$select = mysql_query("SELECT * FROM ".$this->table);

echo "<table>";
while($array = mysql_fetch_array($select)){
    echo "<tr>";
    foreach($array as $column => $value){
        if(!is_int($column) && $column != "id"){
            echo "<td>$value</td>";
        }
    }
    ?>
        <td><a href="?action=edit&amp;id=<?=$array[0]?>">Edit</a></td> 
        <td><a href="?action=delete&amp;id=<?=$array[0]?>">Delete</a></td> 
    <? 
    echo "</tr>";
}

    echo "</table>";

Not very difficult. The query selects all rows from the table defined by the constructor method in $this->table, echoes the beginning of a table, then loops through each row in the table outputting it as a cell if the column name is not id.

The foreach loop is to easily look through each field in the array and check if it’s the id field. $column has the name of the field, and $value, the value. I decided not to output the id because you really don’t need to know the id for each row.

The Edit and Delete links are easy. $array has the value of the current row you’re listing, and id is the first field. So since arrays start at 0… id would be the 0th entry in $array.

One thing I really loved about the scaffolding from Ruby on Rails is that it printed a row at the very top listing each of the rows’ fields so you knew if it was a title, a body, a date, whatever. So why don’t you do this as well?

Insert this code above the while loop.


$i = 0;
echo "<tr>";
while($i < mysql_num_fields($select)){
    $column = mysql_fetch_field($select, $i);
    if($column->name != "id"){
        echo "<td><b>".$column->name."</b></td>";
    }
    $i++;
}
echo "</tr>";

The two new functions here are mysql_num_fields and mysql_fetch_field. Mysql_num_fields() does basically what it sounds like: it takes a query and numbers the fields in it. So we define $i as 0, and loop through, increasing $i by one each time until it equals the number of fields in the table. The next thing to do is to actually display the row.

First you need to set $column to mysql_fetch_field. Mysql_fetch_field takes two arguments: the query, and the field offset, and it returns an object of the field. Remember, the field offset will behave like an array, so since id is the first field, it’ll have an offset of 0.

Then you just have to check if the name of the column is not “id” (because you don’t really need to know the id of each row) and echo the name. Mysql_fetch_field returns an object, so access variables just like you would an object, with $column->name.

Allright, that’s a pretty cool listing function:


    function listtable(){
        $select = mysql_query("SELECT * FROM ".$this->table);
        $i = 0;
        echo "<table>";
        echo "<tr>";
        while($i < mysql_num_fields($select)){
            $column = mysql_fetch_field($select, $i);
            if($column->name != "id"){
                echo "<td><b>".$column->name."</b></td>";
            }
            $i++;
        }
        echo "</tr>";

        while($array = mysql_fetch_array($select)){
            echo "<tr>";
            foreach($array as $column => $value){
                if(!is_int($column) && $column != "id"){
                    echo "<td>$value</td>";
                }
            }
            ?>
                <td><a href="?action=edit&amp;id=<?=$array[0]?>">Edit</a></td> 
                <td><a href="?action=delete&amp;id=<?=$array[0]?>">Delete</a></td> 
            <? 
            echo "</tr>";
        }

        echo "</table>";
        echo "<a href='?action=new'>New Row</a>";
    }

One function down, five to go.

Creating a New Row

Creating a new row is probably the next logical step. We don’t have any rows to list yet (but at least you have an easy link to the new row forms), and you don’t have anything to edit or delete, so might want to create a new row. This function is probably the third to last most confusing (that was a confusing statement in itself). Anyways, better to just jump right into it.


    function newrow(){
        $select = mysql_query("SELECT * FROM ".$this->table);
        $i = 0;
        echo "<form action='?action=postnew' method='POST'>";
        while($i < mysql_num_fields($select)){
            $column = mysql_fetch_field($select);
            if($column->name != "id"){
                if($column->blob == 1){
                    echo "<b>$column->name:</b> <textarea name='$column->name'></textarea><br />";
                }
                else {
                    echo "<b>$column->name:</b> <input type='text' name='$column->name' /><br />";
                }
            }
            $i++;
        }
        echo "<input type='submit' value='Add Row' />";
        echo "</form>";
    }

Okay, really awesome code, huh? Well, it has to be. It has to look at every field to create a custom field for it. At the moment, just textareas or inputs.

So the idea is to first select all fields from the table so that you can create a field for each one. Set $i to 0, echo the beginning of the form, and off you go. And set up the while loop. Of course. Anyways, fetch the object into $column. The offset can be omitted to instead fetch the next field not fetched in a loop. So we say forget it to the offset argument and keep going. Set up one big condition to containt it all. If the column name is not id, don’t output anything that time. Very important. Then it checks if the column is a blob, which is a 1 or 0 value. A blob would be like a text field, and would usually require a <textarea> element. Otherwise, just make a little <input> with the text type. Then it’s all up to a submit button and </form> to end it all.

Whew! Not too bad. It’s a good hearty function. Now it’s time to insert the row.

Inserting the Row

Ah, function postnew(). This is an interesting function. What you’re going to have to do, is cycle through each field concatenating the value of the field through $_POST[fieldname] onto a string passed through the whole function. Fun, huh?


    function postnew(){
        $select = mysql_query("SELECT * FROM ".$this->table);
        $insert = "INSERT INTO ".$this->table." VALUES('', ";
        $i = mysql_num_fields($select);
        $i--;
        foreach($_POST as $key => $value){
            $i--;
            if($i > 0){
                $insert .= "'".$value."', ";
            }
            else {
                $insert .= "'".$value."')";
            }
        }
        mysql_query($insert) or die(mysql_error());
        echo "Added row.<br />";
        echo "<a href='?action=list'>Back To Listing</a>";
    }

Actually not that bad. The omnipresent select all fields query is there. Then a string is started in $insert. Then $i is defined as the number of fields. Then I subtracted one from it. Inside the foreach loop, where $_POST is extracted into $key => $value, $i is subtracted again. Mainly because I tried a few things out to get it working. Feel free to optimize it a bit. Then if $i is greater than $0, $insert has the value concatenated onto the string. If $i is not greater than 0, then $insert has the same thing, but instead of a comma at the end, the query will be ended with the ). Outside the loop, the query is executed, you’re delivered a message, and you can go back to the listing to see your row.

Editing a Row (Form)

Allright. That’s done. You now can insert rows automatically. Awesome. But what if you screw up in entering some data? Well, that’s what this part is for.


    function editrow(){
        $fields = mysql_query("SELECT * FROM ".$this->table) or die(mysql_error());
        $select = mysql_query("SELECT * FROM ".$this->table." WHERE id = ".$_GET["id"]) or die(mysql_error());
        $row = mysql_fetch_row($select);
        $i = 0;
        echo "<form action='?action=postedit&amp;id=".$_GET["id"]."' method='POST'>";
        while($i < mysql_num_fields($fields)){
            $field = mysql_fetch_field($fields);
            if($field->name != "id"){
                if($field->blob == 1){
                    echo "<b>$field->name:</b> <textarea name='$field->name'>".$row[$i]."</textarea><br />";
                }
                else {
                    echo "<b>$field->name:</b> <input type='text' name='$field->name' value='".$row[$i]."' /><br />";
                }
            }
            $i++;
        }
        echo "<input type='submit' value='Edit Row' />";
        echo "</form>";
    }

The process is similar to the newrow() function, except that the values need to be inserted into the fields. This time there’s two selects: one for the mysql_fetch_field (to list the fields) and one for the mysql_fetch_row (to fill in the values of the row). Then set $i = 0 and start the form and the while loop again. Next select the current field and it’s basically just like the newrow function from here on except that in between the <textarea> tags needs the $row[$i] and the same for the value attribute in the inputs. $row is an array from the mysql_fetch_row, and $i is the current field. All that’s left is to increase $i and to end the form.

Updating The Row

The form for editing the row is great and everything, a real piece of work, but you still need to be able to update the row, the backend. So do that with this:


    function postedit(){

        $select = mysql_query("SELECT * FROM ".$this->table." WHERE id = ".$_GET['id']);
        $num = mysql_num_fields($select);
        $update = "UPDATE ".$this->table." SET ";
        $i = 1;
        while($i <= $num){
            $column = mysql_fetch_field($select);
            if($column->name != "id"){
                if($i != $num){
                    $update .= $column->name." = '".$_POST["$column->name"]."', ";
                }
                else {
                    $update .= $column->name." = '".$_POST["$column->name"]."' WHERE id = ".$_GET['id'];
                }
            }
            $i++;
        }
        mysql_query($update) or die(mysql_error());
        echo "Edited row.<br />";
        echo "<a href='?action=list'>Back To Listing</a>";

    }

Let’s go now, almost done. First it needs a general select to select the row from the table. $num will hold the number of fields this time, and $i is set to 1. $update will be the general string to hold the entire update query like $insert was for postnew(). Set up the while loop, and set column to the current field. Then, again, if this is not the id field, go into the main area. First check if $i is not equal to the number of fields. If it isn’t, then add onto $update making the query look like “field = ‘value’, ” with the trailing comma for the next field. If $i does equal the number of fields, then instead of a trailing comma, finish with the WHERE id = [the id] to update the row. Just execture the query and give a congratulatory message and link, and you’re done for that.

Deleting the Row

Finally, the last function. It’s about time, huh? Luckily, this is the easiest.


    function deleterow(){

        mysql_query("DELETE FROM ".$this->table." WHERE id = ".$_GET["id"]) or die(mysql_error());
        echo "Annihilated row.<br />";
        echo "<a href='?action=list'>Back To Listing</a>";

    }

Very very easy. It’s just a simple DELETE FROM query, then it tells you that the row has been annihilated (you can change it to obliterated, destroyed, godzill-ized, whatever) and a link back to the listings. Isn’t that great?

Scaffold Class Conclusion

Finally, your scaffold class is done. Good job, that took a lot of thinking out and planning to accomplish, didn’t it? Sit back and marvel at it.

scaffold.php


<?php

class Scaffold {

    var $table = "";

    function Scaffold($table){
        $this->table = $table;
        $connection = mysql_connect("localhost",
                            "username",
                            "password");
        mysql_select_db("database", $connection);

        switch($_GET["action"]){
            default:
                $this->listtable();
            break;

            case "list":
                $this->listtable();
            break;

            case "new":
                $this->newrow();
            break;

            case "postnew":
                $this->postnew();
            break;

            case "edit":
                $this->editrow();
            break;

            case "postedit":
                $this->postedit();
            break;

            case "delete":
                $this->deleterow();
            break;
        }
    }

    function listtable(){
        $select = mysql_query("SELECT * FROM ".$this->table);
        $i = 0;
        echo "<table>";
        echo "<tr>";
        while($i < mysql_num_fields($select)){
            $column = mysql_fetch_field($select, $i);
            if($column->name != "id"){
                echo "<td><b>".$column->name."</b></td>";
            }
            $i++;
        }
        echo "</tr>";

        while($array = mysql_fetch_array($select)){
            echo "<tr>";
            foreach($array as $column => $value){
                if(!is_int($column) && $column != "id"){
                    echo "<td>$value</td>";
                }
            }
            ?>
                <td><a href="?action=edit&amp;id=<?=$array[0]?>">Edit</a></td> 
                <td><a href="?action=delete&amp;id=<?=$array[0]?>">Delete</a></td> 
            <? 
            echo "</tr>";
        }

        echo "</table>";
        echo "<a href='?action=new'>New Row</a>";
    }

    function newrow(){
        $select = mysql_query("SELECT * FROM ".$this->table);
        $i = 0;
        echo "<form action='?action=postnew' method='POST'>";
        while($i < mysql_num_fields($select)){
            $column = mysql_fetch_field($select);
            if($column->name != "id"){
                if($column->blob == 1){
                    echo "<b>$column->name:</b> <textarea name='$column->name'></textarea><br />";
                }
                else {
                    echo "<b>$column->name:</b> <input type='text' name='$column->name' /><br />";
                }
            }
            $i++;
        }
        echo "<input type='submit' value='Add Row' />";
        echo "</form>";
    }

    function postnew(){
        $select = mysql_query("SELECT * FROM ".$this->table);
        $insert = "INSERT INTO ".$this->table." VALUES('', ";
        $i = mysql_num_fields($select);
        $i--;
        foreach($_POST as $key => $value){
            $i--;
            if($i > 0){
                $insert .= "'".$value."', ";
            }
            else {
                $insert .= "'".$value."')";
            }
        }
        mysql_query($insert) or die(mysql_error());
        echo "Added row.<br />";
        echo "<a href='?action=list'>Back To Listing</a>";
    }

    function editrow(){
        $fields = mysql_query("SELECT * FROM ".$this->table) or die(mysql_error());
        $select = mysql_query("SELECT * FROM ".$this->table." WHERE id = ".$_GET["id"]) or die(mysql_error());
        $row = mysql_fetch_row($select);
        $i = 0;
        echo "<form action='?action=postedit&amp;id=".$_GET["id"]."' method='POST'>";
        while($i < mysql_num_fields($fields)){
            $field = mysql_fetch_field($fields);
            if($field->name != "id"){
                if($field->blob == 1){
                    echo "<b>$field->name:</b> <textarea name='$field->name'>".$row[$i]."</textarea><br />";
                }
                else {
                    echo "<b>$field->name:</b> <input type='text' name='$field->name' value='".$row[$i]."' /><br />";
                }
            }
            $i++;
        }
        echo "<input type='submit' value='Edit Row' />";
        echo "</form>";
    }

    function postedit(){

        $select = mysql_query("SELECT * FROM ".$this->table." WHERE id = ".$_GET['id']);
        $num = mysql_num_fields($select);
        $update = "UPDATE ".$this->table." SET ";
        $i = 1;
        while($i <= $num){
            $column = mysql_fetch_field($select);
            if($column->name != "id"){
                if($i != $num){
                    $update .= $column->name." = '".$_POST["$column->name"]."', ";
                }
                else {
                    $update .= $column->name." = '".$_POST["$column->name"]."' WHERE id = ".$_GET['id'];
                }
            }
            $i++;
        }
        mysql_query($update) or die(mysql_error());
        echo "Edited row.<br />";
        echo "<a href='?action=list'>Back To Listing</a>";

    }

    function deleterow(){

        mysql_query("DELETE FROM ".$this->table." WHERE id = ".$_GET["id"]) or die(mysql_error());
        echo "Annihilated row.<br />";
        echo "<a href='?action=list'>Back To Listing</a>";

    }

}

?>

Acting on the Scaffolding Code

You’re not done just yet. A class is great and everything, but you need a page to execute it!

This code, by design, is made to be very easy. Just an include and a class call.


<?php
include("scaffold.php");
new Scaffold("scaffold");
?>

And that’s it. That’s it. There’s nothing else to do. The constructor class luckily takes care of anything. The scaffold is meant to be very lightweight to use in your code. The argument that Scaffold takes is the name of the table. And that’s it. It’ll create your easy CRUD system for you!

Conclusion

Well, there’s your lightweight solution to adding and editing rows. This is not meant to be a full replacement content management system, just a simple solution to see how some content would look in your application, an easy interface to a table.

Ideas for Expansion

As always:

Add a date field for the forms
Create a page style within the constructor function to make it a bit more pleasing to the eye
There’s probably a bit of optimization that could be done
Using a button instead of a link for deleting rows (for protection against accelerator programs)

Now go forth and code some more cool web apps.

The Shadow Fox Network - Ruby on Rails, PHP, XHTML, CSS, Javascript, and XML Tutorials