Part 5 – Adding and Managing a Database

November 2, 2024

!Series: Local Environment MariaDB PHP phpMyAdmin SQL

In this section, we’ll be adding a database to our websites, along with a way to manage those databases. We will be using MariaDB as our database and phpMyAdmin as our database manager.

Downloading MariaDB

MariaDB is an open-source relational database. We’ll be using the free version, MariaDB Community. Go to the downloads page at https://mariadb.com/downloads/community/. The most recent stable version should be selected by default, but make sure that the correct operating system is selected. Click the download button.

Before we get started, let’s create a location for our databases. Just like with PHP, we could run different databases on different sites (or different versions of the same database). To keep things tidy, let’s create a new database folder in our Development folder. I’ll call mine DB.

With that done, we’re ready to install MariaDB. You should see an MSI file in your downloads folder. Double-click it to run the installation. Once you have accepted the license terms, you will reach the “Custom Setup” screen. Most of the default options are fine, but we need to change the location where MariaDB is going to be installed. Click “Browse” to open the “Change destination folder” panel.

Click “Browse” to open the “Change destination folder” panel. Paste the folder name C:\Development\DB\MariaDB 11.5 into the “Folder name” box (change the version of Maria to match what you’re installing).

Click “OK” and then “Next”. Set your root password. We want to leave “Enable access from remote machines for ‘root’ user” unchecked, since we only want this database accessible within our local environment. The data directory should be correct by default (it will be a folder called data inside the MariaDB 11.5 folder we specified earlier).

Continue through the remaining screens and click “Install”. If installation was successful, MariaDB should be available as a service. To test this, open the Windows services panel and make sure MariaDB is listed and running.

MariaDB in the Command Line

In a minute, we’ll install phpMyAdmin to manage our database, but before that, let’s briefly learn how to manage it from the command line. Launch Command Prompt as an administrator. Navigate to the bin folder within the MariaDB folder:

cd C:\Development\DB\MariaDB 11.5\bin

Launch MariaDB with the following command, replacing 123password with your root password:^[1][2]

mariadb -u root -p123password

You should see a welcome message that starts “Welcome to the MariaDB monitor”. You can interact with MariaDB with specific commands, or with any valid SQL statement.

show databases;

By default, there are only some databases related to how MariaDB functions. Let’s create a new database. Enter this series of commands (you should be able to paste them into Command Prompt all at once).

create database test;
use test;
create table TblUser(
user_id int unsigned primary key not null auto_increment,
user_first varchar(100) not null,
user_last varchar(100) not null
);
desc tbluser;

This code creates a database called “test” and then inserts a table into it with a primary key that auto numbers itself and a first and last name field. The last command shows the structure of the tbluser table.

This code creates a database called “test” and then inserts a table into it with a primary key that auto numbers itself and a first and last name field. The last command shows the structure of the tbluser table.

Now, let’s insert a little bit of data into this table with the following code:

INSERT INTO tbluser (user_first, user_last) VALUES ('John', 'Doe');
INSERT INTO tbluser (user_first, user_last) VALUES ('Jane', 'Smith');
SELECT * from tbluser;

This code inserts two rows into the table and then queries that data back out so you can see it.

Now that we have some data available, let’s make sure we can connect to our database from PHP.

Connecting to PHP

Before we can connect to PHP, we need to make some changes to our php.ini file. Remember that we will need to make changes to two files—the one in the PHP 7 folder and the one in the PHP 8 folder.

In each file, uncomment the line:

extension_dir = "ext"

This tells PHP where to find the ext directory (the other version is for Linux-based installations). Next, we need to enable the extensions that could be used to connect to our database, so uncomment the following lines:

extension=mysqli
extension=pdo_mysql

MariaDB uses the MySQL driver, but you can either use the PDO or MySQLi API with it (although most people seem to recommend PDO, from what I’ve seen). We should make both extensions available, however.

Finally, uncomment the following:

extension=mbstring

This allows for proper use of substrings that have multi-byte characters (which are common in Unicode). This will allow us to set up phpMyAdmin later.

As a side note—in some tutorials, you will see instructions to enable Zend OPCache. This caches PHP files, and allows you to serve files more efficiently when your website is live. However, we don’t want that for our local environment, since we want any changes to take effect in real time.

Once both php.ini files have been saved, we can try retrieving data from our database. Open up both index.php files (from the test-1 and test-2 folders) and add the following code:

<pre>
<?php
$db1 = new PDO('mysql:host=localhost;dbname=test', 'root', '[your root password]');
$db2 = new mysqli('localhost', 'root', '[your root password]', 'test');
echo var_dump($db1);
echo var_dump($db2);
?>
</pre>

When you refresh your pages, you should see information about the two objects:

As a final test, let’s try to actually request data. Update the code in each index.php file to the following:

<pre><?php
$db = new PDO('mysql:host=localhost;dbname=test', 'root', '[your root password]');
$results = $db->query(
	'SELECT * FROM TblUser;',
	PDO::FETCH_ASSOC
)->fetchAll();
echo var_dump($results);
?>
</pre>

If everything’s working, you should see the user data you inserted into the table earlier:

When you refresh your pages, you should see information about the two objects:

Installing phpMyAdmin

Now that we know that PHP can connect to our database, let’s install phpMyAdmin so we can manage our databases through a graphical user interface, or GUI. Download it from https://www.phpmyadmin.net/downloads/.

Extract the ZIP file. Inside the extracted folder, there is another folder which will be called something like phpMyAdmin-5.2.1-all-languages.

Rename this to phpMyAdmin and copy it to C:\Development\www\test-1 and C:\Development\www\test-2.

