PHP - Working with Forms, Working with Database
Working with Forms
Creating Simple HTML form
More Complex Form
Using validation with Forms
Working with Database
Introduction
Creating a Connection
Retrieving Database and Table list
Creating Databases and Tables
Using MySQL DML command
Retrieving record from database
Error Handling
Working with Forms
Creating Simple HTML form
HTML Forms
What makes the Web so interesting and useful is its ability to disseminate information as well as collect it, primarily through an HTML-based form. These forms are used to encourage site feedback, facilitate forum conversations, collect mailing addresses for online orders, and much more. But coding the HTML form is only part of what's required to effectively accept user input; a server-side component must be ready to process the input.
Note:
This is an simplified example to educate you how to use PHP to process HTML form information. This chapter does not describe forms in details or in simple words this isn't a HTML form tutorial. In this chapter we will just tell you that how to obtain and process the information that your site user has entered in the form. For HTML tutorial, please visit out XHTML/HTML tutorial at http://education.mkdtutorials.com.
Creating a Simple HTML Form
In this example, we will create a simple form, which will accept user input and when user click on submit button sends the information to the server side PHP script to process the information.
This example is divided in two parts:
1. Client side HTML form [simpleform.php]
2. Server side form processing script [simpleform.php]
Source code simpleform.php
The example HTML page above contains two input fields and a submit button. When the user fills in this form and click on the submit button, the form data is sent to the "simpleform.php" file. The above page should look like the figure given below:
The server side script:
source of simpleform.php
Now open the simpleform.php in browser, fill the form and press submit the form to view the output. Your output should be like one given below:
As you can see, we have used the following syntax to retrieve the value that we have entered in the from.
There are two common methods for passing data from one script to another:
1. GET and 2. POST.
Syntax to access value of a form field:
if the method is GET:
$_GET["form_field_name"];
if the method is POST $_POST
["form_field_name"];
In the above example GET method is used to make the example verbose. You can use any method as per your choice and requirement. But, GET method is not recommended for that contains sensitive information such as Login form, form that accepts credit card information, etc and form that accepts lengthy data.
Although GET is the default, you'll typically want to use POST because it's capable of handling considerably more data, an important behavior when you're using forms to insert and modify large blocks of text. If you use POST, any posted data sent to a PHP script must be referenced using the $_POST syntax.
For example, suppose the form contains a text-field value named email that looks like this:
<input type="text" id="email" name="email" size="20" maxlength="40" />
Once this form is submitted, you can reference that text-field value like so:
$_POST['email']
Of course, for sake of convenience, you can first assign this value to another variable, like so:
$email = $_POST['email'];
But following the best practice of never presuming user input will be safe, you should filter it through one of the several functions capable of sanitizing data, such as htmlentities(), like so:
$email = htmlentities($_POST['email']);
The htmlentities() function converts strings consisting of characters capable of maliciously modifying an HTML page should the user-submitted data be later published to a Web site, such as a Web forum.
More Complex Form
In this example we will create a self submitting form, which accept user input process the information and display the output, and all in one page.
Step-by-Step description:
if (!isset($_POST['submit'])) { //if the form is not submitted we will display the form itself. Otherwise display the contents of the form. ?>
In the section we have declared variables to store the values of form fields. In the last line of the above declaration we have used isset() function to check if the form has been submitted. If user has submitted the form, then it will jump directly to else part.
The else part
In the else part we are just printing the value entered by user. As you can see above, this part will only execute if the has submitted the form.
The complete Code...
Click here to view the code [Link the feedback.php.txt]
save the file as feedback.php and open the script to view the output. The page should look like the one given below:
When User Fills the form and submit it, user will get the following screen.
Using validation with Forms
In the example, we created a form that accepts user input, but nothing stops the user to enter incorrect value in the form fields. User may enter invalid email id, e.g. email that does not contains @/. etc. In such a scenario the information coming to server is incorrect and incomplete.
To keep the information correct, validations are used. Validation assures that the we have already filtered the unwanted values, and we can now assume that, the information available on the server side is accurate. Validation is necessary to insure the quality, accuracy and desirability of data. With validation we can force the user to enter valid value in the respective form fields so that user does not attempt to submit incomplete form.
There are two types of validation:
1. Server side validation this type of validation uses server-side technologies such as ASP, JSP or PHP and validation is performed on the server.
2. Client side this type of validation runs on client browser and user client side technologies such as Javascript. This type of validation entirely runs on client browser hence reduces the processing load on the server and increases server response time.
Note: This chapter assumes that you've a sound understanding of Javascript. If you to learn Javascript, please refer to our Javascript tutorial at http://education.mkdtutorials.com.
Improved Feedback form with Client side validation
Unlike the previous example which was divided in 2 parts, this example is divided in three parts:
1. HTML Form
2. Client Side JavaScript
3. PHP script to process the information
Part 3, the PHP script is same as the previous example. Part 1 the HTML is same with minor changes in the form declaration. Part 2, the Client side JavaScript force the user to enter data in all mandatory form fields.
The JavaScript
The HTML:
Only the line below has been modified to use the JavaScript:
Rest, everything is same.
Assignment
1. Create an HTML form form.php, which contains 4 fields:
Name (text box),
Mobile Number(text box)
Gender (radio button)
Address (text Area)
On submitting the form, data goes to form.php which process the data and display it in HTML table, like one given below:
NAME
Mobile Number
Gender
Address
[form data]
[form data]
[form data]
[form data]
2. in the above HTML form (form.php) use JavaScript validation to check no form field is empty and all fields contains valid values
Working with Database
Introduction
What is Database?
A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model. The model in most common use today is the relational model. Other models such as the hierarchical
A database is an organized collection of data that is useful to us. The data inside in a database can be modified, deleted or new data can be added.
They are stored in the form of records as shown in the table below:
The above table shows a single record. The columns-Associate_TID, Associate_name, Age ,Commission are the fields or attributes, while the row containing the corresponding the values-900678432, Smith, 28, 25000 is a single record. There can be a number of records in a table and number of tables in a database.
The tables in a database are related to each other through one/more attributes/fields. A Database is diagrammatically shown below. It consists of two tables-Associate and Asso_INFO. While Associate stores information regarding an Associate TID, dept and salary, the table Associate_INFO stores TID, name and date of join of the Associate. The common link between the tables is established by the field Asociate-TID.
Why use MySQL?
If you're looking for a free or inexpensive database management system, several are available from which to choose: MySQL, PostgreSQL, one of the free-but-unsupported engines from commercial vendors, and so forth.
When you compare MySQL with other database systems, think about what's most important to you: Performance, support, features (SQL conformance, extensions, and so forth), licensing conditions and restrictions, and price all are factors to take into account. Given these considerations, MySQL has many attractive features to offer:
. Speed:
MySQL is fast. The developers contend that MySQL is about the fastest database you can get.
. Ease of use:
MySQL is a high-performance but relatively simple database system and is much less complex to set up and administer than larger systems.
. Query language support.
MySQL understands SQL, the language of choice for all modern database systems.
. Capability
Many clients can connect to the server at the same time. Clients can use multiple databases simultaneously. You can access MySQL interactively using several interfaces that let you enter queries and view the results:
command-line clients, Web browsers, or X Window System clients. In addition, a variety of programming interfaces are available for languages such as C, Perl, Java, PHP, and Python.
. Connectivity and security:
MySQL is fully networked, and databases can be accessed from anywhere on the Internet, so you can share your data with anyone, anywhere.
. Portability:
MySQL runs on many varieties of UNIX, as well as on other non-UNIX systems, such as Windows and OS/2. MySQL runs on hardware from home PCs to high-end servers.
. Small size:
MySQL has a modest distribution size, especially compared to the huge disk space footprint of certain commercial database systems.
. Availability and cost MySQL is an Open Source project, freely available under the terms of the GNU General Public License (GPL). This means that MySQL is free for most in-house uses. (If you want to sell MySQL or services that require it, that is a different situation and you should contact MySQL AB.)
. Open distribution:
MySQL is easy to obtain; just use your Web browser. If you don't understand how something works or are curious about an algorithm, you can get the source code and poke around in it. If you don't like how something works, you can change it. If you think you've found a bug, report it; the developers listen.
. MySQL is a relational database management system:
A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility.
The SQL part of "MySQL" stands for "Structured Query Language. SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist.
Creating a Connection
Before you can access and work with data in a database, you must create a connection to the database.
In PHP, mysql_connect() function is used to connect to database . Syntax of mysql_connect:
$con=mysql_connect("localhost/ip", "user_name", "password");
MySQL localhost
If you've been around the internet a while, you'll know that IP addresses are used as identifiers for computers and web servers. In this example of a connection script, we assume that the MySQL service is running on the same machine as the script.
When the PHP script and MySQL are on the same machine, you can use localhost as the address you wish to connect to. localhost is a shortcut to just have the machine connect to itself. If your MySQL service is running at a separate location you will need to insert the IP address or URL in place of localhost. Please contact your web host for more details if localhost does not work.
Example:
Source code of mysql_connect.php
The above example attempts to connect to MySQL database. If connection is established then print the success message like the one below
Otherwise displays the mysql error.
Retrieving Database and Table list
Now that you've successfully used PHP to make a connection to MySQL, it's time to familiarize yourself with some of the built-in MySQL-related functions. In this section, you use the following functions:
. mysql_list_dbs()- Used to list the databases on a MySQL server.
Syntax: resource mysql_list_dbs ( [resource link_identifier ] )
mysql_list_dbs() will return a result pointer containing the databases available from the current mysql daemon.
. mysql_num_rows()- Returns the number of rows in a result set.
Syntax:
int mysql_num_rows ( resource result )
mysql_num_rows() returns the number of rows in a result set.
. mysql_tablename()- Despite its name, can extract the name of a table or a database from a result.
Syntax:
string mysql_tablename ( resource result, int i )
Getting database list
Description:
After connecting to database we have created a variable to hold the result of the mysql_list_dbs() function.
$total_db=mysql_list_dbs($con)or die(mysql_error());
After declaring all necessary variables begin a for loop. This loop will continue for as long as the value of $i is less than the number of rows in the $total_db result value:
for($i;$i<mysql_num_rows($total_db);$i++) {
Once you're within the for loop, get the name of the database reflected in the current row of the result:
$db_list[$i]=mysql_tablename($total_db,$i);
$list_of_dbs.="<li>$db_list[$i]</li>";
Close the for loop, the bulleted list, and your PHP block:
Save the above script and open the page in browser to view the output.
Output:
Output may vary from system to system depending upon how much you have played with your MySQL server, but it should look like the one below:
Getting Table list
To retrieve the list of tables we have just added 1 more for loop inside the main for loop. This for loop retrieve list of tables of each database and add it to the list.
Output: Try it yourself
Creating Databases and Tables
Creating Databases
PHP Script to create database
How the above example works?
1. Create a variable and store the database name user has entered in the form.
$db_to_create=$_POST["db_name"];
2. Create an object of dbUtils class
$dbutil=new dbUtils();
3. Call the createDB function of dbUtils class and pass
$db_to_create $dbutil->createDB($db_to_create);
4. in the createDB function of dbUtils class, check if the database name is blank.
5. Create a variable to hold the query to issue, which will create the new database:
$sql = "CREATE database ".$db_to_create;
6. Add the connection information just as you have been doing:
$con = $this->connect();
7. Check connection is established with database or not
if($con){
7. Issue the query, using the mysql_query() function.
$result = mysqlquery($sql, $con);
8. Check if result is initialized and return the result.
if($result){
Note:
The concept of $PHP_SELF variable and isset() function
has been described in the previous chapter.
Output:
Creating Tables
In this example we are going to use a web based interface to create tables. The interface gives you a list of available databases, a Text field to enter name of table and a Text area to enter table structure in SQL format.
Our interface will look like the one below:
This example is a multi file PHP script and it is divided in to parts:
PHP script to retrieve list of database and print it in desired format
Main PHP script, this one is similar to previous example, except that we are issuing a Create Table command, instead of Create Database command.
In the Table name field enter test_tbl field and enter the following SQL command in the Command Text area
Output:
Try it yourself.
Note: in the coming subsections of this chapter this table (test_tbl) will be used for reference, so make sure you have created the above table.
Using MySQL DML command
When data is put into a MySQL table it is referred to as inserting data. When inserting data it is important to remember the exact names and types of the table's columns. If you try to place a 500 word essay into a column that only accepts integers of size three, you will end up with a nasty error!
Note: As stated in the previous sub-section, we are going to use the table created in the previous sub-section for examples coming in this chapter.
Inserting records
Now that you have created your table, let's put some data into that table! Here is the PHP/MySQL code for inserting data into the table we created in the previous lesson [since we have the interface ready we can create the table anytime and I assume that you've already created one].
About Script should produce the following HTML form
After you fill data in the form and click on Insert record button it should display the following output
Explanation of the above script:
1. In the above script, at first we check if the form has been submitted
2. If the form has been submitted then we retrieve the form field values and construct the query
3. Now, we create an object of dbUtils class
$dbutil=new dbUtils();
4. Call the insert_record function of dbUtils class
$msg2=$dbutil->insert_record($db,$sql);
5. In the insert_record function change the database to use user specified database and execute the query to insert the record into the database table, besides other required rituals
Updating Records
Following PHP script update the name of the user whose id is 2:
This example is like the previous example, except that we are updating the record(s), and we've used update command instead of insert command.
Deleting Records
Above PHP script delete a record from table test_tbl whose id is 2.
All three examples above are same except the SQL command used. As you can see it is quite easy to play with MySQL using PHP. Of course, you can use HTML forms to perform these operations. By doing so you can perform SQL operations dynamically.
Retrieving record from database
I assume that you have read tutorial of adding data into tables. I will try my best to keep it as easy as I can.
Functions we will use:
Ok - now I assume that you have read the above tutorial I mentioned in which we inserted data into table name as test_tbl. Now suppose we have a table named as "test_tbl" and it has the following fields.
id, name and address, email.
and we have N members registered (means we have N rows, where we N is the number of rows/records in the table ) here they are (the data in test_tbl table) so these are the total values inserted in table "test_tbl". Now I will make a PHP file mysql_select.php which will retrieve data from test_tbl table.
<pre class=