Introduction
As mention in the previously tutorial, One of the most relevant subjects at the moment of programing on Php, is working with databases and tables. The world's most popular open source database management system used is MySQL. So, we are going to learn how to connect and read MySQL table with php.
What Will I Learn?
In this tutorial, we will learn how to perform a query of MySQL table with php code. We’ll use mysql statement and mysqli class.
- You will learn to use mysql statement to read MySQL table
- You will learn to use mysqli class to read MySQL table
- You will learn to set a user and password to MySQL database
Requirements
- You must have installed XAMPP
- You need a text editor
- Read Creating MySQL table with XAMPP before continue with with tutorial.
Difficulty
- Basic
Tutorial Contents
Before start programming, we have to define a user and password for our MySQL database for security reason. Let's use a SQL statement textarea in phpmyadmin to create user and password (user_test, 123456789) for our MySQL database test1.
Now let's go to the Windows Explorer and select thehtdocs folder, insideXAMPP folder
Create a new folder, test. Inside this folder we will place all our php code.
Now, we will need a text editor. There are many of them, I prefer to use Notepad++. The one with the tiny chameleon…
Our first code will be the config.php, as we see in the image below, we must save the code with that name in thetest folder, we have created within htdocs.
It is important to place the definition of these variables _DB_SERVER_, _DB_NAME_, _DB_USER_ and _DB_PASSWD_ in a single file (config.php), and then call it wherever we want with a require statement. In this way, if we change the server, database, user or password values, we will not have the task of changing these values everywhere but here.
Let's see then. Example No. 1
<?php
require('config.php');
//
$con=mysql_connect(_DB_SERVER_,_DB_USER_,_DB_PASSWD_);
if(!$con){
die('Unable to connect to server'.mysql_error());
}
$dbselect=mysql_select_db(_DB_NAME_,$con);
if(!$dbselect){
die('Unable to connect to database'.mysql_error());
}
$sql= "SELECT * FROM payments";
$result = mysql_query($sql,$con);
while($fila=mysql_fetch_object($result)){
echo $fila->employee_id;
echo " - ";
echo $fila->name;
echo " - ";
echo $fila->salary;
echo " - ";
echo $fila->payday;
echo "\n
";
}
mysql_free_result($result);
mysql_close($con);
?>
We use the require command to load the config.php. Unlike the include command, require stops the process if it does not find the config.php.
There are 2 types of commands to access a Mysql database, they are: mysql and mysqli.
We will start using the mysql command.
For this, we will use the command mysql_connect, to connect to the server and mysql_select_db for the database. If everything went correctly, the connection that has been created will be saved in the $con variable.
We define a variable $sql, to set our SQL query (the same as the phpmyadmin used). We load our query with the mysql_query command, inside the$result variable. Then, through the mysql_fetch_object command, we can extract each one of the records saved in $result. I like to use the mysql_fetch_object command, since it allows to work with objects that point to the fields of table. This way if we need to read the content of the field, we simply use $row->fieldname. Finally, the last commands will allow us to free the memory.
Remember, in order to run the php code you must open the browser and place the url address shown in the image below. The result should look like this:
As you can see, the output is not formatted, since to improve it, it is necessary to use html, css and other sorts of codes.
In the following code, we will see that the use of mysqli is shorter and more secure. This command is an improved Php extension (the "i" means improved). It is completely object-oriented, improves transactions and is more secure against SQL injection attacks.
Let’s see example 3:
<?php
require('config.php');
//
$mysqli = new mysqli(_DB_SERVER_,_DB_USER_,_DB_PASSWD_,_DB_NAME_);
/* Check database connection */
if (mysqli_connect_errno())
die("Error: " . mysqli_connect_error());
// We create our query
$query = "SELECT * FROM payments";
if ($result = $mysqli->query($query)) {
while ($row = $result->fetch_object()) {
echo $row->employee_id;
echo " - ";
echo $row->name;
echo " - ";
echo $row->salary;
echo " - ";
echo $row->payday;
echo "\n
";
}
$result->close();
}
$mysqli->close();
?>
The output of the code is the same as in example 1:
Let’s see how to format the outputs of both the data retrieve from MySQL and those that are displayed by HTML.
As I mentioned before, we will make use of html labels to format the outputs. I’ll only use html common labels and insert php codes inside of the same. It can be shown on example 2:
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Example 2</title>
</head><body>
<?php
require('config.php');
//
$con=mysql_connect(_DB_SERVER_,_DB_USER_,_DB_PASSWD_);
//
if(!$con){
die('Unable to connect to server'.mysql_error());
}
//
$dbselect=mysql_select_db(_DB_NAME_,$con);
//
if(!$dbselect){
die('Unable to connect to database'.mysql_error());
}
// We create our query
$sql= "SELECT * FROM payments";
//
$result = mysql_query($sql,$con);
?>
<table align="center" cellspacing=4 cellpadding=10 border=1>
<tr>
<th>Employee Id</th>
<th>Name</th>
<th>Salary</th>
<th>Pay Date</th>
</tr>
<?php
while($row=mysql_fetch_object($result)){
$payday = new DateTime($row->payday); // We use DateTime class
// to format date
// We'll use number_format function to format numeric values
?>
<tr>
<td><?=$row->employee_id?></td>
<td><?=$row->name?></td>
<td align="center"><?=number_format($row->salary,2)?></td>
<td><?=$payday->format('m/d/Y')?></td>
</tr>
<?php
}
mysql_free_result($result);
mysql_close($con);
?>
</table></body></html>
Like we saw on example 1, the date’s fields always come with the “year-month-day” format. However, we can show the date with any format we like making use of the class DateTime.In this way, using the format method, we can show the date in the “month/day/year” format (‘m/d/Y’).
To format numeric values, we will use the php function called number_format. The first parameter is to designate the variable which contains the value we want to format, the second, is the amount of fraction number we want to show. In such a way that the output is as shown in the following image:
The next code, we’ll see as same as in example 2 but with mysqli statement.
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Example 4</title>
</head><body>
<?php
require('config.php');
//
$mysqli = new mysqli(_DB_SERVER_,_DB_USER_,_DB_PASSWD_,_DB_NAME_);
/* Check database connection */
if (mysqli_connect_errno())
die("Error: " . mysqli_connect_error());
// We create our query
$query = "SELECT * FROM payments";
?>
<table align="center" cellspacing=4 cellpadding=10 border=1>
<tr>
<th>Employee Id</th>
<th>Name</th>
<th>Salary</th>
<th>Pay Date</th>
</tr>
<?php
if ($result = $mysqli->query($query)) {
while ($row = $result->fetch_object()) {
$payday = new DateTime($row->payday); // We use DateTime class
// to format date
// We'll use number_format function to format numeric values
?>
<tr>
<td><?=$row->employee_id?></td>
<td><?=$row->name?></td>
<td align="center"><?=number_format($row->salary,2)?></td>
<td><?=$payday->format('d/m/Y')?></td>
</tr>
<?php
}
$result->close();
}
$mysqli->close();
?>
</table></body></html>
The outcome
Before ending this tutorial, I leave this quote to establish the difference between mysqli and mysql:
MySQLi is the OOP version of MySQL extension. In the end, MySQLi and MySQL accomplish the same thing: they are extension for interacting with MySQL from PHP. A lot of people still use the original MySQL extension instead of the new MySQLi extension because MySQLi requires MySQL 4.1.13+ and PHP 5.0.7+ .
MySQLi supports some things that the old MySQL extension doesn't. Things like prepared statements, multiple statements, and transactions on top of my head
Quote
I hope you found it useful. Don’t forget to share it
Until next time my fellow coders.
Curriculum
Posted on Utopian.io - Rewarding Open Source Contributors