How to Connect MySQL Database using PHP?
Following example shows you how to connect a MySQL database in PHP.
Now most of web application
builders use MySQL as there database server and use PHP to establish connection to the database server. There are two ways to make connections to a MySQL database server using PHP.
In persistent connection, the link(connection) to the database server will be closed as soon as end of the execution of the script. But you can closed the connection explicitly calling mysql_close() function. In non-persistent connection, the connection to the database server do not close when the end of the execution of the script. When you call a persistent connection PHP first seeks if there is a already open connection to the database. If exists use that connection instead of creating a new connection. If there is no any previous connection to the database, It creates a new connection to the database server.
builders use MySQL as there database server and use PHP to establish connection to the database server. There are two ways to make connections to a MySQL database server using PHP.
- using mysql_pconnect function - Establishes a persistent connection to a MySQL database server
- using mysql_connect function - Opens or reuses a connection to a MySQL database server
What is the difference between persistent connection and non-persistent connection?
In persistent connection, the link(connection) to the database server will be closed as soon as end of the execution of the script. But you can closed the connection explicitly calling mysql_close() function. In non-persistent connection, the connection to the database server do not close when the end of the execution of the script. When you call a persistent connection PHP first seeks if there is a already open connection to the database. If exists use that connection instead of creating a new connection. If there is no any previous connection to the database, It creates a new connection to the database server.
Steps of connecting a MySQL database.
- Establish a connection to the database server
- Select the database that you want to connect
- Execute your SQL query
- Retrieve data from the database
- Close the connection
Functions that are needed to connect a database in PHP
| PHP function | Purpose |
|---|---|
| mysql_connect($host,$user_name,$password) | Opens a connection to the MySQL database server. |
| mysql_select_db($database,$connection) | Select a MySQL database in the connected MySQL database server. |
| mysql_close($connection) | Close existing connection to the MySQL database server. |
| mysql_query($sql_query,$connection) | Send a MySQL query to the selected database. |
| mysql_fetch_array($result) | Fetch a result row as an array. |
| mysql_num_rows($result) | Get number of returned rows in result. (SELECT or SHOW statements) |
1. Example code for connect a mysql database in PHP.
PHP Code<?php
$host = "localhost";
$user_name = "root";
$password = "1234";
$database = "user_info";
$user_name = "root";
$password = "1234";
$database = "user_info";
$connection = mysql_connect($host,$user_name,$password);
if($connection!=NULL){
$db=mysql_select_db($database,$connection);
$db=mysql_select_db($database,$connection);
if($db!=NULL){
print("Ok connection established");
mysql_close($connection);
}else{
print("Database does not exists");
mysql_close($connection);
}
}else{
print("Error: unable to connect to the database");
}
?>
print("Ok connection established");
mysql_close($connection);
}else{
print("Database does not exists");
mysql_close($connection);
}
}else{
print("Error: unable to connect to the database");
}
?>
If host name, user name and password you are provided is valid, you can see the message "Connection established" otherwise it shows "unable to connect". Even above details are correct, but there is no such database, it shows "database does not exists".
2. Example code for retrieve table data in a mysql database using PHP.
PHP Code<?php
$host = "localhost";
$user_name = "root";
$password = "1234";
$database = "user_info";
$user_name = "root";
$password = "1234";
$database = "user_info";
$connection = mysql_connect($host,$user_name,$password);
if($connection!=NULL){
$db=mysql_select_db($database,$connection);
$db=mysql_select_db($database,$connection);
if($db!=NULL){
$sql="SELECT user_id,first_name,last_name,age FROM user_detail";
$result=mysql_query($sql,$connection);
print("<table border='1'>");
print("<th>User ID</th><th>First Name</th><th>Last Name</th><th>Age</th>");
while($row=mysql_fetch_array($result)){
print("<tr><td>".$row['user_id']."</td>"."<td>".$row['first_name']."</td>"."<td>".$row['last_name']."</td>"."<td>".$row['age']."</td></tr>");
}
print("</table>");
mysql_close($connection);
}else{
print("Database does not exists");
mysql_close($connection);
}
}else{
print("Error: unable to connect to the database");
}
$sql="SELECT user_id,first_name,last_name,age FROM user_detail";
$result=mysql_query($sql,$connection);
print("<table border='1'>");
print("<th>User ID</th><th>First Name</th><th>Last Name</th><th>Age</th>");
while($row=mysql_fetch_array($result)){
print("<tr><td>".$row['user_id']."</td>"."<td>".$row['first_name']."</td>"."<td>".$row['last_name']."</td>"."<td>".$row['age']."</td></tr>");
}
print("</table>");
mysql_close($connection);
}else{
print("Database does not exists");
mysql_close($connection);
}
}else{
print("Error: unable to connect to the database");
}
?>
mysql_query() is use to send a SQL query to selected database. After executing the sql query it returns the result set. mysql_fetch_array() function returns an array that associated to the fetched row. looping through a while loop you can retrieve all returned rows.
1 comments:
Thanks
Post a Comment