How To Setup and Query Local MySQL Databases

If you have never setup a local MySQL database on a Windows machine before, then this post is for you. You will be up and running with a database you can query in 10-15 minutes.

To get you rolling, we will also touch briefly on how to create, view and edit your new database using phpMyAdmin.
Although this post is focused entirely on the MySQL database setup, we will also be setting up a development environment on your machine that you can later use to host a local website.

wampserver

Alright, let’s get started by heading over to the downloads section of the WampServer website. Wamp stands for Windows, Apache, PHP and MySQL. It is completely free to use and greatly speeds up the time to get a development environment up and running.

Choose the download version that best suits your local machine. For most people is it one of the top two options, which as of writing are WAMPSERVER (32 BITS & PHP 5.4) and WAMPSERVER (64 BITS & PHP 5.4). If you are really unsure if your machine has 32-bit or 64-bit architecture then follow these instructions.

wampserver-options

A pop-up window will appear warning you that you will need to install the Visual C++ 2010 SP1 Redistributable Package x86 or x64. Go ahead and click the link to download and install this from the Microsoft Download Center.

Once you have this installed this you can click on the first link in the pop-up to download WampServer directly.

Installing WampServer

Follow the WampServer installation instructions.

I recommend installing WampServer at the root of one of your drives so you can quickly access this later and to avoid any technical complications later.
wamp-install-1

Allow Wamp to create a Quick launch icon, we will be using this in a little while to start Wamp and access any of the Wamp configuration files directly.

During installation Wamp will also ask you for your default browser. For now simply click Open, we can configure this at a later point.

If prompted with a Windows Firewall message for Apache HTTP Server, click Allow access for this.

wamp-install-2

Keep the default configuration for SMTP e-mail with localhost and you@yourdomain.

wamp-install-3

Once you have completed the WampServer installation you can go ahead and Launch the application.

Configuring WampServer

In the Quick Launch area of the Windows taskbar you will see the WampServer icon.

wamp-quick-start

If you do not immediately see this icon you can click on the up arrow to get access to all of Quick Launch applications.

wamp-setup-b

The first step will be to click on “Stop All Services” (this will turn the icon red ). Next, click on “Start All Services” (this will turn the icon either green wamp-green  or orange wamp-orange).

If the icon turned green then we are in business and you can move ahead to the WampServer phpMyAdmin section of the post. If not, then there are just a couple more steps that you will have to do below.

WampServer Won’t Start (Orange Wamp Icon wamp-orange)

If you are getting an orange WampServer Icon when trying to start all services then the most likely scenario is that you local machine is not allowing WampServer to run on port 80.

  1. Click on the WampServer Icon > Apache folder > httpd.conf file.
  2. The httpd.conf file should open in notepad
  3. Hit CTRL + F and search for Listen 80. Change Listen 80 to Listen 81.
  4. Next search for ServerName. Change this line to ServerName Localhost if it is not already.
  5. Save and close the file.
  6. Click on the WampIcon again and click “Start All Services”
  7. The icon should now turn green. Success!

wamp-setup-menu

WampServer phpMyAdmin

Open up your favorite web browser.

Type localhost (localhost:81 if using port 81) and hit return.

You will be presented with the WampServer Homepage.

From this Homepage you can get access to any project folders you have setup and you can also access tools you have installed with WampServer. The tool we are interested in here is phpmyadmin. Click on this.

You will be presented with a username and password dialog. You are probably asking at this point what the username and password could be since you did not set this at any point during the installation. By default WampServer will setup MySQL to the username “root” and will set no password. So to get in simply type root and click the go button.

phpmyadmin-login

Creating phpMyAdmin Database

On the left pane of phpMyAdmin you will see a list of databases that are already available to you.

phpmyadmin-databases

This typically comes with a test database for you to use, but for this post we will explore adding a new database. To do this, click on the databases button located top left of the right pane.
From this page you can create a new database. Type in the database name. Keep the drop-down option selected on Collation and click the create button.

phpmyadmin-create-database

On the left you will now see your new database. Click on this new database to access the structure page on the right. From this page we can create new tables and the corresponding table columns.

Creating phpMyAdmin Tables

Lets create a new table in the database. For this example, lets go ahead and create a table that will contain a list of users.

  1. Type in the name “users” and enter 4 in the columns input field
  2. Click the go button
  3. You will see four rows stacked below. In the first box enter userID, keep INT (integer) selected under the type drop-down, enter 6 into length/values, select PRIMARY from the Index drop-down and check the A_I box (Auto-Increment).
  4. In the second row enter firstName into the name field, choose VARCHAR from the type drop-down and enter 50 into the Length/Values field.
  5. In the third row do the same as the firstName row but change the name field to lastName.
  6. Finally in the fourth row enter emailAddress into the name field, select VARCHAR from the type drop-down and enter 255 into the Length/Values field.
  7. Click save

phpmyadmin-create-table

We have just created a new table in our database with four columns per row. In the first row we used auto-increment for the userID, this will result in a unique ID for each member. We chose to use an integer for the type, which allows us to enter in whole number values. We also chose this as our primary key. The primary key creates an index in our database. This means that later we can query the database and quickly retrieve information based on this primary key. In this case our query might read something like SELECT firstName, lastName, emailAddress FROM users WHERE userID = 1.

In the last three rows we created a variable character value for each of the records. We also set a length for each of these values. This means that we can accept first and last names of 50 characters or less and an e-mail address up to 255 characters. Varchars can be set to a value of 0 to 255.

Adding Table Records

On the left pane you will see your database with the table listed below. If this table is not already selected, go ahead and click on it.

On the top navigation bar on the right, click on the Insert button.
Assuming you have followed the table creation steps above and have selected the user table, lets go ahead and create a new user record in our database.

  1. Enter John into the firstName input
  2. Enter Smith into the lastName input
  3. Enter jsmith@mail.com into the emailAddress input
  4. Click go

phpmyadmin-table-user

Here we have created a new user record in our database table.  Notice how we left the userID field blank. For auto-increment fields we do not need to enter in a value, it will auto-increment the userID number without overwriting existing record numbers.

Next up we will learn how to query the database to find records.

Query Database Records

In phpMyAdmin you have three ways to get access to the data contained within.
When we have selected a table we can click the browse button to see all a list of records contained in the table.
We can also click the Search button, which will display a search form that allows us to enter values into each field. From here I can search for users with the firstName LIKE John, which will return everyone with that name. We can also search for the firstName LIKE %…% jo, which will return everyone with “jo” somewhere in their firstName.

As you perform these queries you will notice that phpMyAdmin is displaying SQL queries based on what you entered. In the example used above when searching for first names with “jo” the query displayed will be:


SELECT *
FROM  `users`
WHERE  `FirstName` LIKE  '%jo%'
LIMIT 0 , 30

This is the third way of locating records in the database and far more powerful than what can be done in the phpMyAdmin Browse or Search interfaces. Selecting the SQL button will take you to page where you can enter your SQL queries. From here you can enter in queries like the one above, you can also enter in more advanced queries to get access to records from multiple tables. I will save this more advance querying for later post.

Summary

I hope that this post has been informative and you didn’t run into any roadblocks along the way. If you found any issues or you would like to recommend alternatives, then please feel free to contact me directly or reply to this post below.

Congratulations on setting up your first local dev environment with MySQL database access.

Leave a Reply