This is the second part of my tutorial on how to create a database with PHP and MySQL. If you missed part 1, you can view it here. So far we’ve created a new database for our bike shop, and created a user who can access the database. Now let’s make a table for our products.
The first step is to plan out exactly what information the products table needs to keep track of. For the bike store, we’re only going to keep track of a few things:
- The name of the product
- A description of the product
- How much it costs
Go to your CPanel in Siteground and click on “phpMyAdmin”. PhpMyAdmin will open up in a new window.
Inside PhpMyAdmin, you should see the new database you created on the left hand side. Click on it. Now we can edit the database.
Our database is looking a little empty. Click “Create Table”. Enter “products” for the table name. Under Structure is where we add columns to the table. The first column in any table is usually the id. Since you might run into a situation where you have two products with the same name, we give each item a unique id number that we can reference later.
Name the column “id”. Choose INT from the dropdown menu, and give it a length of 11. Next to Index, choose Primary. This tells MySQL that this column is going to be used as the unique reference number we just discussed. Also check AUTO_INCREMENT. Auto increment does exactly what it sounds like. Every time you add a new product to the table, it will automatically assign an id that is one higher than the last product’s id. This will save you the trouble of having to worry about assigning an id to your products.
Click Save at the bottom of the page, and you should see the products table show up on the menu on the left.
Now add your other three columns to the table. We’ll need product_name, product_description, and product_price. Set the data types and length as shown in the image below. Everything else can be left as the default value
You might be wondering what’s up with the Types. Type tells MySQLwhat kind of data you expect that field to hold. Int is for integers, varchar stands for Variable Character Field, and is used for short bits of text under about 100 characters, like name, address, etc. MediumText tells MySQL that you expect to have a couple paragraphs of text in that field. And Double is another type of number containing two decimal places, perfect for our price field. Figuring out the right type of data to use takes a little practice, but a little googling will get you the type you’re looking for.
Okay, let’s add some products to our store! Click on the Insert tab at the top of PHPMyAdmin, and enter a few items. Don’t worry about adding anything to the id field, MySQL will do that for you when you save the item. Click “Go” to save each item.
Once you’re done, go back to the Browse tab and check out your new product list. You’re in business!
Now that we’ve got all our products in the database, we’re ready for the third and final step- displaying the products on our web page. Head on over to part 3 of the tutorial to get started!