Published on: 2006-11-27 - Views: 10002

If you're browsing TutorialStream.com you'll probably have your own website (or you'll have it in few months, I bet), you'll have thought at least once to make your site dynamic because "it's cool to have it dynamic", because you want to code PHP and so on, or maybe you're facing the need to move to a dynamic website because updating your old static pages takes you way too much time.
In this tutorial we'll use PHP and MySQL, the most widely used scripting language and database type.
The majority of today's hosting plans have them, if not you might seriously consider to switch to another hosting company / hosting plan.
The theory of running a dynamic website is simple to understand, as well as the advantages.
Imagine that you can have all your pages updated at once, using some kind of web interface. No more need to download a page, edit it and reupload it to the server.
This tutorial introduces you to the concept of content generated on the fly, you'll notice that creating fully dinamic websites driven by user inputs and contribution is not so utopic (with some hard hard work upfront). See digg.com, hotornot.com, reddit.com etc...these websites are all based on databases, even if there will be big differences in the technologies used and implementation choiches for sure.
Pages are created only when someone visits them (HTML files don't really exist on the server): our PHP script queries the database for the content, then inserts each retrieved element into a proper HTML location in order to display it.
It's by the PHP script that you create part of the page, or the entire HTML page.
A PHP file can be a standard HTML file renamed to PHP, the difference is that if the extension is .php you are allowed to run PHP scripts between <?php and ?>.
A database is simply an "organized shelf" where you can store everything.
First thing, to make our experiments we need a dummy database to run our tests on.
Use this one so we'll have the same fields during the tutorial and following the instruction will be easier.
In this example we're going to use a table that might be used in the case of a webside that collects articles, we keep the articles stored as HTML into the database, classified by genre, keeping track of the number of times each article has been read.
In order to use this table you need to create a dummy database on your server (or on your own pc if you are running some kind of server environment like easyphp or equivalent).
Paste the following code into the SQL interface of PhpMyAdmin and hit "GO".
This will create a table named "dummytable" and will populate it with some data 
CREATE TABLE `dummytable` (
`id` int( 4 ) NOT NULL AUTO_INCREMENT ,
`title` varchar( 30 ) NOT NULL default '',
`text` longtext NOT NULL,
`genre` varchar( 20 ) NOT NULL default '',
`views` int( 11 ) NOT NULL default '0',
PRIMARY KEY ( `title` ) ,
UNIQUE KEY `id` ( `id` )
) ENGINE = MYISAM;
----------------------
INSERT INTO `dummytable` VALUES (1, 'Title of the First article', 'First article main text, some long
 text goes here, I\'ll fill it with blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1
 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1
 blah1 blah1 blah1 blah1', 'webdesign','');
INSERT INTO `dummytable` VALUES (2, 'Title of the Second article', 'Second article main text, some
 long text goes here, I\'ll fill it with blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2
 blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2
 blah2 blah2 blah2 blah2', 'marketing','');
INSERT INTO `dummytable` VALUES (3, 'Title of the Third article', 'Third article main text, some long
 text goes here, I\'ll fill it with blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3
 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3
 blah3 blah3 blah3 blah3', 'photography','');
INSERT INTO `dummytable` VALUES (4, 'Title of the Fourth article', 'Fourth article main text, some
 long text goes here, I\'ll fill it with blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4
 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4
 blah4 blah4 blah4 blah4', 'learning','');
Now we're done with the database, let's start with the PHP code.
We'll consider the case of having urls such as http://www.yourdomain.com/index.php?a=articleID.
In the very first part the code reads the url parameter and, if the "a" exists, assigns its value to the variable $artnumber that we'll use later to query the database and retrieve the data relevant to the current pageview.
You need to replace the $host, $username, $password and $database variable with your own ones.
The PHP script connects to the database, after that will run a query and parse the results generating the HTML code we need to display the content.
The actual request sent to the database is stored in the $query variable. (you will need to alter it each time you want to retrieve different content, in complicated websites query strings are built dinamically).
<?php
$host = 'mysqlserver';
$username = 'mysqlusername';
$password = 'password';
$database = 'databasename';
$content = "";
if (isset($_GET['a']))
   {
     $artnumber = $_GET['a'];
   }
   else
   {
      $content = "No article selected";
   }
$connect = mysql_connect($host,$username,$password);
mysql_select_db($database , $connect);
$query = mysql_query("SELECT * FROM dummytable where id='$artnumber'");
while ($t = mysql_fetch_object($query))
{
  $content .= "<div><h1>$t->title</h1>This article has been viewed $t->views times.<br/>
$t->text</div>";
}
echo $content;       
?>
This is just a basic example of what you can do with databases and you'll agree that, if your site has a lot of pages, it cuts the time you spend on updating/editing its content.
The content in the database isn't static, but can be changed and this is the most important aspect of a database.
Let's consider our example, the views column in the table is totally useless if it's not updated each time the article is read.
Any value in a database can be updated, here follows the code I'd use to update the views:
$updateviews = mysql_query("UPDATE dummytable SET views = views + 1 WHERE
id ='$artnumber'");
This line updates the value stored in the view column in the current $artnumber row to the previous value + 1.
Try putting it after "echo $content;", refresh the page several times and see what happens.
Again, this is just an example but it's easy to understand that there are unlimited possibilities: you could store your visitor's IP and timestamps to track some statistics, or your store's products, prices, descriptions and availability etc etc.
I hope this tutorial helped you and motivated you to build your site using a database, I assure you won't ever come back to static HTML.
Working with PHP and MySQL might be tough at the beginning but once you understand how things work it's not that difficult.
If you face any problem or need help to create your first dynamic page I strongly encourage you to join the forums and ask, it's that simple.