PHP Databases - MySQL/MariaDB Tutorial

In this tutorial we learn free and open source relational database solutions like MySQL and MariaDB.

We cover what MySQL/MariaDB is, how to start their services in XAMPP, and how we can use PHP's built-in mysqli() functions to easily interact with databases.

What is MySQL?

Even though databases like Oracle and PostgreSQL own a large share of the relational database market, MySQL is still the most popular system used in the world. MySQL is also the most popular system used in combination with PHP.

MySQL is a database that runs on a server and stores its data into tables. A table consists of columns and rows, and stores collections of related data. We can think of a database as similar to an Excel Spreadsheet.

As an example, let’s consider an eCommerce website. Its database may consist of the following tables:

  • Products
  • Customers
  • Orders

A single table, like Customers, would then have several columns of the types of data that could be saved.

IdNameEmailShippingAddressPaypalEmail

Each customer’s data would be saved in a row of this table.

IdNameEmailShippingAddressPaypalEmail
1John Doejohn@mail.com123 First Street, Nowherejohn@mail.com
2Jane Doejane@mail.com321 Last Street, Somewherejane@mail.com

MySQL uses Structured Query Language (SQL) to create and manipulate databases with queries.

As an example, let’s consider a situation where we want to display a customer’s shipping address. Our SQL query to the database, would look similar to this.

Example:
 SELECT ShippingAddress FROM Customers WHERE Id=1

In the example above, we select the data in the shipping address column from the Customers table in the row where the Id is 1 (John Doe’s row).

What is MariaDB?

MariaDB is like an improved version of MySQL and was developed to be a drop-in replacement for it.

MariaDB provides greater features and better performance with added bug fixes, additional storage engines, performance improvements and new features.

Even though XAMPP works with MariaDB instead of MySQL, the following tutorials will apply to both. In select cases, we specify where and how any queries differ between MariaDB and MySQL.

PHP's mysqli() functions

PHP provides us with a set of mysqli() functions to quickly and easily connect to and manipulate databases.

Example: mysqli functions
<?php

// open db connection
$conn = mysqli_connect("servername", "username", "password");

// custom operations

// close db connection
mysqli_close($conn);

?>

In the example above, we connect to the database with the built-in mysqli_connect() function.

The function makes it easy to connect to the database and can also give us feedback through its return statements. Apart from a few parameters we need to specify, everything is done for us.

How to start the MySQL/MariaDB server in XAMPP

Before we can do anything database related, we need to start up the database server.

  1. Open the XAMPP control panel
  2. Find the MySQL module in the list and click on Start.

Summary: Points to remember

  • A database is like an online spreadsheet that stores relational data in a table with columns and rows.
  • MySQL is the most popular relational database in the world.
  • MariaDB is an improved version of MySQL and is used in XAMPP.