Using phpMyAdmin has been the go-to tool for managing online databases for years. In this tutorial you’ll learn how to create a database, create and manage tables, enter records, and create primary and foreign keys using the tool. You’ll also look at creating queries and even stored procedures.
Rather than getting an online domain and working with the database and tables online, it’s best to work on a local workstation. You can download software that will convert part of your desktop or laptop to a web server. If you’re familiar with developing websites that use a server language like PHP, ASP, ASP.NET, etc. then you’ll be familiar with setting up your server.
A very common acronym used to describe the local version of a web server is LAMP. Basically it stands for:
- Linux operating system
- Apache web server
- MySQL database
- PHP server files
You can also download WAMP (Windows version) or MAMP (Mac version). The next section of the tutorial steps through the process of downloading and installing the XAMPP web server software. All these services basically operate the same even though they have different names.
Setting up Localhost
As mentioned previously, there are several well known server frameworks available that you can install on your PC, Mac or Linux workstation. The XAMPP software will be used for this tutorial. To install the software:
- Go to the XAMPP website (https://www.apachefriends.org/download.html). You can locate XAMPP by using your favourite search engine.
- Find the Operating System you want to install and download. For this tutorial, it won’t matter on which version of PHP you download. Figure 1 shows what the download screen looks like at the time of this article.
Figure 1 – Download page for XAMPP
- Click on the Download button. In this tutorial, the middle version of the Windows operating system was chosen.
- It’s over 100 MB so it will take time to download. Once it is downloaded, run the xampp-win32-5.6.15-1-VC11-installer.exe file. The file name will change depending on the latest release.
- After the opening dialog box appears, click the Next button.
- When asked which components to install, stay with the default settings and click the Next button.
- In the Installation folder dialog box, make sure to note where the software is being installed. This is very important later on. For this tutorial, XAMPP will be installed in D:\xampp. You can install it on C:\xampp which is the default. Click the Next button once the folder has been designated.
- The next dialog box asks if Bitnami should be installed. This is used for installing frameworks like Drupal, Joomlia or WordPress. For this tutorial, click on the check box to turn off the Learn more about Bitnami for XAMPP as shown in Figure 2. Click the Next button.
Figure 2 – Skip the Learn More
- The next dialog box will say “Ready to Install”. Click the Next button to Install the XAMPP web server.
- Once the installation is complete, leave the check box on to start the Control Panel and click the Finish button.
The control panel for XAMPP should be open now. In the future to open the control panel, navigate to the folder where XAMPP was installed. Figure 3 shows the location of XAMPP. Double click on the xampp-control.exe to start the control panel.
Figure 3 – Starting the Control Panel
Once the control panel is up and running, click on the Start buttons for both Apache and MySQL. As shown in Figure 4, both these services are running.
Figure 4 – Apache and MySQL are running
Click the Admin button for Apache. This will open the computer’s default browser. You’ll notice that the address starts with “localhost”. If it’s the first time XAMPP has been started, you may be prompted to select a language. Once the language is selected, Figure 5 shows what the home page for XAMPP may look like.
Figure 5 – XAMPP Home Page
The final step to get into the phpMyAdmin is to click on the link under the Tools section called phpMyAdmin as shown in Figure 6.
Figure 6 – Starting phpMyAdmin
The result will be the start of phpMyAdmin. It should look similar to Figure 7.
Figure 7 – phpMyAdmin Interface
Your screen may not look exactly like Figure 7 but the layout should be the same.
You may ask if working with the localhost version of phpMyAdmin is the same as what you experience when managing databases on a web server. Figure 8 is a screen capture of GoDaddy’s version of phpMyAdmin. As you can see, it’s very similar and you should have no trouble navigating between different versions of phpMyAdmin.
Figure 8 – phpMyAdmin from GoDaddy
Creating a Database
The next step is to create a database. Depending on your web hosting service, this may be restricted. Some hosting companies will give you one database file. However, inside that database, you can create a large number of tables. To help explain this, think of a database as an Excel file. Inside the Excel file, there are three spreadsheets by default. You can always add more spreadsheets as you see fit. Think of the spreadsheets in Excel as a Table in a database.
To create your own database to store your tables:
- Click on the Databases tab.
- In the Database text field, enter the name easydb and click the Create button.
That’s it. You should notice in the left column, the database name now appears as shown in Figure 9.
Figure 9 – Listing of all Databases
Now that the database is created, you can start creating tables. While this tutorial does not focus on database schema (structure) design, there are a few simple rules to keep in mind. First rule is to attempt to eliminate duplicate data. For example, we will create a system that keeps track of student marks. When doing this, you don’t enter the students name over and over for each mark. What happens if the student has a name change part way through the semester? You don’t want to have to go back and change all those records.
A second rule is that each record needs a unique value. This way you can specify a record or a range of records based on that unique value. This unique record is known or set as the Primary Key.
Final rule we’ll use is to use integers to establish relationships. Databases can search numbers faster than they can search text. Figure 10 shows an outline of the tables and the fields in each table. The arrows indicate how the tables will be linked together.
Figure 10 – Basic Database Schema
In the marks table, you’ll see studentid points to the ID in the student table. The ID column is the Primary Key. The studentid in the Marks table is a Foreign Key. A simple definition of a Foreign Key is that it points to a Primary Key in another table.
To create these tables:
- Click on the easydb database in the left column.
- Enter the name student in the Create Table Name text field.
- Change the number of columns to 5. It should look like Figure 11.
Figure 11 – Creating the Student Table
- Click the Go button on the far right side.
- In the first row for the name, enter id.
- If need be scroll to the right. Click the Null check box so the system knows that this field cannot be null.
- Click the A_I check box. This will make it an Auto Increment. By having the Auto Increment checked, each record will get a unique value.
- There may be a pop up window asking if you want this as the Primary Key. Click the OK button to make the id column the Primary Key. The row will look like Figure 12 when you finish the row.
Figure 12 – Settings for the ID
- The rest of the columns will be text based. So enter the names and change the data Type to text. It should look like Figure 13 when the form is completed. Make sure to keep all the names in lowercase.
Figure 13 – Completed form
- Click the Save button.
When it’s done, phpMyAdmin will display the structure of the table. Notice that you can also change or edit each of the fields. It’s also possible to add or drop fields to the table after it’s been created. Be very careful about changing a database structure after your site has gone into production. It’s very easy to break if you’re not careful. That’s why database design up front is so important.
One of the tasks was to make sure the column id was set as the Primary Key. This tells the database system that this column will have the unique identifier for a row of data. A table can only have one Primary Key.
Now it’s time to create the next table. The process is basically the same as above so the steps below will be abbreviated.
- Click on the New link under the easydb in the left database column.
- The phpMyAdmin will give you a new form with a default four columns. Type in the table name marks at the top of the page.
- Add 3 more columns. Your page should look similar to Figure 14. Once it does, press the Go button.
Figure 14 – Creating the marks Table and Adding 3 Columns
- Make the first column the id column that has the auto increment and Primary Key turned on. Follow the steps 5 – 8 from the previous steps.
- Fill in the rest of the names and data types so it matches Figure 15.
Figure 15 – Column Names and Data Types
- Click the Save button.
While the columns should make sense as to the type of data, there are two that may require and explanation. The base column is going to be used to record what the assignment is out of. If you have a mark of 19, is that good or bad? Well if the base is 20, it would mean you got 19/20. The other column is the courseid. That’s the third table you will create. This table will all the courses that a student may be part of. We don’t list the course code such as MAD100. We would use the ID value of the course to link the tables together. There is more on that in the Establish a Relationship part of the tutorial.
Now you need to make one final table. This table will store information of the different courses that the student may be enrolled in. Between the two previous explanations on how to create a table, see if you can create a table called courses so it looks like Figure 16.
Figure 16 – The courses Table
Now that the tables have been created, you can use the phpMyAdmin to add data. Once the data is added to a table, then you can browse and edit the data. We will add data using phpMyAdmin to the tables in the following order:
- Insert two students.
- Insert two courses.
- Insert 8 marks.
To insert data into the student table:
- 1. Click on the student table under the easydb database in the left column.
- Click the Insert tab at the top of the page.
- The form has enough fields to enter two students. DO NOT fill in the id column. This is set to Auto Increment so the database system will fill that in. Figure 17 shows the start of filling in the first record. (Tip: You can move to the next text field by pressing Tab after you finish typing in the data.)
Figure 17 – Inserting Data into the Student Table
- After scrolling down and filling information for two students, click the Go button.
- phpMyAdmin will then show you the results of the SQL statement.
Figure 18 shows what the completed job looks like. At the top in the light green is a comment indicating 2 rows were inserted. Under that is the SQL statement that was created and run. Below that is an editable version of the SQL statement. From here you can edit the statement and add more records.
Figure 18 – Results of the Inserting of Two Records
By clicking on the Browse tab at the top, phpMyAdmin will all the records of the table. In this view, you can click the Edit link to change parts of that record. Or, you can double click on one of the text fields to edit it inline. Figure 19 shows the editing of a record.
Figure 19 – Editing a Record in the Browse View
The next step is to add a few courses into the courses table. The information used in this tutorial is as follows:
|MAD105||Basic Programming for Mobile||8||14|
After inserting the two records, it will look similar to Figure 20.
Figure 20 – Two Courses in the courses Table
Finally, we need to enter a number of records into the marks table. The Figure 21 shows the data entered in for this tutorial. What may be a little confusing is when the data is entered in for the student ID and the course ID. What is happening is the value students.id column is what is put in the marks.studentid. The courses.id is inserted into the marks.courseid. Seeing that we only have 2 students and 2 courses, the values will be either 1 or 2.
Figure 21 – Records Inserted in the marks Table
Are You Lazy?
When using the phpMyAdmin to enter the data, you can do a little bit of a short cut. When the first two records were entered, you’re taken to the result page. By changing the data in the editable part of the SQL section, you can insert two new records. Or another thing you can do is click the Copy link for a record. Then change a few of the data cells. So once the four records for student.id of 1 was entered, click the Copy link and change the student.id to 2. Also change the mark and comments to make the records a little more unique.
Normally when developing something like this, there are many more tables. From what we have above, we could make a fourth table for the assignments and link in the assignment by its ID value. However, for this tutorial, we wanted to keep the database schema or structure simple so we can
focus on phpMyAdmin.
Establish a Relationship
To keep this simple, in this section of the tutorial we will focus on creating a SQL statement that creates an Inner Join between the marks table and the student table. When an inner join is created, you can access data from a second table. Normally the SQL statement would be written in the server side language of the web page. A majority of the time the SQL statement is written in PHP.
There are actually two ways the SQL statement can be written. The first example we will write using an INNER JOIN attribute. To use phpMyAdmin to write an inner join SQL statement:
- Click on the marks table.
- Click on the SQL tab. Figure 22 shows you what the default interface looks like.
Figure 22 – Default SELECT Statement
- Click the SELECT button. Now the SQL statement now lists all the columns. You can edit this statement to remove columns that are not needed.
- Put the name of the table ‘mark’ followed by a period in front of each column name. Make sure to remove the single quotes around each column name. The SQL statment should look like the following:
MySQL1SELECT marks.id, marks.studentid, marks.courseid, marks.assignment, marks.mark, marks.base, marks.comment FROM `marks` WHERE 1
- Add the columns from the student table to the column list. The SQL statement will not look like this:
MySQL1SELECT marks.id, marks.studentid, marks.courseid, marks.assignment, marks.mark, marks.base, marks.comment, student.id, student.fname, student.lname FROM `marks` WHERE 1
- Remove the FROM ‘marks’ WHERE 1 from the SQL statement.
- Add the INNER JOIN code. The bolded code below shows the INNER JOIN parameter.
MySQL1SELECT marks.id, marks.studentid, marks.courseid, marks.assignment, marks.mark, marks.base, marks.comment, student.id, student.fname, student.lname FROM `marks` INNER JOIN`student` ON marks.studentid = student.id
- Click the Go button. If everything is typed in correctly, phpMyAdmin will show all the records of the marks table with the additional columns of id, fname and lname appearing at the end of the columns. It’s data from these columns that you would use in your web page as opposed to the studentid value in the marks table.
Figure 23 – Results of SQL statment
- Click on the Create PHP Code link. The phpMyAdmin system will write the appropriate PHP code that you can copy and paste into your PHP page. The phpMyAdmin system shows that the code works so this will reduce chance of errors in your PHP scripts.
The other way you can write the same JOIN code is the following:
SELECT marks.id, marks.studentid, marks.courseid, marks.assignment, marks.mark, marks.base, marks.comment, student.id, student.fname, student.lname FROM `marks`, `student` WHERE marks.studentid = student.id
In the above SQL statement, you don’t use the INNER JOIN but use the WHERE filter instead. You’ll also notice the both tables are listed in the FROM section.
Creating a Stored Procedure
A stored procedure is a set of SQL commands stored under a name. This procedure can be called from a web page to run the SQL commands found in the name of the stored procedure. There are two main advantages. First, it can run quicker because the stored procedure resides with the database. Secondly and most importantly, it is more secure. You are not sending a SQL statement, just making a call to the stored procedure.
To make a stored procedure in phpMyAdmin:
- With the easydb database selected on the left column, click on the Routines tab.
- Click the Add Routine link under the New section. Figure 24 shows the layout of the Routine section.
Figure 24 – Adding a Routine
- In the Routine name, type averageMark.
- In the Type make sure it’s set as a Procedure.
- For the Parameters, set the Direction to IN.
- Under the Name, type cid. Leave the Type at the default INT.
- In the Definition text area, type the following SQL statement.
MySQL1SELECT AVG(mark) FROM marks WHERE marks.courseid = cid;
- In the Definer text field, type [email protected].
- Leave the Security type as DEFINER.
- Change the SQL data access text field to READS SQL DATA. The completed dialog box should look like Figure 25.
Figure 25 – Add routine Completed Dialog Box
- Click the Go button.
Once the Go button is pressed, the procedure is created. We added one variable, cid. This will allow us to call the procedure and pass the ID of the courses the students have been taking. Currently we only have the ID of 1 or 2. So we can run the procedure, pass the course ID (1, 2) and it will return the average student mark. That’s what the SQL statement does.
To run the procedure you just created, click the Execute link. A dialog box will appear asking you for a value for cid. Enter the number 1 in the Value text field and click the Go button. You should see results similar to Figure 26. You’ll see the result is 20.500. Try executing this procedure again but this time add the number 2 in the Value field and see the results.
Figure 26 – Results from running the stored procedure
If you look over at the left column of the under the easydb, you should notice there is a Procedures category. Click on the plus sign in front of Procedures. It should look similar to Figure 27.
Figure 27 – Procedures is Added to Database
As shown in Figure 27, you can now see all the stored procedures created. Click on the procedure, averageMark, and you’ll run the procedure right away. Once the procedure has run, you can click on the Edit link to edit and change the procedure. By clicking on the Drop, you will delete the procedure. Be careful as this is not UNDOABLE.
Transfer Data from Local to Web Server
In 2004 I wrote a textbook for Thompson Publishing that shows how to build database driven websites using ASP.NET. The ASP.NET server technology is a Microsoft product so it was also common to use a Microsoft Access database. It was very easy to transfer data to and from the server. You basically only had to copy the Access database file. It had the extension of MDB. However, MySQL as well as other system databases, don’t have a single file that you can copy. So if you’ve been working on your computer under the localhost, how do you get the data from your local computer to an online server? It’s actually not too difficult. To transfer data from the local computer to your web server:
- In the localhost version of your phpMyAdmin, click on the database you want to export. In this case, easydb.
- Click on the Export tab.
- Leave the radio button on Quick as we want to export all the data and tables.
- Click the Go button.
- There will be a file that is downloaded. It ends with .sql. Figure 28 shows the name of the file that was downloaded to the Download folder.
Figure 28 – Database Download
NOTE: You don’t have to export all the tables. You can click on a table and then export the data from just that table.
To install the database to your web server that’s online:
- Login to your online database service.
- Click the Import tab.
- Click the Choose file button.
- Navigate to the location of the SQL file. By default in a browser like Chrome, it would be saved in your Downloads folder.
- Once you click on the easydb.sql file, click the Select button.
- For this tutorial, leave all the inputs at the default settings.
- Scroll down to the bottom of the page and click the Go button on the left hand side.
- The data from your local computer is now copied onto your server’s database.
Hopefully stepping through some of the features of the phpMyAdmin system have shown how quickly you can setup database and tables. There are also other tools in there to help you manage your data and even test the writing of SQL statements. Using the Import and Export features, you can quickly move the data from one service to the other service. If you haven’t had the time to use phpMyAdmin much, hopefully you’ll see some of the advantages of this service for your next database driven website project.