Hello! In this tutorial we will be getting familiar with some of the common phpmyadmin commands and menu system. We shall be creating a new database, putting some tables in that database and then adding data, or rows to that database. If you don’t know what phpmyadmin is, then you can find out about it and get started here.
Adding a database in phpmyadmin
Open up phpmyadmin and from the dashboard, click on ‘Databases':
This will take you to the database page. You will see a list of current databases, and you will see an area to add a new database:
Enter the name of your new database in here, let’s call it ‘my_database’. Leave the drop down box as it is and click on create. You should see a success message telling you your database has been set. You can now see your new database in both the menu on the left, and in the database list.
Now that we have the database, we need to add some tables. A common set-up would be to have one database for the site that is using it, and within that database we would store our data, separated into different tables. For example, we might have one table for products, one table for users, one table for subscriptions etc.
Each table is in turn split into columns which specifies the type of data we have stored. If we had a ‘users’ table, we might have columns entitled ‘first_name’, ‘surname’, ‘email’. The best thing to help explain it is to just go ahead and do it.
Select my_database, either by clicking on it in the database table, or in the menu on the left hand side. You will be given the option to create a new table:
Call your new table ‘users’, and give it four columns. You will be presented with a ‘Create Table’ pop-up.
We are going to add four new columns – ‘id’, ‘first_name’, ‘surname’, ‘email’. You will see with each column there are number of different options. In most cases, we will just be picking the default option. To create the first row, fill in the following columns. If it’s not specified below, then leave it at the default value ( you may have to scroll right to find some of the columns ):
- Column – id
- Index – primary
- A_I - ( tick the box )
This first column is your primary key. This is the column that will be used to index your table. Just think of it as a unique identification number for each of the rows in your table.
Now create the following rows, using the specifications below, changing only the column name to either ‘first_name’, ‘second_name’ and ‘email’
- type – VARCHAR
- Length/Values - 64
Once you have done this, scroll down and click ‘Save’. You will now have the empty table, ‘users’.
Managing a table – Insert, Edit, Delete
Navigate to your table by clicking on it in the left hand menu. You will be taken to the ‘structure’ sub-page.
You will see each of your rows with information on each row, such as it’s name, type and size. If you look under the ‘Actions’ column, you will see you have the option to ‘Change’ or ‘Drop’ the row. Clicking on ‘Change’ will allow you to alter the attributes of the table that we defined when creating the table. ‘Drop’ will delete the row entirely.
The table is empty at the moment, so if you hover over the ‘Browse’ option in the top menu, you will be presented with a red cross – no go! We have to add some data into our table, or, in mysql speak, ‘insert’ a row.
Inserting a row
Click on ‘Insert’ in the top menu bar, and you should see something like below.
Inserting a record is very straightforward. In the ‘Value’ column, add the following for each column:
- id – leave this blank. If you remember, when creating this row, one of the attributes we set was ‘A_I’ – ticked. This means the column is ‘Auto-Incrementing’. We don’t need to do anything with it – everytime a row is added, this column will populate automatically with the next number from the previously added row.
- first_name – Testy
- surname – McTest
- email – firstname.lastname@example.org
Don’t change or put anything in any of the other columns. While we are here we may as well add a second row. Put the following values into the second box:
- first_name – Whiskers
- surname – Cat
- email – email@example.com
Pressing ‘Go’ will now create these two rows, and you should be told so. Now, if you hover over ‘Browse’ in the top menu, you will be able to click it. Doing so will show you your table, with its two users:
Oh no! We’ve just heard from Testy McTest, apparently we have his email wrong. Let’s change it then. Editing a record is, like everything with phpmyadmin, simple.
In Testy’s row, you will see a small pencil icon with ‘Edit’ written next to it. Click on Edit and you will be brought back to a view similar to when you first inserted the row, but this time with the current values prefilled. To edit the row is easy enough – just change the value. Alter his email to ‘firstname.lastname@example.org’. Pressing ‘Go’ will bring you back to the ‘Browse’ view, were you will see that his email has been updated. Easy.
You’ve decided that you now want your website to be open only to cats. Which means you will have to get rid of all your users who aren’t cats. Well, as we only have two users, this shouldn’t take long.
I’m sure you have worked out that clicking on ‘Delete’ in Testy’s row will remove the row. Once you click on ‘Delete’ phpmyadmin will present you with an alert box to make sure it’s what you really want to do. Once you confirm, we will be back in the Browse view, with no more Testy – only Whiskers Cat.
I hope are beginning to see how handy and easy to use phpmyadmin is. With what you have learnt in the last tutorial and this one, you probably know enough to get by with most small web applications. We can add users, add a database, add a table, and insert, edit and delete rows. In the next lesson we can start marrying up our php scripts to our database. This is all good stuff, as once you can do this you are well on your way to being able to write fully functional web apps. Whoop! See you in the next tutorial. Until then, if you would like to be kept up to date with new posts here at the web developing cat, then be sure to add your name to our mailing list below.