PHP and MySQL/MariaDB tutorial on Ubuntu

PHP and MySQL are the two most important tools to learn today for web programming. PHP is a server side programming language which is used to process any request sent by the clients. A web server is required to run any PHP code. Apache server is used in this tutorial as web server. To store the data of the site in structured format you will need a database server. Data can be easily inserted, deleted, updated and searched from the database server using SQL queries. PHP can connect with many types of database servers. The most widely used database server with PHP is MySQL or MariaDB. MySQL server is developed by Oracle and MariaDB server is developed by MySQL developer, Monty Widenius. MariaDB server is now more popular than MySQL server because if its open source community. In this tutorial you will learn how you can use PHP with MariaDB for developing web projects.

Install Apache, PHP and MySQL

Run the command to update the system before starting the installation process.

$ sudo apt-get update

Before writing PHP script, you have to install Apache, PHP and MariaDB on Ubuntu. By default, apache server is installed on Ubuntu. So, you don’t need to install it. Check the version of Apache server by the following command.

$ apache -v

If you didn’t install PHP before then run the command to install PHP.

$ sudo apt–get install php

After installing PHP, check the version of the PHP.

$ php –v

Now, run the command to install MariaDB server and client if it is not installed before.

$ sudo apt-get install mariadb-server mariadb-client

Type ‘y’ and press enter to complete the installation process.

Run the following command to start MariaDB server.

$ sudo systemctl start mariadb

Check if the server is working properly or not. The output of the following command will show the detailed status of the server. The output indicates that MariaDB 10.1.30 verion is running.

$ sudo systemctl status mariadb

After installing the above three necessary packages successfully, you can start the next part of the tutorial.

Create database

You will need a database with a minimum one table to check how PHP works with MySQL/MariaDB server. Run mysql client to create a new database named company. If you didn’t set any password for root user then you have to omit –p option.

$ mysql –u root –p

Run the query to create company database.

> create database company;

Create a table

Select the database in which you want to create the table.

> use company;

Run the query to create a table named employees.

> CREATE TABLE employees
( id INT(11),
name VARCHAR(30) NOT NULL,
address TEXT NOT NULL,
designation VARCHAR(50) NOT NULL,
phone VARCHAR(50) NOT NULL,
email VARCHAR(50) NULL,
salary INT(11),
PRIMARY KEY (id)
);

Insert some records into employees table using insert query.

> INSERT INTO employees values(1001, ‘Jeffrey B Peters’, ‘4787 Blackwell Street,
Dry Creek, Alaska’,
‘Assistant Manager’, ‘907-323-6503’, ‘[email protected]’, 1000);
INSERT INTO employees values(1002 ,‘Indiana C Halley’, ’42 Woerdens Road, New South Wales’,
‘Sales Executive’, ’02 4914 7750′, ‘[email protected]’, 1500);

Create database connection using PHP

Create a new folder named phpcode in /var/www/html to store php files.

Open any editor and add the following script to make a connection with the previously created database company. Here, test editor is used to write the code. Assigning three values are mandatory to make the database connection, the first value is the web server name and localhost is used for apache server. The second value and third value are username and password. In the script, root is used as username and root password is used for making the connection. Database name company is used as fourth value which is optional. You can set the database name after creating the connection using another method. After running the script, if the connection can’t be established then an error message will print, otherwise it will print the success message. Save the file in the location which is mentioned above. Here, the filename is dbconnection.php.

<?php

$server="localhost";
$username="root";
$password="mynewpassword";
$database = "company";

/* Create database connection with correct username and password/*
$connect=new mysqli($servername,$username,$password,$database);

/* Check the connection is created properly or not */
if($connect->connect_error)
echo "Connection error:" .$connect->connect_error;
else
echo "Connection is created successfully"; ?>

Open a browser and browse the location using localhost to run the script.

http://localhost/phpcode/dbconnection.php

Now, make some modification in the script to check the connection. For example, change the database value by company2 which is not exist.

Again, run the script and this time the following error will appear.

Read data from database using PHP

Create another file name readdata.php in the same location with the following code. dbconnection.php file is included at the beginning of the script to make connection with the database. Next, execute the select query to retrieve all records from employees table. If no records exist then the value of property, $result->num_rows will be zero and it will print “No record found”. If records exist then a while loop is used to read the records. There are various methods available to read records from the result set. Here, fetch_assoc() method is used to read the records. It reads each record as associative array.

<?php

//Include the connection script
Include ("dbconnection.php");

//Execute the query to read all records from emplyees table

$result = $connect->query("select * from employees");

if ($result->num_rows > 0) {
// Read the records
while($row = $result->fetch_assoc()) {
echo " Name: " . $row["name"]. " Designation: ". $row["designation"].
" Salary ". $row["salary"]. " ";
}
}
else
echo "No record found";

$conn->close();

?>