PHP Database Integration With MySQL
One of the defining features of PHP is the versatility it offers for connection to, and manipulation with, databases. In this article, we look at some features of the PHP and MySQL combination. We shall go through the following steps:-
- Connect to MySQL Database Server
- Create new Database
- Select MySQL Database
- Add data to table
- Retrieve data
- Error Handling
Connect to MySQL Database Server:
To work with the MySQL database, we first need to connect to MySQL Database Server. PHP provides mysql_connect() function to do this, and requires three strings as input: ‘hostname’, ‘username’ and ‘password’. Use Code-1 to connect to MySQL Database Server. Code 1: test.php
<html>
<head>
<title>Server test Page </title>
</head>
<body>
<p>Testing working of MySQL.</p>
<?php
/* Connecting to MySQL */
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
print "Connected successfully";
?>
</body>
</html>
If your script has no error, then Code-1 gives output as:
Output 1: test.php
Testing working of MySQL.
Connected successfully
Create new Database :
In the next example, we are creating a database user and under that, we are creating a table visitor. The table visitor will be created with four columns: a primary key called ‘id’ that will be auto incremented as data is added to the table, and the remaining three columns are the character (VARCHAR) fields: ‘name’, ‘address’ and ‘email’. Code-2 will help you to create the table successfully. Code 2 :- db.php
<html>
<head>
<title>Create Database </title>
</head>
<body>
<p>Creating Database & table in MySQL.</p>
<?php
// Connecting to MySQL
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
print "Connected successfully<P>";
$DB = "user";
$table = "visitor";
$query = "CREATE DATABASE $DB";
$result = mysql_query($query) or die("ERROR while creating database".mysql_error());
print("OK, database made, name of DB : $DB<br><br>");
mysql_select_db($DB, $link);
$query2 = "CREATE TABLE $table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(25), address varchar(50), email varchar(25))";
$result2 = mysql_query($query2) or die("ERROR while creating table".mysql_error());
print("OK, table made, name of table : visitor<br><br>");
?>
</body>
</html>
If db.php runs without any error, it generates the output as shown in Output-2.
Output 2: db.php
Testing working of MySQL.
Connected successfully
OK, database made, name of DB: user
OK, table made, name of table : visitor
Select MySQL Database :
Now, we have established the connection with MySQL, and creates database user and table visitor. To work with the database, it must select. To select any MySQL database, PHP provides the mysql_select_db() function. It requires the database name which is to be selected and the link to database. Link is optional and if that is omitted, then the identifier returned from the last connection to server will be assumed as link. Code 3: dbselect.php
<html>
<head>
<title>Server test Page </title>
</head>
<body>
<p>Selecting MySQL Database.</p>
<?php
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
print "Connected successfully<p>";
$DB = "user";
mysql_select_db($DB) or die ("Database $DB not select.." . mysql_error());
print "Successfully select the Database: $DB ";
?>
</body>
</html>
The dbselect.php produces output as shown in Output-3.
Output 3 :- dbselect.php
Selecting MySQL Database.
Connected successfully
Successfully select the Database: user
Adding data to table :
To add data to table, we need to build and execute a SQL query. PHP provides the mysql_query() function for that purpose. mysql_query() requires two inputs: first, the SQL query and second, a link identifier. Identifer is optional. If omited, then query is sent to the database server to which you last connected.mysql_query() returns true if the query executes successfully. If there are any syntax errors or if you do not have permission to access database, then it return false.
Code 4 :- adddata.php
<html>
<head>
<title>Server test Page </title>
</head>
<body>
<p>Display data from MySQL Database.</p>
<?php
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
print "Connected successfully<p>";
$DB = "user";
$table = "visitor";
mysql_select_db($DB) or die ("Database $DB not select.." . mysql_error());
print "Successfully select the Database: $DB ";
$query = "INSERT INTO $table(name,address,email) values('max','nagpur','
This e-mail address is being protected from spambots, you need JavaScript enabled to view it
')";
if ( ! mysql_query( $query, $link) )
die ( "MySQL error.....<p>" .mysql_error() );
print "<p>Successfully data added to table : $table";
?>
</body>
</html>
The adddata.php produces output as shown in Output-4.
Output 4: adddata.php
Display data from MySQL Database.
Connected successfully
Successfully select the Database: user
Successfully data added to table : visitor
Retrieve data:
The mysql_query() function of PHP also allow us to get data from table. The following code gives you an idea about that...
$result = mysql_query( "SELECT * FROM $table");
$total_rows = Â
After perfoming SELECT operation using mysql_query() function, result is stored in the identifier $result. Now with the mysql_num_rows() function, we get the total number of rows of table ‘visitor’.
Now, to display data of table, we use the PHP function mysql_fetch_row() which gives us all data of the table. mysql_fetch_row() returns false when it finds end-of-data in the table. We use it with the while condition to display the content of table ‘visitor’. Following code displays table content (Code-5).
Code 5: printdata.php
<html>
<head>
<title>Server test Page </title>
</head>
<body>
<p>Display table content of MySQL Database.
<?php
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
print "<p>Connected successfully<p>";
$DB = "user";
$table = "visitor";
mysql_select_db($DB) or die ("Database $DB not select.." . mysql_error());
print "Successfully select the Database: $DB ";
$result = mysql_query( "SELECT * FROM $table");
$total_rows = mysql_num_rows( $result );
print "<p>There are $total_rows in table : $table </p><p> Table contents are: </p><P>";
print "<table border=1> \n";
while ( $pr_row = mysql_fetch_row( $result ) )
{
print "<tr>";
foreach ( $pr_row as $data )
print "\t <td>$data</td>";
print "</td>\n";
}
print "</table>\n";
mysql_close ( $link );
?>
</body>
</html>
Error Handling:
When performing any operation on MySQL using PHP, if any error occurs, our script will not work properly. A single error can cause hundreds of lines of code to not work properly. Here, PHP provides some special functions to print more informative error messages to the browser to aid debugging. MySQL gives an error message and an error number when an operation fails. PHP provides the function mysql_error() to print error message and mysql_errno() to print error number to browser, which becomes very useful while debugging the code. You can easily get output for mysql_error() if any error occurs while executing code given in this article. You can just replace mysql_error() with mysql_errno() to get an error number in place of error message.
Source Code:
Download Source code for 'PHP Database Integration With MySQL'
Download
TrackBack URI for this entry
Subscribe to this comment's feed
written by very very helpfull tutorial, November 21, 2008
Very very helpfull tutorial
written by Gaxton, June 03, 2009
I'll like say so much thanks for this... I want to build similar form but I want to include a part where the visitor has to upload his/her picture and this picture will be displayed along with the data of the person in a tabular format like the above...
Thanks!
Thanks for Such Good Tutorial.
Its a good Resource for beginners.
Thanks
Vijit Patil