Database in XAMPP

Database setup using XAMPP

---

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

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

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

---

Add a user account

1. On the  phpMyAdmin  screen, select  User accounts  tab.
2. Select  Add user account  link.
3. Enter user name and password of your choice. Note: do not use any of your official accounts such as UVA account.
4. Select  Local  for  Host name
5. Check  Create database with same name and grant all privileges
6. Check  Grant all privileges on wildcard name (username\_%)
7. Check  Check all  for  Global privileges
8. 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.

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

---

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

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  Control+Enter  to run

---

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.

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));

  

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

  

• Click the  Go  button to run the command.
  • For Mac users, you may press  Control+Enter  to run
  • For Windows users, you may press  Control+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.

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!");

  

• Click the  Go  button to run the command.
  • For Mac users, you may press  Control+Enter  to run
  • For Windows users, you may press  Control+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.

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  Control+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  Control+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  Control+Enter  to run