Deprecated: Assigning the return value of new by reference is deprecated in /home/content/48/9096548/html/xfunda.com/components/libraries/cmslib/spframework.php on line 76

Deprecated: Assigning the return value of new by reference is deprecated in /home/content/48/9096548/html/xfunda.com/components/libraries/cmslib/spframework.php on line 571

Deprecated: Assigning the return value of new by reference is deprecated in /home/content/48/9096548/html/xfunda.com/components/libraries/cmslib/spframework.php on line 572

Deprecated: Assigning the return value of new by reference is deprecated in /home/content/48/9096548/html/xfunda.com/components/libraries/cmslib/spframework.php on line 580

Deprecated: Function eregi() is deprecated in /home/content/48/9096548/html/xfunda.com/templates/ja_barite/ja_menus/Base.class.php on line 62
PHP Database Integration With MySQL

Excellent Fundamental...!!!

 
  • Increase font size
  • Default font size
  • Decrease font size
  • default color
  • green color
  • blue color

Deprecated: Function eregi() is deprecated in /home/content/48/9096548/html/xfunda.com/plugins/content/emailcloak.php on line 50

Deprecated: Function eregi() is deprecated in /home/content/48/9096548/html/xfunda.com/plugins/content/emailcloak.php on line 67

Deprecated: Function eregi() is deprecated in /home/content/48/9096548/html/xfunda.com/plugins/content/emailcloak.php on line 79

Deprecated: Function eregi() is deprecated in /home/content/48/9096548/html/xfunda.com/plugins/content/emailcloak.php on line 99

Deprecated: Function eregi() is deprecated in /home/content/48/9096548/html/xfunda.com/plugins/content/emailcloak.php on line 112

Deprecated: Function eregi() is deprecated in /home/content/48/9096548/html/xfunda.com/plugins/content/emailcloak.php on line 112

PHP Database Integration With MySQL

E-mail Print PDF

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>

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 Database Integration With MySQL

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(0)
Comments (6)Add Comment
Nice Tutorial
written by Vijit Patil, March 30, 2008
Hi Chetan,
Thanks for Such Good Tutorial.
Its a good Resource for beginners.

Thanks
Vijit Patil smilies/smiley.gif
very very helpfull tutorial
written by very very helpfull tutorial, November 21, 2008

Very very helpfull tutorial
Excellent
written by GendeDios, January 24, 2009
*-*-*
Thanks for your existence!
*-*-*
Thanks
written by Gaxton, June 03, 2009
Hi,

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!
Treasure of mysql integration to php
written by mohanbabu, March 11, 2010
Explaination of integration of PHP with MySQL is worthful to beginners of php developer like me.

Thank U so much.
Very well presented!
written by justin nguyen, March 14, 2010
Very well presented! 10 stars if there are more stars!

Write comment
quote
bold
italicize
underline
strike
url
image
quote
quote
smile
wink
laugh
grin
angry
sad
shocked
cool
tongue
kiss
cry
smaller | bigger

busy
 

Articles


Warning: Parameter 1 to modMainMenuHelper::buildXML() expected to be a reference, value given in /home/content/48/9096548/html/xfunda.com/libraries/joomla/cache/handler/callback.php on line 100

Adobe Gallery


Warning: Parameter 1 to modMainMenuHelper::buildXML() expected to be a reference, value given in /home/content/48/9096548/html/xfunda.com/libraries/joomla/cache/handler/callback.php on line 100

Community


Warning: Parameter 1 to modMainMenuHelper::buildXML() expected to be a reference, value given in /home/content/48/9096548/html/xfunda.com/libraries/joomla/cache/handler/callback.php on line 100

FAQ's


Warning: Parameter 1 to modMainMenuHelper::buildXML() expected to be a reference, value given in /home/content/48/9096548/html/xfunda.com/libraries/joomla/cache/handler/callback.php on line 100

Resources


Warning: Parameter 1 to modMainMenuHelper::buildXML() expected to be a reference, value given in /home/content/48/9096548/html/xfunda.com/libraries/joomla/cache/handler/callback.php on line 100

Who's Online

We have 108 guests online

Subscribe Feeds

Enter your email address:

Sponsor Adds


 
 

Submit Article

We want to develop such a Community portal which will provide a good resource on latest hot technologies such as .NET, VB.NET, C#.NET, ADO.NET, ASP.NET, PHP, Flash, Photoshop. If you like to write for us, this guideline for you. please send your article, faqs on admin[at]xfunda.com

Adobe Gallery Photoshop Tutorials...

Photoshop Tutorial - Create Colorful Composition in Photoshop Photoshop Tutorial - Create Colorful Composition in Photoshop Photoshop Tutorial - Create Colorful Composition in Photoshop Category - Photoshop >> illustration and Art. ...
 
Photoshop Tutorial - Create a Nice Autumn Scenery in Photoshop Photoshop Tutorial - Create a Nice Autumn Scenery in Photoshop Photoshop Tutorial - Create a Nice Autumn Scenery in Photoshop Category - Photoshop >> illustration and Art. ...
 
Photoshop Tutorial - Create a Beautiful Lonely Fantasy Fairy Photoshop Tutorial - Create a Beautiful Lonely Fantasy Fairy Photoshop Tutorial - Create a Beautiful Lonely Fantasy Fairy Category - Photoshop >> illustration and Art. ...
 
Photoshop Tutorial - How To Create a Futuristic Sci-Fi Scene Photoshop Tutorial - How To Create a Futuristic Sci-Fi Scene Photoshop Tutorial - How To Create a Futuristic Sci-Fi Scene Category - Photoshop >> Photo Effects. ...
 
Photoshop Tutorial - Goddess cadeira photo effect in Photoshop Photoshop Tutorial - Goddess cadeira photo effect in Photoshop Photoshop Tutorial - Goddess cadeira photo effect in Photoshop Category - Photoshop >> Photo Effects. ...