For our assignment today, we will need to set up a table in the database where the information about all the products in our e-commerce site will be stored. This post outlines the steps necessary to create a table in the database.
All administration of databases for our class will be handled through phpMyAdmin, a web-based database administrative tool written in PHP and published for free by an altruistic development team. phpMyAdmin allows us to easily do common database tasks using a (relatively) intuitive graphical interface.
The first thing we will be doing is setting up a table in the database. Database servers can hold more than one database. To create a table in the database, we need to first select which database we want to use. On the left side of phpMyAdmin, select “classdb”: this is the database for this class.
Select database
Selecting the database will bring up a list of all the tables that already exist in that database. At the bottom of this list, you will see a space in which we can create a new table on database “classdb”. Enter in the name of your table, and the number of fields it will hold. The name should be your first initial followed by your last name, followed by an underscore, followed by the name you want to give the table. So, for example, my table would be “abloomberg_products”. The number of fields in this example is 5.
Create new table
Once we click “Go”, we are brought to a page that asks us to indicate what fields we would like the table to have, and what type of data will be stored in these fields.
- For any table we ever make, the first field will always be called “id”, and will be of type “Integer”, and have an extra property called “auto_increment”. It will also be selected as being the “Primary Key”.
- Likewise, all tables we make will have the last field named “created”, which will be of type “Timestamp”, and will have the CURRENT_TIMESTAMP checkbox selected.
The other fields that go between “id” and “created” will depend upon the table. In this case, they are:
- “title” of type “Varchar” – this will hold the title of any products in our table
- “description” of type “Text”, and length 255 – this will hold the description of any products in our table
- “price” of type “Float” – this will hold the price of any products in our table
Make sure your table has the same settings as the following:
Set fields for new table
Once you click “Save”, you should see your table name show up in the left navigation area in phpMyAdmin:
Verify new table created
If you click the tab named “Structure”, you will see the structure of the table you just created. Yours should look like this:
Table structure
If you click the tab named “Insert”, you will be able to insert a few rows into this table. Type the following two rows of data, and click the “Go” button. Notice that we are leaving the “id” field blank – the id for any row will be automatically created because we selected the “auto_increment” extra when we created the table.
Insert new rows into table
At any time, you can browse the rows you have stored in your table by clicking the “Browse” tab. This will show you a paginated list of data that is stored in the table.
Browse existing rows in table
The “SQL” tab allows you to run arbitrary SQL commands on the table. This is an example of asking for the database to show you a list of all the data in the table:
SELECT * FROM abloomberg_products WHERE 1
Run SELECT query on table
And this is an example of inserting a new row into the table:
INSERT INTO abloomberg_products (title, description, price) VALUES ('Another great cookie', 'This one has ginger schnapps in the mix', 7.49)
Run INSERT query on table
The following is how you would update an existing row in the table:
UPDATE abloomberg_products SET title='Dark Chocolate Cake', description='Only the finest 85% cocoa goes into each hand-made cake.', price=19.99 WHERE id=1
Run UPDATE query on table
And this shows how you would delete a row from the table, depending on the value stored in its id field:
DELETE FROM abloomberg_products WHERE id=1
Run DELETE query on table