How to Connect to Mysql Database in Php

sql-connection

In PHP programming language there are 3 different ways to connect to a Mysql database. We are going to show these all three methods here which will explain How to Connect to Mysql Database in Php. Here are the three methods:

  • Mysql Connect Method
  • Mysqli Connect Method
  • PDO Connect Method

Fir of all we are going to use Mysql connect method to connect to a Mysql database. First of all you have to create a database by using a command line interface or by using PHPMyAdmin. Name the database as “my_database”.

Now create a table by using command line interface or phpMyAdmin and name this table “my_table”. Table has id, name, class and age as the table fields. Create a PHP file with the name of “my_test.php”.

Now all the coding we will make in “my_test.php” file. First of all we are going to write a sample code for Mysql connection method. Here is the code sample:

<?php

$server_name = ‘localhost’;//write the server name
$user_name = "root";//write the database user name
$password = ""; //write the database user password

$db_name = “my_database”;//the name of database to be selected

$conn = mysql_connect($server_name, $user_name, $password) ;//give all the variable values in the given order

mysql_select_db($db_name, $conn) or die(mysql_error());//selecting the database with this connection

$result = mysql_query(“SELECT * FROM my_table”, $conn);//select all records from my_table

If(!$result){

die(mysql_error());

}else{

While($row = mysql_fetch_array($result)){//this will loop through all the records in the table

echo $row[‘name’].”<br>”;//printing “name” field of the “my_table”

}

}

?>

In the above code you can see that we are using Mysql Connect method to connect to a Mysql database. Now we are going to elaborate the second method of connecting to Mysql database i.e. Mysqli connect method.

It is quite similar to the above explained method. The variables are the same and the methods are a bit different. It has two types of methods to one called the object-oriented method and one called the Procedural method. See the code for Mysqli connect (Procedural method) method:

 

<?php

$server_name = ‘localhost’;//write the server name
$user_name = "root";//write the database user name
$password = ""; //write the database user password

$db_name = “my_database”;//the name of database to be selected

// Creating the connection to mysql database

$conn = mysqli_connect($server_name, $user_name, $password, $db_name);

// Now checking the connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

 

$query = "SELECT id, name, class FROM my_table";

$result = mysqli_query($conn, $query);

$row1=mysqli_fetch_row($result);

 

if(count($row1) > 0){

// output data of each row

while($row=mysqli_fetch_array($result)) {

echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";

}

} else {

echo "0 results";

}

mysqli_close($conn);//closing the connection for the file

 

?>

Now let us see the Objected-oriented method for Mysqli.

<?php

$server_name = ‘localhost’;//write the server name
$user_name = "root";//write the database user name
$password = ""; //write the database user password

$db_name = “my_database”;//the name of database to be selected

// Creating the connection to mysql database

$conn = new mysqli ($server_name, $user_name, $password, $db_name);

// Now checking the connection

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$query = "SELECT id, name, class FROM my_table";

$result = $conn->query($query);

 

if ($result->num_rows > 0) {   // output data of each row

while($row = $result->fetch_assoc()) {

echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";

}

} else {

echo "No results";

}

$conn->close(); //closing the connection for the file

 

?>

In the above method we have used the object oriented technique for the connection. As you should know that the Mysqli method works with only Mysql database. It can’t be used for any other database like Oracle etc.

On the other hand the PDO method can be used to connect to about 12 different databases. It is easy to shift from one database to the other when you are using PDO method.

Both the connection methods support Prepared Statements which are very important to protect applications from SQL injection. This is very important for your application security.

Now let us see the last method the PDO method to connect to Mysql database with PHP.

<?php

echo “<table>”;

$server_name = ‘localhost’;//write the server name
$user_name = "root";//write the database user name
$password = ""; //write the database user password

$db_name = “my_database”;//the name of database to be selected

try {

$conn = new PDO("mysql:host=$server_name;db_name=$dbname", $user_name, $password);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("SELECT id, name, class FROM my_table");

$stmt->execute();

 

// set the resulting array to associative

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {

echo $v;

}

 

 

}

catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null;

echo “</table>”;

 

?>

Comments