Monday, May 7, 2012

PHP with MySQL: How to connect to MySQL database using PHP?

Connection to MySQL database using PHP is very simple and includes only few steps. First of all we establish a connection to database using username and password. Then we select the database that is already created using MySQL console or PhpMyAdmin. Then some operations are performed on data and finally we close the connection.
For the connection to MySQL, we need a database. So lets create a database with name testDB having one table user with three fields id, username and password  using MySQL console. Enter the following command in MySQL console and hit Enter. (MySQL console is generally located in C:\wamp\bin\mysql\mysql5.1.36\bin in WAMP server, the mysql version may be different in your case)
mysql> CREATE DATABASE testDB;
It creates a database with name testDB. In order to perform the operations on a database we must use the database. So to use the database testDB, insert the following command and hit enter.
mysql> USE testDB;
Now we create a table with fields id, username and password.
mysql> CREATE TABLE user (
    ->  id int(11) NOT NULL auto_increment,
    -> username varchar(30) NOT NULL,
    -> password VARCHAR(30) NOT NULL,
    -> PRIMARY KEY (id)
    -> );

We have successfully created the database. Our next job is to access this data base using PHP i.e MySQL with PHP. As I already told at the beginning of this tutorial that there are few steps to connect MySQL using PHP. I illustrates every steps with necessary code and explanation.
Step 1: Connection to MySQL database
Before selecting a database and before performing any operations on the database, we first connect to the database using a function mysql_connect() . This function opens a connection to MySQL server. The general syntax of mysql_connect() is
mysql_connect([string hostname[:port][:/path/to/socket][,string username [,string password [, bool new [, int flags ]]]]])
Except hostname all parameters are optional. Hostname is “localhost” in our case because we run our PHP code in local machine. The default username is “root” and there is no password initially. But if you have changed the configuration in config.inc.php, then use username and password as you set in config.inc.php. A piece of code is given below:
<?php    $connect  = mysql_connect("localhost", "root", "");
    if(!$connect){
        die("Cannot connect to MySQL server". mysql_error());
    }else{
        echo "Sucessfully conencted to MySQL server";
    } ?>


Step 2 : Selection of database

The next step is to select a database. We have already created a database ‘testDB’. Now we select this database using PHP. The function use in this case is mysql_select_db(). This function selects a MySQL database. The general syntax is

mysql_select_db(string database_name [, string link_identifier]);

The piece of code that selects the database testDB is
<?php    $db = mysql_select_db("testDb", $connect);
    if(!$db){
        die("Cannot select a dababase". mysql_error());
    }else{
        echo "Database is sucessfully selected";
    }?> 


Step 3: Operations on data

We can perform various operations on the database. Some operations are perform database query, select data, update data etc. Here is a small piece of PHP code that performs a database query to select the data from database table ‘user’ and echo this result to the web browser.
<?php 
$result = mysql_query("SELECT * FROM user", $connect);
if(!$result){
    die ("database query failed". mysql_error());
} 


while($row = mysql_fetch_array($result)){
    echo $row[1]." ".$row[2]. "</br>";
} 
?>


Step 4: Close the Connection

Finally we close the connection to the MySQL server. mysql_close() is used to close the connection to database using PHP. The piece of PHP code is
<?php
    mysql_close($connect);?>

1 comment:

  1. This technical post helps me to improve my skills set, thanks for this wonder article I expect your upcoming blog, so keep sharing..
    Regards,
    PHP Training Center in Chennai|php training in velachery

    ReplyDelete