Code Done Right!

PHP and SQL

PHP and SQL are vital to any modern website. They are not services themselves, but many services rely on them.

PHP is a general-purpose programming language that allows for creation of dynamic websites. A lot of pages that you are using implement PHP, even this one – WordPress relies heavily on PHP. There is more PHP in the internet than you realize.

SQL on the other hand is a programming language that relies on databases. Think of databases as a gigantic Excel sheet. All posts, pages and databases on this page make use of SQL. MariaDB is how we are going to implement that on our server.

You also need to know that there is a necessity for a parser. A package that will allow PHP and SQL to talk to each other. Worry not, I will tell you exactly what you need to do in order for all this to work.

PHP installation

This one will be quite easy. In order to install PHP type the following code

sudo apt install php

More or less that is all you need for now. Apt will install the latest available PHP package with all the basic modules that you need.

After you have installed PHP, restart Apache with the following command

sudo service apache2 restart

Find out which PHP version is installed

In time, you will notice that you need additional PHP modules if you want your services to work properly. A newly installed forum or cloud solution is going to produce an error upon initial run stating that it requires certain PHP module to run.

First of all you need to find out which version of PHP you are running. Useful tricks page contains an instruction on how to check which packages are installed on your system.

In short, run the following code

php -v

or, if you want to see all packages with php in its name

apt list --installed | grep php

The first command will show you the PHP version that you have on your system. Short and to the point, but take a look at the second approach.

The second line will list all packages with PHP in their name that are installed on your system. Look for phpX.X string which will tell you what PHP version you have on your server. Take a look at the screenshot below. It shows that my server has php7.3 installed

php version
Proof of php7.3 installed

Installation of additional PHP modules

Now we know what version we are running. Now let us assume a service told you that it needs CURL PHP module. The easiest way to find out which package you need to install is running the following command

axi-cache search php curl

Result will show you all packages with PHP CURL in their name. I know it is not that of an exact instruction but install the most logical choice. In this case it would be php-curl. Remember you can uninstall said package if it turns out this is not the one you were looking for, and install a different one.

Now why we needed to find the version of PHP? Some packages are version specific and you need to install a compatible one. Like in the case of a zip module for PHP, which is named php7.3-zip

In short, if the package has a version number, grab one for your version of PHP, if there is no package that contains a version number, or a version number for your PHP, try this one.

When installing new services without their perquisites they will tell you that they require new modules, packages etc. You can use the above method to find out what are the names of said packages. This applies not only to PHP. Check out Useful tricks page to know more about searching for packages.

After you have installed required modules restart Apache with the following command

sudo service apache2 restart

SQL with MariaDB

SQL is an enormous topic, too big to dive into in such a small how-to guide. Right now the only thing you need to know is how to install SQL server, how to create database user and how to create a database.

Rule of thumb is – create a separate user and database for every service that you use. If you have WordPress and Nextcloud on your server, make sure that they are using separate users and databases. In short, one service – one user, one database.

Installing SQL server

As usual, installation is not that hard. We will utilize MariaDB server, an open source successor of MySQL.

Run the following code

sudo apt install mariadb-server mariadb-common php-mysql

CAUTION note that you need to install php-mysql package in addition to MariaDB server. This is the parser that I mentioned in the introduction. It will allow PHP and MySQL to talk to each other!

Initial MySQL secure installation

Before we start using MySQL we need to secure it properly. Set up a root password for MySQL. Note that this is MySQL root, and not root system user.

Run the following code

sudo mysql_secure_installation

Initially MySQL root user has no password. Just press Enter and when asked, set up a new password. Answer with y to all other prompts. Basically remove everything, disallow remote root login and reload privileges. You will be left with a clean install secured with a new root password.

Logging into MySQL

In order to create users and databases you need to log in to MySQL with MySQL root user. To do so, run the following code

sudo mysql -uroot -p

Now, type in the password that you have set for the root user. Note that your terminal changed. It is no longer the default Bash shell. You are logged into MariaDB server.

In order to log out of MySQL and go back to your terminal just press

CTRL+D

Creating SQL data for our services

As mentioned before, each service should have the following, separate, set of data to use

  • User
  • Database

This is fairly simple and after logging in just put the following in MySQL terminal

CREATE DATABASE $DATABASE_NAME;
GRANT ALL PRIVILEGES ON $DATABASE_NAME.* TO '$USER'@'localhost' IDENTIFIED BY '$USER_PASSWORD';
FLUSH PRIVILEGES;

CAUTION do not remove semicolons (;) from the end of lines. They are required syntax elements that tell SQL that an instruction has ended.

Substitute

  • $DATABASE_NAME – with database name for your service (e.g. wordpress_database)
  • $USER – with a new username that will be used for handling this database
  • $USER_PASSWORD – with a password for our new user

CAUTION GRANT ALL PRIVILEGES… line will create a new user if you are specifying a non-existent user.

Now you are basically done. You have set up a database and a user that can be utilized with one of our services like WordPress or Nextcloud. Just remember about setting separate sets for different services!

Remember about naming users and databases in a way that will allow for easy identification. You will not remember what service uses a database named db_1 but wordpress_mywebsite will be easily identifiable.

Show MySQL data

NOTE you have to be logged to MariaDB in order to use the following commands.

To list all users registred in your MySQL use the following command

SELECT User FROM mysql.user;

To show all databases in your system

show databases;

If you want to show grants just for a particular user, use the following pattern

show grants for '$USER'@'localhost';

To show users and databases they are allowed to use

SELECT User, Db, Host from mysql.db;

Deleting a user and a database

If you decide that you want to remove a user or a database log into MySQL server as root user and run one of the following commands

DROP DATABASE $DATABASE_NAME;
DROP USER '$USER_NAME'@'localhost';

Before logging out reload privileges with the following

FLUSH PRIVILEGES;

And you are done

Conclusion

PHP and SQL are a necessity, but you do not really have to know that much about them. If you know how to create users and databases in SQL, and search for missing PHP modules you will do just fine.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.