Now we need to run the installation, which will be available at http://test-1.local/phpmyadmin/setup/. Note that you will get quite a few warnings at the top, including one that warns about security issues because we’re currently using HTTP and not HTTPS. For now, that’s okay since we’re in a local environment.

We’re going to change a few settings. Under “Features”, select the “General” tab, we can set the first day of the calendar.

In addition, under the “Security” tab, we’ll create a 32-bit encryption key, which helps the login/logout function to work correctly. To generate this, go to one of your index.php files and use the following code:

<?php
echo bin2hex(random_bytes(32)) . PHP_EOL;
?>

Refresh http://test-1.local/ and copy the string you get. In the phpMyAdmin setup, go to “Features” and the “Security” tab, and then paste the key into the “Blowfish secret” box.

Click “Apply” at the bottom of the page. When you return to the “Overview” page, click the “Download” button to download a configuration file. Move the file (which needs to be named config.inc.php to C:\Development\www\test-1\phpMyAdmin.

With that done, go to http://test-1.local/phpmyadmin/ and try logging in with the root username and password. You should see the phpMyAdmin panel, along with all your databases on the left side (including the test database we created earlier).

Repeat the process in this section for http://test-2.local/.

Creating Users and Managing Permissions

We probably don’t want our test-1 and test-2 sites to be using the same root user. Let’s create different databases and user accounts for both of them. You can use phpMyAdmin from either site for this portion of the tutorial.

Create a new database, test1. You can create a database in the “Database” tab by clicking on the “New” button, naming the database, and clicking “Create”.

When the database has been created, you’ll be taken to the “Create new table” function. We’ll create a table called tbluser with three columns.

You’ll be dropped into the Structure page for this table. Choose the following settings:

1. Column 1
• Name: user_id
• Type: INT (integer)
• Index: PRIMARY (makes this the table’s primary key)
• A_I: checked (stands for auto-increment, will number the table rows automatically)
2. Column 2
• Name: user_first
• Type: VARCHAR (variable-length string)
• Length/Values: 100
3. Column 1
• Name: user_last
• Type: VARCHAR (variable-length string)
• Length/Values: 100

Let’s insert some data into the table. We can do that with the “Insert” button. Add some data and click the “Go” button at the bottom.

We’ll also create a database for test-2. This time, will use the SQL to achieve the same effect. Click on the “SQL” tab.

First, we’ll create a database by entering the SQL below and clicking “Go”.

create database `test2`;

Click on the newly created test2 database in the left sidebar to activate it, and then go to the “SQL” tab. Enter the following SQL and click “Go”.

create table TblUser(
user_id int unsigned primary key not null auto_increment,
user_first varchar(100) not null,
user_last varchar(100) not null
);
INSERT INTO tbluser (user_first, user_last) VALUES ('Esme', 'Weatherwax');
INSERT INTO tbluser (user_first, user_last) VALUES ('Gytha', 'Ogg');

With that done, let’s add some new users and restrict their permissions so they can only see the test1 or test2 database. Click on the “Home” icon to go back to the home screen, and then click the “User Accounts” tab. Click the “Add user account” button.

Set a username. Set “Local” for the host name, and set a password. Don’t give any other permissions and click “Go”.

Go back to the “User accounts” tab and click “Edit privileges” by the test-1 user.

Click the “Database” tab. This will let us give permission for this user to manage the test1 database but not any others. Chose test1 and click “Go”.

Click “Check all” next to “Database-specific privileges” and click “Go”. Repeat these steps for the test-2 user with the test2 database.

Log out of phpMyAdmin and log back in as one of the users we just created. You should only be able to see the information_schema database (which will always be visible) and the test1 or test2 database (depending on which user you’re using).

The last step is to update the login information in your PHP files. Change them like this:

Index file (index.php) in C:\Development\www\test-1:

<pre>
<?php
$db = new PDO('mysql:host=localhost;dbname=test1', 'test-1', '[your password]');
$results = $db->query(
	'SELECT * FROM TblUser;',
	PDO::FETCH_ASSOC
)->fetchAll();
echo var_dump($results);
?>
</pre>

Index file (index.php) in C:\Development\www\test-2:

<pre>
<?php
$db = new PDO('mysql:host=localhost;dbname=test2', 'test-2', '[your password]');
$results = $db->query(
	'SELECT * FROM TblUser;',
	PDO::FETCH_ASSOC
)->fetchAll();
echo var_dump($results);
?>
</pre>

Refresh each page and make sure everything is working. If you entered different data in your databases, you should see that now on your two websites.

Conclusion

That’s it for this section. In the next section, we will switch from using HTTP to HTTPS, an important precursor to installing WordPress.

Further Reading

• More information about MariaDB command line commands:
• mariadb Command-Line Client official documentation
• More information about PDO vs MySQLi API:
• “Choosing an API” on the PHP official documentation
• “PDO vs. MySQLi: The Battle of PHP Database APIs” on Website Beaver
• “What is the difference between MySQL, MySQLi and PDO?” on Stack Overflow
• More information about why extension=mbstring needs to be enabled:
• “What Every Programmer Absolutely, Positively Needs to Know About Encodings and Character Sets to Work With Text” by kunststube
• More information about letting users create their own databases:
• “Let MySQL users create databases, but allow access to only their own databases” on Stack Overflow
• “PHPMYADMIN – How do allow a user to create a database, but only show the db’s created by that user?” on Stack Overflow

Notes

[1] If you look at online tutorials, you’ll sometimes see the command given as mysql -u root. MariaDB is a fork of MySQL, so it still supports that command, but mariadb is the more current version.

[2] Including the password in the command line isn’t considered secure, but it’s fine for a quick test like this. You can also load it from an options file–see the password section of the MariaDB Command-Line Client documentation for more information

Posted In: Tutorials