Add a Simple Database and User to Local phpMyAdmin

I like to make different types of websites. I like making blogging websites, product websites, affiliate marketing websites, and online stores.

These require a database. I prefer to make my websites on my local computer, and get it perfect.

Then I like to upload the website to my live site.

Here is an example of a mug webpage. Each mug has a picture, a title, a blurb, and a link to a product page.

This could easily be an affiliate website. With a different template, it could be a blogging website.

Start MAMP or XAMPP

Start MAMP or XAMPP, depends on whether you have a Mac or a PC. Wait for the servers to start.

MAMP will show a Green start/stop icon.

XAMPP will show Apache and MySQL coloured green.

Go to localhost/phpMyAdmin

Bring up a browser and go to locahost/phpMyAdmin.

If you are working on a website on the internet, go to phpMyAdmin on your website, such as through cPanel. What is shown here will apply, perhaps with some differences.

This will take you to phpMyAdmin.

Add a New User Account

Click on New in the left column.

Click on the User Accounts tab.

Click on the Add user account link.

Enter a username and a strong password in the appropriate fields. Retype the password in the Re-type field. Alternatively, click the Generate button and phpMyAdmin will fill in the appropriate password fields.

The generated password will be displayed next to the Generate button.

Keep a copy of your User name and password in a safe place for future use.

Select the privileges you want the user to have and click the Go button.

You will be told that you have added a new user.

Create An Empty Database

Next, create an empty database. For some uses this is the final step. For example, if you are installing WordPress manually, WordPress only needs an existing empty database. Of course, WordPress will also need to be told the database name, username and password.

To create an empty database, click on New in the left column.

Click on the Database tab, enter the database name and click Create button.

You will be told that a database has been created.

The empty database will appear in the left column with a minus (-) sign next to it.

For some cases, an empty database is needed and you are finished. For example, if you are installing WordPress manually.

Most times you need to put a table in the database and populate the table.

Database Tables

A MySQL database table holds information. To make this information very useful, database tables use rows and columns.

Remember this?

Here is the database for that webpage.

Rows typically hold all the information about an item. In my case, each row holds information about a mug.

Rows contain columns. A column holds information about a specific feature of the item. A column has a data type, which tells you how to use it. A column can contain numbers, text, the date and time, etc.

In my case, the middle five columns in each row have a link to the mug’s image (img_url), name of the mug (title), a blurb about the mug (content), a category (category), and a link to a product page (link). I will insert or update these columns myself, with PHP code.

These columns will only contain text characters, so I will use the data type TEXT. TEXT can store up to about 2,147,483,647 characters of non-Unicode data. Yes, I want overkill.

The TEXT data type stores variable-length non-Unicode data. It can store variable-width character strings up to 2,147,483,647 characters. Its maximum size is 2 GB. Its storage size is 4 bytes + the number of characters being stored.

learnsql.com

Normally, a row will also have a column to hold the date and time. This allows you to order the rows from newest to oldest, or oldest to newest. This is the rightmost column, called timestamp. The database will insert or update timestamp as needed. The data type for this column is TIMESTAMP.

The TIMESTAMP data type is also used for values that contain both date and time parts. It also supports fractional seconds. Additionally, starting from MySQL 8.0.19, this data type provides full support for time zone information. The supported range is ‘1970-01-01 00:00:01‘ UTC to ‘2038-01-19 03:14:07‘ UTC.

learnsql.com

A row typically has a column that has a unique identifier. This allows the specific row to be found quickly by the database. This is the leftmost column, called id.

One of the purposes of a unique row identifier such as id is that if a mug is entered into the database twice accidentally. Other than the identifier and timestamp, the information about each mug is identical. The identifier allows us to remove the specific mug we want to remove.

id is special. It is a Primary Key that auto-increments. This ensures that each row as a unique id.

A Primary Key value must be unique and can never be empty.

A primary key is a column or a set of columns that uniquely identifies each row in a table. It must obey the UNIQUE and NOT NULL database constraints.

learnsql.com

To do this, I will have the id column auto-increment. It will start at 1 and increments by 1 whenever a new row is added. This auto-increment feature allows the database to automatically fill in this column, so you don’t have to. This ensure that every value in the column is unique and never repeats.

One of the many features offered by SQL is auto-increment. It allows us to automatically generate values in a numeric column upon row insertion. You could think of it as an automatically supplied default value – the next number in a number sequence – that’s generated when the row is inserted. It’s most commonly used for a primary key column because this column must uniquely identify each row and the auto-increment feature ensures this condition is fulfilled.

learnsql.com

I also never want to run out of rows for my database. Again, this is overkill. I will also make the id column a BIGINT data type. This will allow me to have up to approximately 9,223,372,036,854,775,807 rows.

You don’t have to get fancy like this, it is just my personal preference. Most databases use INT instead of BIGINT and VARCHAR instead of TEXT. INT and VARCHAR are good for most databases, but they do not meet my needs.

Set up your database as you wish. This example shows a database set up the way I often do it. I will later access this database using PHP and MySQL. I try to reuse my PHP and MySQL code as much as possible to minimize software bugs.

To learn more about data types, go here.

I have created a generic table that works well for most of the websites I create. Basically, I want the database and its tables to be as versatile as possible. I want an insanely good database. I do not want to think about the nitty-gritty details, I just want to use my generic database.

Most people will never need a database like this. I do.

How to Add a Table to the Database

I am going to call my table items and I need seven columns per row.

To create a table, click on the database name in the left column.

In the right column, enter the table name and the number of columns needed. Then click the Go button.

This will take you to a page for setting up the columns.

Enter the information for the columns as shown below.

Don’t forget to put the checkmark in for auto-increment on the id column. This will set Index to PRIMARY.

Again, you don’t have to Export the database. I did, so I wanted to show you how to.

Optional: Insert Data Into the Table

I normally use PHP to insert information into a database table. Sometimes, it is quicker to use phpMyAdmin for this. Here is how.

In the left column, click on the name of the table you want to insert data into.

Then click on the Insert tab. The following will appear.

Enter information into the columns/fields, except for id and timestamp. Leave id and timestamp blank. MySQL will fill those for you.

Below the last column/field, in this case timestamp, there is a Go button. Click the Go button.

A record has been added to the table items. Click on the table name items in the left column to see it.

There you have it. You now know how to set up a local database on your computer.