Create the Tables
The first thing we need to do is create the database tables to store this information. Since we are making a very simple site we will only make two tables, one for code and one for content. Execute this code to create the database tables:
Add Some Data
CREATE TABLE template (template_id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY, Head_Open VARCHAR(60), Head_Close VARCHAR(60), Page_End VARCHAR(60), Date VARCHAR(60)); CREATE TABLE content (content_id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60), body MEDIUMBLOB);
Now we need to add data to the tables. If you were adding information on a regular basis, it might be easiest to add it from a web browser. In our case we will just add it in manually to demonstrate how to retrieve it from the database and use it. Remember if you are adding it from the web, you need to consider addslashes and magic quotes.
We can make additional pages simply by adding additional rows to the table called content. Likewise, we could make additional templates by adding a new row to the table called template.
INSERT INTO content (title, body) VALUES ( "MyPage", "This is my sample page, where I will use PHP and MySQL to template my website. <p> Here is a list of things I like <ul><li>PHP Code</li><li>MySQL</li><li><a href=http://php.about.com>PHP @ About.com</a></li></ul><p> That is the end of my content.") ; INSERT INTO template (Head_Open, Head_Close, Page_End, Date) VALUES ( "<html><head><title>", "</title><body>", "</body></html>", "$b = time (); print date('m/d/y',$b) . '<br>';");
Build the Page
Now all we need to do is call this information from our PHP file. We can do that like this:
This method is a very effective way of creating a template based site, or executing different PHP on the fly simply by calling different rows from MySQL.
<?php // Connects to your Database mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error()); mysql_select_db("Database_Name") or die(mysql_error());
//This retrieves the template and puts into an array. No WHERE clause is used, because we only have one template in our database. $coding = mysql_query("SELECT * FROM template") or die(mysql_error()); $template = mysql_fetch_array( $coding );
//This retrieves the content and puts into an array. Notice we are calling ID 1, this would change if we wanted to call a page stored on a different row $text = mysql_query("SELECT * FROM content WHERE content_id =1") or die(mysql_error()); $content = mysql_fetch_array( $text );
//Actually puts the code and content on the page Print $template['Head_Open']; Print $content['title']; Print $template['Head_Close'];
//When pulling PHP code we need to use EVAL Eval ($template['Date']);
Print $content['body']; Print $template['Page_End']; ?>