My Web Training

📖 Using phpMyAdmin

phpMyAdmin is a web-based application used to access MySQL databases for development of database driven web sites. It is installed by your hosting company and all that is needed to begin using it is valid credentials to a database schema.

I will create a database schema for each student that needs one. If you are in the PHP class, you will get one. If you get to this point and still do not have a database schema, please contact your instructor and ask them to create an account.

About Your Database Account

Use the server credentials assigned to you for accessing your server account (avatar and password) to access your databases. The same credentials assigned to you in your user profile are used for your database credentials. There may also be a community database available for use in specific classes. If you need access to that database, you will be provided those credentials separately.

phpMyAdmin Help

You can visit SiteGround tutorials for information about phpMyAdmin for some great tutorials on phpMyAdmin. You can also get help with phpMyAdmin by going to the phpMyAdmin forum at StackOverflow. This may be way more information than you need to complete your assignments. I will try to include the relevant information, but if you haven't taken a database class, it might still be confusing.

Here's what you'll need to know for this class.

Accessing and logging in to phpMyAdmin.
Creating tables.
Inserting records.
Selecting records.
Updating records.
Deleting records.

phpMyAdmin login screen shot

All of these things can easily be accomplished using phpMyAdmin. You use the phpMyAdmin application as a development tool, not part of your site. Therefore, phpMyAdmin becomes a great tool for developing database driven site, but it isn't an integral part of your site or it's code. You'll need to write your own PHP code to access the database resources for use in your site pages. The good news is, you can test your site access credentials, develop working queries and test your scripts in phpMyAdmin to see if they are working. And if not, why not?

Access Your Databases

To access your databases, open phpMyAdmin on the class server, First, open a web browser and go to https://mywebtraining.net/webdev/phpmyadmin/.On the phpMyAdmin home page, enter your server credentials. Your server credentials are based on your avatar (from your profile) where your avatar name is prepended with sherd_ .

Username: sherd_AvatarName

Password: avatar password

You should login without any error messages. If you get error messages, check your credentials to make sure they are correct. If you still cannot get in, contact your instructor.

Once in, you should see your databases listed on the left side of the window. Your default database name will be the same as your avatar name. Click on your avatar name in the left-hand navigation menu and it will open the table list. You probably don't have any tables. You'll need to create them as part of your assignments. Once you have database tables created, you can insert new records and select data from existing records. You will also be able to update and delete those records.

Creating Database Tables

Tables are a core element of relational databases. The store the information needed to make the application work. They need to be designed specifically to the needs of the application and can be changed over time as the application evolves. Creating a table isn't super difficult but can be a little daunting for new developers. Tables are created by running a special 'CREATE TABLE' query. The syntax is specific to MySQL and needs certain attributes like column names, data size and data type to be specified when the table is created.

Create Membership Table

See the example below for the code to create a new membership table with the following columns and data attributes.

Member ID - numeric unique record identifier - generated automatically by MySQL
First name - variable character - length(max 75 characters) - Cannot be Null
Last name - variable character - length(max 75 characters) - Cannot be Null
Username - variable character - length(max 75 characters) - Cannot be Null
Email - variable character - length(max 75 characters) - Cannot be Null
Password - variable character - length(max 255 characters) - Cannot be Null
Image - variable character - length(max 75 characters) - Null is allowed

Example

CREATE TABLE IF NOT EXISTS `membership` (
  `memberID` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(75) NOT NULL,
  `lastname` varchar(75) NOT NULL,
  `username` varchar(75) NOT NULL,
  `email` varchar(75) NOT NULL,
  `password` varchar(255) NOT NULL,
  `image` varchar(75) NULL,
  PRIMARY KEY (`memberID`)
);

To use this script, open your database in phpMyAdmin and click of the SQL button. Clear the SQL text area and paste in the code above. Click Go and it should create the table.

Insert Records to the Membership Table

Adding information to a table becomes the next step to having a working database. With the 'INSERT' query, we can add records to the table. These are normally done one at a time, but they can also be done in batches. Batch processing of database tables makes backing up and restoring database tables much easier. Below is an example of the code needed to insert data to the membership table.

Example

INSERT INTO `membership` (`memberID`, `firstname`, `lastname`, `username`, `email`, `password`, `image`)
VALUES (DEFAULT,'Stephen','Herd','sherd','sherd@dcccd.edu','12345', DEFAULT);

