How To Retrieve Data From Database in PHP Using MYSQL

Nowadays every websites is dynamic in nature. Websites has the capability to To retrieve data from database in PHP Using MYSQL from a remote server. It fetches the required data when needed from the server and serves the  data to the user.

Websites commonly use PHP to interact with the databases.

Pre-requisites:

1. XAMPP: local database

After installing XAMPP we need to create a MYSQL database

Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL.

PHP Functions:

The most frequently used function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Other ways of selecting the database is mysqli_fetch_assoc() selects the rows of the database as associative array.

CODE:

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$db = 'customers';
$conn = mysql_connect($dbhost, $dbuser, $dbpass, $db);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT `user_id`, `user_name`, `user_phone` FROM users';
$res = mysqli_query( $sql, $conn);
if(! $res) {
die('Could not get data: ' . mysql_error());
}
while($row = mysqli_fetch_assoc($res))
{
echo "USER ID :{$row['user_id']} ".

"USER NAME : {$row['user_name']} ".
"USER PHONE : {$row['user_phone']}".
"--------------------------------";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>


EXPLANATION:

In the above code we have created four variables which stores the

1. Hostname of the server.
2. Username.
3. Password
4. Name of the database.

Now we declare a connection variable ($conn) to connect to the database(‘customers’)

Mysqli_query is the function used to connect to the database and the connection is stored in the variable “conn”.

Next we need to check if the connection is correctly made to the database or not.

So if statment with a negated condition checks if the connection is not correctly made the print the appropriate message to the screen.

And if the connection is correctly made then we make a sql query to connect to the table we want to fetch data from, then with the use of mysqli_query() function we make the query to the database and select all the row of the table ‘user’. If everything id fine then we will use a while loop to loop through all the data fetched from the database.

Here $row stores the values fetched in an associative array and we can now print the values as $row[‘user_name’]  which a will print all the values of field user_name and so on.

RELEASING MEMORY:

Its a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysqli_close(). Below is the example to show how it has to be used.

NOTE:

In above example the constant MYSQLI_ASSOC is used so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.

You can also write complex sql statements.