Skip to main content

MYSQL DATABASE CONNECTION WITH PHP

Hello friends, today we will learn how to connect to MySQL database with PHP. This tutorial will require a MySQL database and a web development environment using mysql, apache and php and a simple text editor.
The tutorial takes you through establishing a MySQL connection using php on a web page, connecting to a MySQL table and retrieving the results and displaying them back on the web page.
Earlier versions of PHP used the MySQL extension.However, this extension was depreciated in 2012. Later can work with MySQL database using
  • MySQLi extension (the "i" stands for improved)
  • PDO (PHP Data Objects)
       The main difference between these two are if you want to switch your project to another database PDO makes it easy(since it can connect 12 different databases). You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.In this tutorial i will show you how to use MySQLi.
Steps we follow :
make the connection and select the database
perform the query on the table
print out the data
close the connection
Connecting to MYSQL :
      To make a connection you need your MySQL server address (if the database is on the same server you can use as localhost or 121.0.0.1),username,password,database name.Create a index.php file and open and paste the below code.
Example MySQLi
1
 2 
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<?php
$servername = 'localhost';
$username = 'root';
$password = 'root';
$db_name = 'test';
$db = mysqli_connect ( $servername, $username, $password, $db_name ) or die ( 'Error connecting to MySQL server.' );
?>
<html>
 <head>
 </head>
 <body>
 <h1>PHP connect to MySQL</h1>
</body>
</html>
open the file in the browser and you should see heading only.mysqli_connect creates a connection.The variable $db is created and assigned as the connection string, it will be used in future steps. If there is a failure then an error message will be displayed on the page. If it is successful you will see PHP connect to MySQL.
Performing a database query:
     For the query we are going to specify a read of all fields from a given table. The  $query variable selects all rows in the table. You just need to use your table name.
1
2
3
4
5 
<?php
 // Step2
 $query = "SELECT * FROM myTable";
 mysqli_query ( $db, $query ) or die ( 'Error querying database.' );
?>
Again the returned page in the browser should be blank and error free, if you do receive the error – ‘Error querying database..’ check the table name is correct.
Put the data on the page:
      We will store the result query we just made above,we just need to go through all the rows of that query which we need mysqli_fetch_array which stores the rows in an array, so now we are storing the $result in mysqli_fetch_array and passing that into a variable called $row.
The $row now can be output in a while loop, here the rows of data will be echoed and displayed on the page to when there is no longer any rows of data left, my example uses 3 fields in the table first_name, last_name, email.
1 
2
3
4
5
6
7
8
9
<?php
// Step3
 $result = mysqli_query ( $db, $query );
 if (mysqli_num_rows ( $result ) > 0) {
  while ( $row = mysqli_fetch_assoc ( $result ) ) {
    echo $row['first_name'] . ' ' .$row['last_name']. ' ' .$row['email'];
  }
 }
?>
Here you should see the all data as output from your table.
Closing off the connection:
      Closing the connection will require another set off opening and closing php tags after the closing html tag. It is good practice to close the database connection when the querying is done.
1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 
32
<?php
$servername = 'localhost';
$username = 'root';
$password = 'root';
$db_name = 'test';
$db = mysqli_connect ( $servername, $username, $password, $db_name ) or die ( 'Error connecting to MySQL server.' );
?>

<html>
<head>
</head>
<body>
 <h1>PHP connect to MySQL</h1>
 
 <?php
 // Step2
 $query = "SELECT * FROM myTable";
 mysqli_query ( $db, $query ) or die ( 'Error querying database.' );
 // Step3
 $result = mysqli_query ( $db, $query );
 if (mysqli_num_rows ( $result ) > 0) {
  while ( $row = mysqli_fetch_assoc ( $result ) ) {
    echo $row['first_name'] . ' ' .$row['last_name']. ' ' .$row['email'];
  }
 }
 
 // Step 4
 mysqli_close ( $db );
 
 ?>
</body>
</html>
Database connections should always be closed off. You do not need to keep the connection variable $db after the initial connection but is considered best practice.
     Hope you understand the tutorial  , in the next tutorial we will see how to connect database with PHP using PDO. Please share the post with your friends in Twitter and Facebook.Thank you.Have a sweet day :)
Labels:

Comments

Post a Comment

Popular posts from this blog

Reading and Generating QR codes in Python using QRtools

What are QR codes? A Quick Response (QR) code is a 2 dimensional barcode that is used due to its fast readability and relatively large storage capacity.  2 dimensional barcodes are similar to one dimensional barcodes, but can store more information per unit area. Installation and Dependencies Linux:   qrtools can be installed on debian based linux systems with the following commands $sudo apt-get update $sudo apt-get install python-qrtools The following dependencies must be installed as well [sudo] pip install pypng [sudo] pip install zbar [sudo] pip install pillow Windows:   qrtools can be installed on windows by downloading the file from here(https://pypi.python.org/pypi/qrtools/0.0.1). On downloading and extraction, run the following command from inside the folder python setup.py install Generating a qrCode: qrtools contains a class QR (can be viewed in the source code), for which we must initially create an object. The object takes the ...
Post a Comment
Read more

MYSQL CONNECTION USING PDO WITH PHP

Hello guys, In the previous tutorial we have seen how to connect to MySQL database with PHP using MySQLi. Today we will learn how to connect with PDO. As a PHP programmer we have learned how to access database by using either MySQL and MySQLi. As of PHP 5.1, there's a better way. PHP Data Objects(PDO) provide methods for prepared statements and working with objects that will make you far more productive! PDO Introduction :        PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. Database Support:       The extension can support any database that a PDO driver has been written for. The following drivers currently implement the PDO interface: PDO_CUBRID Cubrid PDO_DBLIB FreeTDS / Microsoft SQL Server / Sybase PDO_FIREBIRD Firebird PDO_I...
4 comments
Read more