Notice the use of the DEFAULT value for the first and last fields. This tells the database to use the programmed default value in the database for that field. This is handy when you are inserting an incomplete record. These fields must allow these default values. The memberID field is set to AUTO_INCREMENT which means it will insert the next available integer. The image field is set to allow NULL values. The other fields will return an error. Make sure your data has the appropriate fields when inserting and updating data.

The other thing worth noting here is that we have specified the fields to be inserted followed by a list of data to insert. The order of each list must be aligned to each other. If the first field listed is memberID, then the first data element must also be memberID and so on. The first list of data fields is optional. If you are inserting a complete record including all fields you don't have to list them. However, you still have to submit the appropriate data in the correct order for the database to manage the insert correctly. The next example shows how to insert multiple records without using the field list.

To use this script, open your database in phpMyAdmin and click the database and table you want to use. Then click click of the SQL button. Clear the SQL text area and paste in the code above. Click Go and it should insert the record.

Example

INSERT INTO `membership` VALUES 
(1,'Stephen','Herd','sherd','sherd@dcccd.edu','12345', DEFAULT),
(2,'Santa','Claus','sclaus','sc@npole.com','snowball', DEFAULT),
(3,'Easter','Bunny','ebunny','ebunny@fluffy.com','eggs', DEFAULT);

Again, notice that each record includes data specific to every field. To use this script, open your database in phpMyAdmin and click the database and table you want to use. Then click click of the SQL button. Clear the SQL text area and paste in the code above. Click Go and it should insert the records. If you get an error that says #1062 - Duplicate entry '1' for key 'PRIMARY', you will need to delete any records you have already inserted and then re-run the script. This script specifies the primary keys and they cannot be duplicated. If you have records from a previous insert, there may be conflicting duplicate ID's.

Working with a Backup Script

Each of the above queries can be run to create a basic table and insert a few records. However, they have to be run as separate queries and there are some details missing in these scripts. If the database is already set up, it can be backed up using the Export command in phpMyAdmin and then the entire database with tables, structure and data can be recreated by Importing a single script. This script also has all the details about specified database engines, character sets and collation. Here is a sample script that creates the database membership table, inserts a few records and sets all the other necessary details.

Example

-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Aug 13, 2020 at 10:01 PM
-- Server version: 10.4.12-MariaDB-1:10.4.12+maria~bionic-log
-- PHP Version: 7.2.24-0ubuntu0.18.04.6

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `avatarname`
--

-- --------------------------------------------------------

--
-- Table structure for table `membership`
--

DROP TABLE IF EXISTS `membership`;
CREATE TABLE IF NOT EXISTS `membership` (
  `memberID` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(75) NOT NULL,
  `lastname` varchar(75) NOT NULL,
  `username` varchar(75) NOT NULL,
  `email` varchar(75) NOT NULL,
  `password` varchar(255) NOT NULL,
  `image` varchar(75) NULL,
  PRIMARY KEY (`memberID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used as base table in ITSE 1406 - PHP class';
COMMIT;

LOCK TABLES `membership` WRITE;
/*!40000 ALTER TABLE `membership` DISABLE KEYS */;
INSERT INTO `membership` VALUES 
(1,'Stephen','Herd','sherd','sherd@dcccd.edu','12345', DEFAULT),
(2,'Santa','Claus','sclaus','sc@npole.com','snowball', DEFAULT),
(3,'Easter','Bunny','ebunny','ebunny@fluffy.com','eggs', DEFAULT);

/*!40000 ALTER TABLE `membership` ENABLE KEYS */;
UNLOCK TABLES;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

To use this script, copy and paste it into a plain text editor like Notepad or Notepad++. Save it as a plain text file with the .sql extension. I would name it membership.sql. Login to phpMyAdmin, select your avatar database by clicking its name in the navigation menu on the left. Then look for the Import button. Click it and click the Browse button to open the File Upload dialog box. Navigate to your membership.sql file, select it and click Open. Scroll down and click Go.

You should see verification messages that tell you all went well. You should also see a new database table listed in the navigation menu on the left. Feel free to click the name of the table to open it. You will see the list of records already present. You are now ready to write your PHP scripts to interact with this table.