Lesson 7 - Forms and Databases
Dreamweaver Students: this is a very challenging assignment, and requires you to have some rudimentary knowledge of PHP, databases, and in particular, MySQL - if you are unsure about this, email me before jumping in! This tutorial is being developed in September - October 2008, and should be complete and posted by November 1st, 2008.
Databases
A database stores information in tables where the columns define what the data is and where each row is a record which is an entry. A table could be defined to hold information about a person's address, the street, city, zipcode. Each row would represent the address of a different person. Often the first column is an id that is incremented automatically for each entry. It is usually a primary key which is unique. Tables may be related to one another which is often done using the primary key. This defines a relational database.
We will be using the relational database called MySQL which is Open Source and free. We will be getting it in the XAMPP bundle which is described further down in this lesson.
PHP
We'll use PHP as our means of connecting the form to the database. PHP stands for PHP: Hypertext Preprocessor. PHP is a widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML. If you are new to PHP and want to get some idea of how it works, try the introductory tutorial. After that, check out the online manual, and the example archive sites and some of the other resources available in the links section. You can download PHP files for Linux from here.
If you have your own MySQL database on a Web host, feel free to use that for this exercise. If you would like to host it on a free site open an account at www.freehostia.com or www.leadhoster.com. A third option is to download a web server, the database, and php to your computer and run it as a localhost.
If you select the third option then I recommend that you use XAMPP which is an open source, cross platform package that contains the Apache Web Server, MySQL database, PHP, and Perl. Go to the XAMPP web site and follow their directions to download it and to install it on your computer. If you are on windows platform and you install the mysqli (extension) make sure that both php_mysql.dll and php_mysqli.dll are in the php.ini file. My php.ini file is located in xampp/apache/bin directory. This is the location in php.ini file where the dlls are:
Using phpMyAdmin to Access MySQL
In this lesson I will be using XAMPP. If you are using your host then access phpMyAdmin from your host instead of from localhost.
Bring up the XAMPP Control Panel, start the Apache web server and MySQL database, then use phpMyAdmin to access the database. It is accessed from localhost: http://localhost/phpmyadmin/. You will be asked for your phpMyAdmin username and password that you selected when you installed it.
The first thing you will do will be to create a database. Select the database link and then create a new database.
Create a new table that will contain the guestbook information. Name the table and create 7 fields.
Create the 7 new fields with the following values:
After you save the table it will show the following and the id will be the primary key. Whenever a new guestbook entry is written to the table the id will automatically increment and it will be unique.
The database and table are now ready to be populated. We will be using a form that the user will fill out and will also show the guestbook entries at the bottom. On submit the information will be written into the database and then the guestbook list will be updated on the same form. This means that the form serves 2 purposes, one for showing the entries, and another for entering new information.
Php Form
Next, a php file, in this case DWguestbook.php, is created to display the database entries and to display a form the user may fill in to add an entry to the database. This form is popped up using the link on a different page:
<a href="DWguestbook.php" target="_blank"
onclick="window.open(this.href, 'popupwindow','width=800, height=720, scrollbars=yes');return false;">View Guestbook</a>
The php file looks just like an html file with <head> and <body> tags. Within the <body> tag php code may be added. The php code must be within the php tag set:
<?php php code ?>
To let the server know that the file contains php scripting code the file is given the php extension. You may use DWguestbook.php as a template to build your guestbook. To use it you must insert your username and password where is says yourUsername and yourPassword. If you are not using the web server on your computer then you must change localhost to that of your domain. Notice the <?php tag and that it is within the <body> tag. The ending tag is further down in the file.
The file has three parts. The first part is where the values, input by the user, are read in from the form.
After they are read in they are stored in the database. A connection is made to the database, a query string is created, and the query is made on the database to insert the values, then the database connection is closed. Notice the end php tag, ?>.
The second part of the file contains the form and it is done in html which is outside the php tags. The arrow points to the php file that is to be executed when the submit button is pressed. It is the same name as the file that it is in. This is a dual function file. It serves to read the values input by the user and to display the guestbook entries.
The third, and last part, is in php where the guestbook entries are read from the database and displayed in html. This first image sets up the connection to the database and the query to get the 50 latest entries.
An html table is created to hold all of the guestbook entries. The first row of the table contains the timestamp and the name. The second row contains the comment. 'echo' statements are used to output the html. The stripslashes are used to remove delimiters that are necessary for database storage. When the data was being stored addslashes function was used in part 1.
Click to get a copy of DWguestbook.php to use as a template. In Firefox it will display the contents of the code so you will have to copy it into a file. In IE it will attempt to display it so 'View Source' and then copy those contents to a file.
Using the Form in the Browser
After the file has been created it must be copied into the directory where localhost is connected which is in the htdocs directory where XAMPP was installed. In this case there are 2 files that must be copied. This path is on windows platform:
c:\xampp\htdocs
There are other ways of doing this but this is the easiest.
In the browser I type in the name of my html file, DWcontact.html, that has the link that pops up the guestbook form:
http://localhost/DWcontact.html
There are 3 prior entries and another one is in the process of being input. Note that whenever you make a change to the php file you must copy it into htdocs.
Accessing MySQL from Dreamweaver
First bring up XAMPP Control Panel and start Apache and MySQL. In Dreamweaver a connection must be made with the database. If the Databases panel is not showing bring it up from the file menubar under Window>Databases. In the Databases panel click on the plus key and select MySQL Connection.
Fill out the popup window. Connection name may be anything. Since we are using XAMPP the server name is localhost. Enter the username and password to MySQL database and the name of the database that is to be used. In this example I used guestbook.
After the OK button is pressed information describing the structure of the database is displayed in the Databases panel.
- Tables lists all of the tables in the database. Clicking on a table shows all of the fields in that table.
- Views lists all of the views in the database. A view is a selection of data.
- Stored Procedures lists functions that access and manipulate data in the database and they are stored in the database to enhance responce time.
To view the data, right-click (control-click) on the table name in the Databases table. A window will pop up showing all of the data associated with that table.
Data associated with the table is displayed in the popup window.
PHP Tutorials from The Missing Manual
I have downloaded PHP tutorials from 'The Missing Manual' which you might find useful. If you are using The Missing Manual as your textbook - these advanced tutorials will help you with the advanced features of PHP and MySQL. The first one is in this lesson and the others are in the next lesson where those features are discussed.
PHP Tutorial 1 - Getting Started with Dynamic Web Sites
References
Dreamweaver CS3 The MIssing Manual by David Sawyer McFarland.
Database Design for Mere Mortals (Addison-Wesley Professional) by Michael J. Hernandez - A great book on database design.
For concise, online introductions to database designs visit this site.
Copyright © 2008 - 2009 Robert D. Cormia - November 5, 2008