Database setup using XAMPP

This is a short introduction to help you setup and test access to your database using a local web server (XAMPP). For more information on SQL commands, please refer to database textbook(s).
(Please tell me if you find any errors or omissions in the document —Upsorn Praphamontripong, 29-May-2022)

XAMPP is an open source package that is widely used for PHP development. XAMPP contains MariaDB, PHP, and Perl; it provides a graphical interface for SQL (phpMyAdmin), making it easy to maintain data in a relational database.

If you have not installed XAMPP, please refer to XAMPP-setup to install and set up XAMPP.

Assuming that you have already set up XAMPP

Start the database server ("MySQL Database")
Start the PHP environment ("Apache Web Server")

Note: phpMyAdmin runs on a PHP environment. To use phpMyAdmin to manage databases, Apache Web server must be started.

Reminder: be sure to stop the server when you are done. Leaving the servers running consumes energy and may later prevent the servers from starting (in particular, MySQL server).

Access `phpMyAdmin`

Open a web browser, enter a URL http://localhost to access XAMPP dashboard
Select phpMyAdmin tab

XAMPP dashboard

Alternatively, you may access phpMyAdmin via the XAMPP manager / controller, click Go to Application button to access XAMPP dashboard.

The main page should look similar to the following
screen showing the main page of phpMyAdmin

Add a user account

On the phpMyAdmin screen, select User accounts tab.
Select Add user account link.
Enter user name and password of your choice. Note: do not use any of your official accounts such as UVA account.
Select Local for Host name
Check Create database with same name and grant all privileges
Check Grant all privileges on wildcard name (username\_%)
Check Check all for Global privileges
At the bottom-right of the screen. click the Go button

Do not change or update the root account. If you may forget or need to reset your password, you can use the root account to manage users.

sample screen to add a user account

To verify that the account has been created, go to User accounts tab. You should see the newly created user account (as shown below).

sample screen showing the account has been created

Create a database

Let's create a guestbook database. To create a database, there are several options.

You may use the Create database feature.

On the phpMyAdmin screen, select the Databases tab. Alternatively, you may click the New link on the left panel.
Under the Create database, enter a Database name
Click the Create button

screen showing the create database screen of phpMyAdmin

You may run the SQL command to create a database.

On the phpMyAdmin screen, select the SQL tab
Enter CREATE DATABASE guestbook;
Note: SQL commands are not case sensitive. This example uses uppercase and lowercase simply to make it easy to read.
Click the Go button to run the command.
- For Mac users, you may press Control+Enter to run
- For Windows users, you may press Command+Enter to run

screen showing the SQL command to create a database

Create a table

Let's create a table named entries. To create a table, there are several options.

You may use the Create table feature.

On the phpMyAdmin screen, select the guestbook database.
Select the Structure tab.
Under the Create table, enter a table name and the number of columns.
Click the Go button. This will prompt you to enter the column information.

screen showing the create table screen of phpMyAdmin

You may run the SQL command to create a table.

On the phpMyAdmin screen, select the SQL tab

Enter the following code

USE guestbook; 
CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255), 
    entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));

screen showing the SQL command to create a table

If you already selected the guestbook database (on the left panel), no need to include USE guestbook in your SQL to run.

screen showing the SQL command to create a table

Click the Go button to run the command.
- For Mac users, you may press Control+Enter to run
- For Windows users, you may press Command+Enter to run

Insert data

To insert data into a table, there are several options.

You may use the Insert feature.

On the phpMyAdmin screen, select the guestbook database, select the entries table.
Select the Insert tab.
For each record of data to be inserted, enter the value for each column.
Click the Go button.

screen showing the insert data screen of phpMyAdmin

You may run the SQL command to insert data.

On the phpMyAdmin screen, select the guestbook database, select the entries table.
Select the SQL tab

Enter the following code

INSERT INTO entries (guestName, content) values ("Humpty", "Humpty's here!");
INSERT INTO entries (guestName, content) values ("Dumpty", "Dumpty's here too!");

screen showing the SQL command to insert data into a table

Click the Go button to run the command.
- For Mac users, you may press Control+Enter to run
- For Windows users, you may press Command+Enter to run

Retrieve data

To retrieve data from a table, there are several options.

You may use the Browse feature.

On the phpMyAdmin screen, select the guestbook database, select the entries table.
Select the Browse tab. This will display all existing records of the table.

screen showing the Browse screen of phpMyAdmin

You may run the SQL command to retrieve data.

On the phpMyAdmin screen, select the guestbook database, select the entries table.
Select the SQL tab
Enter the following code
```
SELECT * FROM entries;
```
Click the Go button to run the command.
- For Mac users, you may press Control+Enter to run
- For Windows users, you may press Command+Enter to run

Import SQL file

Create a blank file named friendbook.sql. Paste the following content in the file

CREATE TABLE friends
   (friendName VARCHAR(255),
    phone VARCHAR(255),
    entryID INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(entryID));

INSERT INTO friends (friendName, phone) values ("Humpty", "111-111-1111");
INSERT INTO friends (friendName, phone) values ("Dumpty", "222-222-2222");

On the phpMyAdmin screen, select the guestbook database
Select the Import tab
Choose the .sql file to import
Click the Go button to run the command.
- For Mac users, you may press Control+Enter to run
- For Windows users, you may press Command+Enter to run

Export SQL file (back up your database)

On the phpMyAdmin screen, select the guestbook database
Select the Export tab
Click the Go button to run the command.
- For Mac users, you may press Control+Enter to run
- For Windows users, you may press Command+Enter to run