Open source training & tutorials from experienced, passionate people
By A Web Design
PHP's strongest feature is its capability interface with a database. Connecting to and using database content for an Internet application has never been as easy as with PHP.
PHP supports many of the most popular database servers in use today , including MySQL (perhaps the most powerful and commonly found database server in use today). PHP permits the creation of Internet/Intranet based user interfaces that interact in real time with MySQL database tables.
MySQL is a database server. PHP, gives Internet/Intranet builders and users a way to access information stored in MySQL to produce dynamic web pages in real time with the help of the PHP / MySQL combination.
The PHP MySQL Architecture is as shown in Diagram 1. It depicts the following:
The Visitor's
The Web Server (Apache or IIS)
PHP
MySQL Database
PHP
The Web Server (Apache or IIS)
This means PHP, as server side scripting language can deal with MySQL (using its natural language) and HTML (to deal with the client Browser).
Diagram 1
MySQL stores its data in user defined tables. These user defined tables must belong to a MySQL database. Hence the first MySQL object we will learn to create using PHP is a MySQL database.
Here is how to connect to the MySQL Db Engine running in memory using PHP codespec:
mysql_connect( localhost, $username, $password );
The built in PHP method, mysql_connect() is used to establish a connection to the MySQL Db engine running in memory on the local computer (or on an entirely different computer). mysql_connect() requires at least three values to succeed, they are:
The above syntax tells PHP to connect to a MySQL Db engine running in the memory (RAM) of the localhost (i.e. localhost indicates that the MySQL Db engine is running on the same computer as the PHP interpreter) using a specific username and password contained within the PHP variables $username and $password.
Most functions return a value. That value is always stored in a variable for later use. mysql_connect() being a functions returns a value.
mysql_connect(), returns a number that identifies a connection that has been successfully established. The value returned by this function should be stored in a variable.
$dbcon = mysql_connect(localhost, $username, $password);
After this sentence is executed by the MySQL db engine use PHP codespec to check the value contained in $dbcon to establish whether the MySQL database connection was successful or not. It is good programming practice to verify that MySQL Db connection was established successfully.
The Connection usually fails due to one (or more) of the following reasons:
In any of the above cases, mysql_connect() will not return a value since no connection has been established. Instead, it will return FALSE.
This allows a programmer to react to such failures using an if statement as shown in Example 1.
Example 1:
$dbcon = @mysql_connect(localhost, $username, $password); if (!$dbcon) { echo("Database Connection Failed!"); exit(); }
NOTE: The @ symbol in front of mysql_connect() informs mysql_connect() that if the Db connection failed do anything the system error normally generated must be suppressed. Then the user defined error message within the If <condition> will be the only error message displayed. If this is not done the System Error Message will be displayed first ( system error messages always take precedence ), then the user defined error message will be displayed, i.e. two error messages will be displayed.
In the code spec of Example 1, error handling is done using an if condition, hence internal system errors are suppressed.
The code spec, returns a value to $dbcon. If $dbcon holds FALSE further processing of the PHP program is terminated after displaying an appropriate user defined message.
Once a successful Db connection is established, the table will be created within the database.
After having connected to the MySQL DB engine, running in the computer's memory (i.e. RAM) there maybe multiple MySQL databases that exist. Hence a specific MySQL database must be selected first, for ex. studentdb. The table StudentMaster is going to be created within the MySQL database studentdb.
The following PHP code snippet does this:
mysql_select_db($database_name);
This tells the PHP program to select the database stored in the variable $database_name, programmatically set earlier.
mysql_select_db() accepts two parameters. The first parameter is the Db name and the second parameter is an optional parameter. The second parameter is a valid connection identifier that is returned by mysql_connect(). This parameter informs the PHP interpreter which database connection to use while selecting the database. If the second parameter is omitted, mysql_select_db() automatically uses the current active connection.
mysql_select_db ($database_name, $dbcon) or die("Database Not Found");
This function returns TRUE when successful and FALSE otherwise.
Now that you are aware of:
Let’s blend this learning together into a single PHP program that connects the MySQL db engine and selects an appropriate MySQL database to work with.
Example 2:
$dbcon = @mysql_connect(localhost, $username, $password); if (!$dbcon) { echo("Database Connection Failed!"); exit(); } if (! @mysql_select_db ($database_name, $dbcon)) { echo("Database Not Available!"); exit(); }
Alternatively, the same effect can be achieved using the following code spec:
$dbcon = @mysql_connect(localhost, $username, $password); if (!$dbcon) { echo("Database Connection Failed!"); exit(); } mysql_select_db ($database_name, $dbcon) or die("Database Not Found");
If the PHP interpreter cannot connect to the MySQL database specified, it will display the error message, Database Not Found and stop executing the PHP script. This extra or die part is good to leave in, as it provides minimal user defined error handling, but is not essential.
Since a successful connection with the MySQL DB engine has been established and the database to be used is selected, ANSI SQL commands can be fired against specific database table(s) to manipulate table data.
To connect to a MySQL table for the purpose of table data manipulation use the built in PHP function mysql_query(). mysql_query() accepts a variable which was previously loaded with the query string.
The command looks like this:
mysql_query($query);
Here $query holds a string that is the SQL command must be executed.
mysql_query() provides a functional interface via which database table(s) can be queried. It accepts a parameter which corresponds to an SQL query. The useful thing about using this technique is that mysql_query() can be used to fire different query strings against a table simply by changing the query string held in the variable being passed to it.
Depending on the type of query string, mysql_query() will do different things.
This is shown below:
$resultSet = mysql_query($query);
If for any reason the query fails, mysql_query() returns FALSE.
The number of rows that have been selected from the table can be determined by using mysql_numrows(). Simply pass $resultSet as a parameter to it.
In SQL statements involving INSERT, UPDATE or DELETE, the function mysql_affected_rows() can be called to determine how many rows were affected by the query.
Go to any convenient logical drive on your hard disk and create a folder named public_html. In our example we’ve used G:\. If you already have such a folder on your hard risk because you have been using a web server before simply use it. Within the folder public_html create a folder named studentinfo as shown in Diagram 2.
Make the folder named studentinfo, a virtual domain under your web server. Hopefully your web server is Apache.
NOTE: To read about how to create Virtual Domain under Apache web server please refer to lesson - Creating Virtual Domain Under Apache.
Diagram 2
In the studentinfo folder, create a php file named config.php using any ASCII editor such as Notepad, Dreamweaver, Editplus or your favorite ASCII editor, as shown in Diagram 3. config.php will contain the codespec as shown in Example 3 below.
Diagram 3
NOTE: It is a good practice to keep your MySQL database connection details in a single file for ease of maintenance.
It is necessary to connect to MySQL database engine running in memory. To be able to do this within the PHP code spec must have a User name, Password and exactly where the MySQL db engine is running. In this case ‘localhost’ indicates where the MySQL db engine is running, i.e. the same computer in which the PHP code snippet is being executed.
Example 3:
<?php $user = "root"; $pwd = "passwd"; $host = "localhost"; $con = @mysql_connect($host, $user, $pwd) or die("Connection Failed!"); if($con) { echo "Connection Established!"; } ?>
Copy paste the above codespec into the file named config.php and save this file in:
G:\public_html\studentinfo
Once done config.php can be invoked using any Browser by simply entering:
http://<URL to the virtual domain>/config.php
in its address bar.
When config.php executes at the web server its codespec will establish the MySQL Connection as shown in Diagram 4.
There is a minimum of error handling added to the PHP code within config.php. If the connection to MySQL succeeds, a message “Connection Established!” will be displayed on the VDU screen as shown in Diagram 4.
Diagram 4
If the connection to MySQL fails, a message “Connection Failed!” will be displayed on the VDU screen as shown in Diagram 5. This is because if some of the parameter value passed is incorrect to mysql_connect().
Diagram 5
In the studentinfo folder, create a php file named createdb.php using any ASCII editor such as Notepad, Dreamweaver, Editplus or your favorite ASCII editor, as shown in Diagram 6. createdb.php will contain the codespec as shown in Example 4 below.
Diagram 6
To create a MySQL database (i.e. studentdb,), it is necessary to connect to MySQL database engine running in memory. To be able to do this within the PHP code spec include the config.php file once. The codespec to include the config.php file in createdb.php is as given in Example 4.
The include_once() statement, as given in Example 4, includes and evaluates the specified file during the execution of the script. This is a behavior similar to the include() statement, with the only difference being that if the code from a file has already been included, it will not be included again. As the name suggests, it will be included just once.
Example 4:
<?php include_once('config.php'); $qry="create database studentdb"; $result=mysql_query($qry); if($result) { echo("The MySQL Database Successfully Created!"); } else { echo("The MySQL Database Creation Failed!"); } ?>
Copy paste the above codespec into the file named createdb.php and save this file in:
G:\public_html\studentinfo
Once done createdb.php can be invoked using any Browser by simply entering:
http://<URL to the virtual domain>/createdb.php
in its address bar.
When createdb.php executes at the web server its codespec will create the MySQL database named studentdb.
There is a minimum of error handling added to the PHP code within created.php. If the connection to MySQL and the database creation succeeds, a message “The MySQL Database Successfully Created” will be displayed on the VDU screen as shown in Diagram 7.
Diagram 7
If the connection to MySQL fails, a message “Connection Failed!” will be displayed on the VDU screen.
If the connection to MySQL database creation fails, a message “The MySQL Database Creation Failed” will be displayed on the VDU screen
After creating the MySQL database - studentdb, which is an empty container, it is necessary to populate this empty container with table structure(s) to hold user data which can in turn be manipulated using PHP code.
The first table we are going to create using PHP code within the MySQL database – studentdb is the table StudentMaster. This MySQL table will be populated with student data to be manipulated later on demand using PHP codespec. Let’s take a look at the structure of the MySQL table StudentMaster :
Table Definition:
| Table Name: | StudentMaster |
Column Definition:
| Column Name | Data Type |
Width |
StudentID |
INTEGER |
3 |
StudentName |
VARCHAR |
25 |
StudentDegree |
VARCHAR |
25 |
Speciality |
VARCHAR |
35 |
DOB |
VARCHAR |
15 |
Table Description:
| Column Name | Description of column contents |
StudentID |
Holds an identity number of the student |
StudentName |
Holds the name of the student |
StudentDegree |
Holds the degree acquired by the student |
Speciality |
Holds the specialty of the student |
DOB |
Holds the Date of birth of the student |
In the studentinfo folder, create a php file named studentmaster.php using any ASCII editor such as Notepad, Dreamweaver, Editplus or your favorite ASCII editor, as shown in Diagram 8.
Diagram 8
Example 5:
<?php include_once('config.php'); $db = "studentdb"; mysql_select_db($db) or die("Could not select database!"); echo "The MySQL Database Connection Established!"; $query = "CREATE TABLE StudentMaster( StudentID INTEGER(3), StudentName VARCHAR(25), StudentDegree VARCHAR(25), Speciality VARCHAR(35), DOB VARCHAR(15))"; if (@mysql_query($query)) { echo("<BR />Table created successfully."); } else { echo("<BR />Error creating table"); } ?>
Copy paste the above codespec into the file named studentmaster.php and save this file in:
G:\public_html\studentinfo
Once done createdb.php can be invoked using any Browser by simply entering:
http://<URL to the virtual domain>/studentmaster.php
in its address bar.
If the MySQL connection gets established, a message “Connection Established!” will be displayed on the VDU screen as shown in Diagram 9.
There is a minimum of error handling added to the PHP code within studentmaster.php. If the database connection is established, a message “The MySQL Database Connection Established!” will be displayed on the VDU screen as shown in Diagram 9. It will then create a table in a MySQL Db engine running in memory.
If the query is executed successfully, it will display “Table created successfully.” on the screen as shown in Diagram 9.
Diagram 9
Now you have successfully created a database. Using the newly created database, we have successfully created an empty table(s) structure with no data. This table needs to be populated with data. In the later lesson, we will cover how to insert data into this empty table(s) structure.
