Step 2: Create Your Products Table

Step 2: Create your products table

You need a products table that has the information from your products table. It is difficult to add photos to a database, so we will only store the following:

•Name

•Price

•Description

To make it easy to work with the database, a unique numeric ID will be added to each row. So, you will have another row:

•ID

You need to know the types you will use. For this project, I give each column a different type:

•ID: bigint – an integer field that can get extremely large

•Name: varchar – A character/string field that can vary in length (as opposed to a char field which sets all values to the exact same length by padding the values with spaces)

•Price: decimal – A numeric type that allows you to set exactly how many decimal places will follow the decimal point

•Description: text – A character/string field that can be extremely large (varchar is often limited to 255 characters, which isn't long enough for a good description)

Notice that two fields have a length setting.

•In this lab, I set the size of name to 50 characters – change that if you need more characters in your product or service names

•In this lab, I set the size of price to 5,2 (for a maximum price of 999.99) – change that if you need higher prices

To create the table:

•Click on Databases

•Click on your name (which is actually your database). You will see a Create Table pane.

•In the Create Table pane, type in products as the name of your new table

•It will have 4 columns: id, name, price, description. If you really want to add more columns, you can. In this assignment, I will only show you four columns

•Click the “Go” button to continue.

The only thing you really need to change here is the size of the name and price columns. Pay attention. I left out columns you won't use. Also note: When you select “primary” for the index, it will pop up a modal box. Just click “Go” to get back to editing.

NameTypeLength/ValuesIndexA_I

idbigintPrimary(checked)

namevarchar50

pricedecimal5,2

descriptiontext

Now, click the Save button and it will create your table. If your table doesn't look correct, you can drop it by clicking the “drop” link below it and starting over.

Step 3: Add rows to your table

Click on the Browse button to view the data in your database. It is empty, so the display will be empty. You need to add rows to your table. That is done by clicking on Insert, so click on Insert.

This looks like a mess. You will see your four columns followed by the type, then a function, and then the value. Leave id blank. Fill in the name, price, and text description and then click Go. Ignore the fact that there is another entire form directly below it. That is so you can insert more than one row at a time. Start with one and make sure it works.

After you click Go, you will see the insert statement. DON'T CLICK GO AGAIN. It ran the statement. This is just showing you what it did.

Now, click Browse and you will see the data. What if you clicked Go twice? You may see the same entry twice. Click the Delete link on the row to delete. Then, go back and enter your other two products.

Once you have a table named “products” with your three products/services entered, you are done with the database side of this lab. You need to work on the PHP side of this lab.

Step 4: Connecting PHP to MySQL

The following PHP will connect to your database. You will need to change the username and password.

Remember that your database name is the same as your username. Everything else should remain the same. This uses the MySQLi interface. If you want to use the PDO interface, you can, but the examples here will need to be updated.

$db = new mysqli('localhost', 'MyUserName', 'MyPassword', 'MyDatabase'); if($db->connect_error) die(“DB Fail: “.$db->connect_error);

Hopefully the connection never fails. If it does, the “die” function will print out the error and stop PHP from running.

The next step is to make sure you can query your database. The following code runs a simple query and prints the results (don't forget that if you copy/paste you will end up with code that doesn't work):

$result = $db->query(“select * from products”);

while($row = $result->fetch_object())

{

print_r($row); }

I suggest saving the code to connect and the code for the query in a single test.php file. Then, go to

and see the output. Alternately, if you are at the command line in SSH, you can see the output by typing: phptest.php

If this works, you should see the data from your three products in your products table. That means that your database connection works and you can delete the test.php file.

I like to keep things clean and neat. So, I create a file named db.inc and I type in the code to connect to the database. When I need to connect to the database, I use: require_once('db.inc');

Why “require_once” instead of “include”? I want it to error out if the db.inc file is missing. I also want it to include the file only once – just in case I include it from more than one place.

COMMON MISTAKE HERE: I gave you example code that queries your products table and prints out the contents of the table. Do you want your website to print all of that out every time you connect to the database? Of course not. That code was just used to test your connection to the database. It is not required to actually connect to the database. Don't use it in your actual website. Only use the “$db = new mysqli...” line and the “if($db->connect_error...” line in your db.inc file.

Step 5: Dynamic Product Listing

You have a products.html page. That page has a product listing. You are now going to delete the product listing inside the products page and replace it with dynamic code. This can be stressful. My goal is to make it as easy as possible.

For the overview, I will use a very simple example. Imagine my website uses a list for my products:

ul id='productlist'>

liBucket Wheel $12.95: A safe wheel for all small animals.</li>

</ul

My goal is to remove the actual product listings and replace it with PHP code.

The following code will query the database, just like I did before, and print out the data in a while loop.

ul id='productlist'>

<?php

require_once(“db.inc”); //Connect to the database $result = $db->query(“select * from products”);

while($row = $result->fetch_object())

{

print “<li”.$row->name.” ”; print “$”.$row->price.”: “; print $row->description.”</li>”;

}

</ul

Now, if I have 50 products in my database, I will get 50 products listed in my products page. Further, everything is dynamic. If someone updates the price of a product, the website instantly changes.

This lab removes images from the product listing. If you really need an image to keep your design looking reasonable, use the same product image for each listing. We will fix that problem next week.

That is all you have to do for this lab, but many students will have trouble reconfiguring their design. So, the following walks through a popular product listing design that should help.

If your listing is vertical – each product is below the previous product – it is easy to make the design dynamic. Just keep adding rows. If your design is horizontal – each product is to the right of the previous product – it is hard. You quickly have to scroll left/right to see the listing. Nobody likes to scroll left/right. What you want is a listing that automagically moves down a row when the next product goes off the screen.

You likely used tables to make your layout. Throw away the idea of tables right away. We won't do that. We will do something else that will look like tables. Imagine that each product listing is a card. If you place cards in your display, the default layout is flow layout. They will start on the left and the next one will be placed to the right. When you hit the side of the screen, they start on the next row down. That is what you want. If the cards all look the same, it will look like a table layout – but it is actually a flow layout. The trick is in designing the cards.

The cards are divs. The following CSS defines a div of class product:

div.product { display:inline-block; vertical-align:top; width:10em; height:20em; }

That div will have the layout of a block, but flow inline. The top of each div will align to the top of the next div. The width is 10em (resize that if you need to). The height will be 20em (resize that if you need to). Place each product inside <div class='product'> and they will show up as a series of cards. Complete the CSS with margin, border, padding, colors, etc... to make your website look professional.