CentOS PHP MariaDB Example for Testing

This article is a tutorial on configuring and testing MariaDB on a GNU/Linux system. Here a CentOS 7 web server is used running on a virtual machine (VM). The steps can shown here can be applied to other Linux configurations, e.g. accessing a virtual private server (VPS) or dedicated server via SSH. This article assumes that CentOS is installed and running on a machine (VM, VPS or hardware) and the shell (command line) is accessible (either locally or via SSH).

Check PHP is Running

If it is needed to check that PHP is working use the phpinfo() function in a PHP script. Here the default Apache public directory is being used. Once logged in to CentOS change to the directory (all commands here show the shell prompt):

# cd /var/www/html

Use echo to create the script to test for PHP correctly configured:

# echo '<?php phpinfo(); ?>' > php-test.php

Access the script via a browser using your domain address or localhost for a local test machine or VM. E.g. for this article a VM was being used and the script was accessed via localhost:8000/php-test.php:

If PHP is not installed see Install PHP on CentOS VPS for Apache Hosted Web Sites.

Install and Configure MariaDB (Equivalent to MySQL)

Use yum to install MariaDB on CentOS, confirming the installation with y:

# yum install mariadb-server

Configure MariaDB to run at start up on CentOS using systemctl:

# systemctl enable mariadb.service

Finally start the MariaDB service, again using systemctl:

# systemctl start mariadb.service

Next secure the MariaDB service by setting a root password and removing test items with this command:

# mysql_secure_installation

Press enter to start with as there is no root password on the intial install. The answer Y to all the questions, i.e.: Set root password? Y. Remove anonymous users? Y. Disallow root login remotely? Y. Remove test database and access to it? Y. Reload privilege tables now? Y.

Now a database is created, use mysql to log into the MariaDB service then use CREATE DATABASE followed by a name:

# mysql -u root -p
MariaDB [root]> CREATE DATABASE somedata;

Create a table in the database (called datahere in this example with name_id and namedata rows) by entering these commands:

MariaDB [root]> USE somedata;
MariaDB [somedata]> CREATE TABLE datahere ( name_id int(5) NOT NULL AUTO_INCREMENT, namedata varchar(50) DEFAULT NULL, PRIMARY KEY(name_id) );

Add a little data with these commands:

MariaDB [somedata]> INSERT INTO datahere ( namedata ) VALUES ( "Tom" );
MariaDB [somedata]> INSERT INTO datahere ( namedata ) VALUES ( "Jerry" );

Display the data with this command:

MariaDB [somedata]> SELECT * FROM datahere;
+---------+----------+
| name_id | namedata |
+---------+----------+
|       1 | Tom      |
|       2 | Jerry    |
+---------+----------+

Set up a user name for the created database and then logout of the MariaDB service:

MariaDB [somedata]> GRANT ALL ON somedata.* TO 'ourusername' IDENTIFIED BY 'asecurepassword';
quit

Next add the PHP module for MariaDB (which is the MySQL module) using yum:

# yum install php-mysql

Restart the Apache web server with the apachectl command:

# apachectl restart

PHP Page to Display the Data Using mysqli

The mysqli extension is the new way to access MySQL, and hence MariaDB databases, from PHP. The i in mysqli is for improved. Use the vi or nano text editor to create file called data-test.php:

# nano data-test.php

Or (in vi use i to enter insert mode):

# vi data-test.php

Enter the following PHP code:

<?php
    $connect = new mysqli( "localhost", "ourusername", "asecurepassword", "somedata" );
    if( $connect->connect_errno ) {
       exit( "Unable to connect to MariaDB:".$connect->connect_error );
    }
    $result=$connect->query( "SELECT * from datahere" );
    if( !$result ) {
        exit( "Data query error:".$connect->error );
    }
    while( $namerow = $result->fetch_assoc() ) {
        echo "<p>".$namerow[ "name_id" ]."=".$namerow[ "namedata" ]."</p>";
    }
    $result->free();
    $connect->close();
?>

For nano use Ctrl-O then Ctrl-X to save the file and exit. For vi use Escape to exit insert mode and enter the command :wq to write the file and quit. Run the script (e.g. for this article the script on the VM web server was accessed using the address localhost/data-test.php):

PHP Page to Display the Data Using the Deprecated mysql Methods

For comparison purposes here is the way it would have been done prior to mysqli (this way can still be used):

<?php
    $connect=mysql_connect( "localhost", "ourusername", "asecurepassword" )
       or exit( "Unable to connect to MariaDB" );
    mysql_select_db( "somedata" ) or exit( "Could not access the database." );
    $result=mysql_query( "SELECT * from datahere" );
    if( !$result ) {
        exit( "Data query error:".mysql_error() );
    }
    while( $namerow = mysql_fetch_assoc( $result ) ) {
        echo "<p>".$namerow[ "name_id" ]."=".$namerow[ "namedata" ]."</p>";
    }
    mysql_free_result($result);
    mysql_close($connect);
?>

Also See

To prepare a CentOS server for web hosting see Setup CentOS Server for Web Hosting Using DVD ISO. However for a minimal (non-DVD) CentOS ISO see Install Apache on CentOS to Configure VPS Test System and Install PHP on CentOS VPS for Apache Hosted Web Sites.