« « Tutorial: How To Create A Database With PHP & MySQL – Part 1 | Tutorial: How To Create A Database With PHP & MySQL – Part 3 » »

Tutorial: How To Create A Database With PHP & MySQL – Part 2

Feb 23, 2014 by     No Comments    Posted under: PHP, Tutorials

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.

go to phpmyadmin

Inside PhpMyAdmin, you should see the new database you created on the left hand side. Click on it. Now we can edit the database.

choose 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.

create table

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

mysql-table-design

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.

mysql-insert-product

Once you’re done, go back to the Browse tab and check out your new product list. You’re in business!

mysql-product-table

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!

Go to part 3 >

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>