PHP Database Integration With MySQL--tutorial.jcwcn.com

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>
Testing working of MySQL.
<?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

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>
Creating Database & table in MySQL.
<?php
 // Connecting to MySQL
 $link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
 print "Connected successfully";
 $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 ");
 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 ");
?>
</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

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>
Selecting MySQL Database.
<?php
 $link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
 print "Connected successfully";
 $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

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>
Display data from MySQL Database.
<?php
 $link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
 print "Connected successfully";
 $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',' test@yahoo.co.in ')";
 if ( ! mysql_query( $query, $link) )
 die ( "MySQL error....." .mysql_error() );
 print "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

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 = mysql_num_rows( $result );

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>
Display table content of MySQL Database.
<?php
 $link = mysql_connect("mysql_host", "mysql_user", "mysql_password") or die("Could not connect : " . mysql_error());
 print "Connected successfully";
 $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 "There are $total_rows in table : $table Table contents are: ";
 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>

After executing Code-5 at my terminal, I got output as in Figure-1. (Please note that I have added data to the table. Here you see only the structure of the output.)

PHP MySQL Database Integration

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.

Printable Document Generation with PHP hot!

14.03.2007

Download Source code for 'Printable Document Generation with PHP '

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

PHP Database Integration With MySQL

Connect to MySQL Database Server:

relation tutorial

Category

New

Hot