Web Development
Tuesday, July 21, 2009

Professor: Good morning, everyone. I’m going to start. Any questions about anything from yesterday? We’ll be covering the same material. I’ll do a refresher this morning for some of the important points you’ll use today. We’ll also be doing some new stuff, so I want to make sure yesterday’s info has sunk in.

Let’s start from the blog. I just posted something we’ll cover later. This is information about managing a database.

Before we go over that, I want to review yesterday’s class. It’s Class 6 with the PHP examples. I’ll run through a couple of these things that will be very important when we’re dealing with databases. I believe towards the bottom there’s a page of data using forms with the get method, the post method, and links. This is critical to databases. That’s how we get data.

So where does data come from? It’s from a form or a link they click or some other trick we use to disguise how we store data or how we actually get the data.

I’ll run through these examples. We’ll use them today, so I want to make sure they make sense to you. We’ll start with the get method. Open that code in WinSCP. Remember to create your own folder for your own files. I’ll download that example from Class 6. That was in the “passing_data” folder.

So I have that open. I have to install Notepad++. So if you haven’t done that already, you should do the same thing. We have Notepad++. We have the files we’ll be dealing with. We have a folder you’ve created with your personal folders in it on your client side. I’ll open up the form_get.html. Let’s bring this example up in the browser.

This was an example where we entered in some data. [On screen.] This is what I got as the output. This page was proving we were sending data from what the user entered on one page to the next page where the information was extracted on the screen. Up at the top was some debugging left over from yesterday, so ignore that.

How did this work? We had this method equals get attribute. That was indicating to send the data up in the top section of the browser where it requests this file. It also adds data after a question mark. That section of the file name is the query string. You’re querying the server to give the file and a response to the data I’m submitting to the server. Anything after a question mark is the query string.

If you look at the data being submitted at the top, you can see it on your own screen, you’ll see the data is broken down to the first name equaling something and the last name equaling something else. There are two bits of information for the first name and the last name. Those are separated by an ampersand. I was saying yesterday that the word “first_name” in the name attribute of the input tag is critical for it being named first_name at the top.

If I named this “email_address” you’ll see email address at the top equals some value. So you have to know what you call this piece of data or you won’t know where to find the data. The name in the query string depends entirely on that input tag. That’s true for all input tags like radio buttons, checkboxes, etc. The name is exactly what that data looks like.

This is one page that’s just a regular XHTML page. I click submit and it submits the data by the “get” method. The next page receives that information. That file was process_data.php. It looks like this. [On screen.] There are three lines of code here. The third line is really just for debugging. The only interesting lines are the first two.

First_name is a variable I create in PHP. We’re setting it equal to the request variable that’s built in. It has all the data submitted to the server with any requests. When you’re loading something in the browser and asking for the file, I’m making a request to the server. Then the server makes a response. As part of my request, I’m saying that I’ve submitted data into the query string.

PHP, being a friendly language, has a built in request variable with all the data you may have submitted along with your request. So for this file I submitted the first and last name. So I know I can look for those two pieces of data in the request string. That’s what I’m doing. I want to look in the request and find the first name data and store it into my own variable called firstName. The same thing applies to the last name. So that’s the end of the PHP. The top section is PHP code. The bottom section is regular HTML except I have two PHP commands embedded in the HTML that prints out the variables for the first and last name.

Any questions?

That’s why you see the data you enter show up there. I mentioned yesterday that you can use one of three different variables that are built in. I can use “get” instead of request. That looks for the requests that are get requests where the method equals get on the form. Or I can look for “post.” That only looks for requests that have been made by the post. Post is a behind-the-scenes method of passing data.

I generally use request because I dislike having to remember the form I use. You just have to be careful not to have two requests with the same name happening at the same time. This form works for most practical purposes. I encourage you to use that. But you should remember that get and post exist that are more specific to the data requests.

This page I have here will accept any form submission and it shows what was put in the first and last name fields. So I have different forms that submit to the same page in different ways. Because this uses the request variable, it shows all the results of the form no matter what kind of request was made.

The other example was the form_post.html which was identical to the get one. But it uses the method equals post as its way of submitting data to the server. You can put in anything to the fields and click “go.” You don’t see the data in the address bar because it’s not a get request. The data has still been sent to the server but it didn’t show up in the address bar. That’s the nature of the post request.

Post requests are useful for larger chunks of data. If you’re uploading an image or video or a large file type to the server, you want to use the post form instead of the get. You don’t want huge amounts of data showing up in the query string of the address. Any questions about that?

That sounds nice but now we’ll get into more complicated stuff. Let me show the last example, which are the links. So this was the third way of passing data from one page to the next. When I click this link, the link has the file name I’m requesting and the data into the query string embedded within it. You can see it in the browser bar. So this link is also submitting data by a get request.

Student: A real-life application would be somebody logs in and then clicks on a link and then they remain logged in?

Professor: That’s more of a cookie example. I’ll show you examples today. If you have a dynamic website with all the same templates for your pages, it’s just one HTML file for every page on the site but loads different content. You then need to indicate to that page what content you want to look at, whether it’s the first page of content, second page, etc. So you pass that request from page 1, 2, 3, etc., in the query string.

Most dynamic sites with dynamic content, you use some kind of link form that’s passing data. There are ways to hide this thing from the user. But the technology is still the same. You may not see the query string but it’s there.

Let’s go back to the list of examples. I want to cover that last one, which we won’t deal with so much today which was passing data using cookies. I want to reiterate that point. It’s in the cookies folder. There was the get cookie and the set cookie page.

Getting a cookie, the page looks for the cookie variable. There’s a $_cookie variable. This looks for one of those. If there was a cookie on the site, you would see the value of that variable. There is no cookie for this site right now.

We can go to the set_cookie page. This script has commands to set a cookie for this site. So this page sets that. It doesn’t do anything else. If we go back to the get cookie page, you’ll see that the text shows up because that’s what’s been stored in the cookie variable. By going to one page on the site, we can store data that’s available for every page on the site. That’s stored semi-permanently in your client side.

We won’t deal with this today very much. I think that’s all the necessary stuff. You’re all familiar with include files from yesterday because you worked on them for half the day. We’ll get into databases now. What is a database? How do you use it? Those are fun things that go along with it all.

We have a database server set up for our class called mysql.[server name delete for security].com. This will be just like how we have a web server and now we have a database server. The credentials you need are the same as for the web server. Your username is [username deleted for security]. And the password is “[password deleted for security]“.

They’re the same credentials from the web server. So once you log in you should see a screen like this. This is a program called “phpMyAdmin.” It’s a software product that administers web bases. So if you want to update a database this is a good tool for that.

So we have first set up a class database called classdb. There are others but we’re not using them. On the left [On screen.] you can see the ones that exist. I’ll introduce the structure of the database. When you log in you see all the databases in the server. So we’ll click into get into classdb. Now you’ll be taken to the database page for THAT specific database.

So now you get to a page that has a list of the tables for the database. So every database server has multiple databases and every database has multiple tables. This is a list of all the tables for this database. There’s a list here on the left for more information for the table. Content is used for adding and deleting tables. If you want to access a table you would use the list on the left like a navigation bar. Let’s say I want to go to the “animals” table. I click that link now I see that table.

So this is a table that has three fields. You see here [On screen.] the field names.

So there are three bits of data in every entry — ID, name, and a created field which is the date that this data was entered. We log all the times that data was entered in the database. I’ll repeat how I got here. First I select the database server by going to mysql.[server name delete for security].com. That’s the first step. Then I select the database I want to administer. In this case, it’s clasdb. Then I select the table in the left tab section here. Then that brings me to a view of the table where the data is and where we’ll be spending our time. So I can browse the data in the table. I click the browse tab and that generates a list of the data that’s entered. You see there are rows of data, an ID and name, and the date it was created.

So this is how data is represented in a database. It’s a table and has rows. Every element is a row of data like an Excel sheet. Think of it as an Excel sheet of data. I can delete individual rows. There’s a duplicate of “alligator.” So I can click the X next to the “alligator” to delete it.

Feel free to delete animals if you want. If you want to edit any animal you can click the “edit” icon next to the row which takes you to an interface so you can modify the data. The ID is 17, name and date. So if I want to edit “striped bass,” that will edit the data. This will be SQL code called the sequel query. Don’t confuse that with the string. Now you see the results from the data.

Student: Is the ID automatic? Like the array has a 1,2,3. Is that the ID?

Professor: You can put it in if you want. But it’s automatic. We’ll set up the tables so they’re automatic. But you can override it if you want. It’s like the arrays.

So I’ll change that back to striped bass.

[On screen.]

Ok, so there are a couple of commands you can run. When you click the browse tab that shows the SQL command. You can run arbitrary SQL commands which allows you to run any command you want.

This is the command to select in the browse section to select all the rows in the section. I can change any of this code. I click “Go” and it returns the list of all the rows.

So another command I can do is “delete.” So if I go back to the list. What’s one you want to delete? Lemur. That has a unique ID. You reference it by the ID, not by the name. The ID is unique and the name could have multiple users. So use the ID for delete or modification.

I say “Delete.” Can you read that? Delete – from – then you give the table name. This is on the blog. Delete from animals where ID = 7. So we tell it to delete something from the table and the conditions for how to figure out which row to delete. That’s the where clause. This is the condition that specifies to delete a row or not. If the row has ID = 7, it’ll delete that row. Make sense? Now we know we’re deleting row 7. You hit “Go” and it asks if you really want to delete it.

Back to browse. You’ll see 7 is gone. So you may notice these aren’t stored in order. ID is not in order. The database has its own way of organizing the information. Don’t assume that the data is in any special order. So we sort the database. Let’s see a few other commands. Let’s say I want to change “Monkey” to another word through the code.

Student: Goldfish.

Professor: Ok. So if you want to change monkey to goldfish, you have to know the ID. So we have to know it’s ROW 4 or ID 4. So we’ll say here .. [On screen.] Update abloomberg_animal SET name = goldfish. This is on the blog. Don’t feel like you have to memorize it. Where ID = 4. So it’ll find row 4 and change the name and update the name to equal goldfish

Student: What’s the advantage for this?

Professor: You’ll be modifying this in the database and these are commands you have to run.

I click “Go” that updates 4 to goldfish. If I browse again …. sometimes it’s slow. So 4 is goldfish. We lost the monkey and gained the goldfish. Let’s insert a new row. I click the = tab again which allows me to run any command I want. The command is “insert.” Insert into given table name.

And here it’s different. You list the fields you want to add data for. So this I just want to add “name” for data. You give the values for the fields. We lost “monkey” right?

So I’ll put lemur. So you say “insert into” then the name. The fields are the columns in the table. The name field is the only one we’re dealing with. Strings are in quotes like in PHP. So I give it the value lemur.

It tells me I inserted one row. It took 0.0228 seconds

Student: Why wouldn’t you just edit the rows. Why wouldn’t you use “browse” and “delete or edit row” on each row? Why use code?

Professor: Same question as Doug’s question. We’ll do this automatically in PHP. So you won’t have your users modify your data. So you’ll have to protect your database and we’ll do this directly in PHP code which will run these commands

Student: Everything in PHP code will show up in the server.

Professor: Right.

Student: You can edit like this?

Professor: Sure. It’s faster to do that but you don’t want everyone to be able to do this

Student: They could delete everything.

Professor: Right. So we deleted a lemur. On page 2 – let’s go to the next page. It must be there somewhere.

There it is. So again it’s NOT in order. You can sort it by any field by clicking the field. Now it’s sorted in ascending order. Lemur is 62 on the next page. Those are basic commands for managing data in the database. That’s known as database CRUD. Programmers have a sense of humor ☺. CRUD stands for Create, Read, Update (modifying data) and Delete. So CRUD is all you’re going to be doing in your database. Questions?

I’ve outlined on the blog some of this stuff. There’s a tutorial for creating a table on the database. We’ll go through that on your homework later. For the assignment we’ll — scroll down to Class 5. This same assignment yesterday we converted it so it’s templates. Now we’ll get those products from a database. So we’ll move up. Rather than create a new page from scratch we’ll use this example. Bear that in mind. So back to this interface.

We’re done with this for now. We’ll move to PHP code.

So again the folder for Class 7 [On screen.] We have a couple of examples. The one we’ll go through now are basic SQL examples. There’s create, read, update, delete files, CRUD. Create has a form. You enter the animal and it stores that data. That answers your question. This is an example. If you had a site where you asked people to enter an animal … if I enter in a name like “Groundhog” I click submit.

Up there it says it inserted a new ID in row 64. Now that we’re the administrators, we can see the animals table. You should see the new number 64 on the second page. There’s groundhog. It’s now entered into the database. I’ll show you that this works with multiple commands.

Let’s say I want to read. This just gets a list of all the rows like the browse tab in the administrative interface. This generates a list of rows. At the bottom you have 64, which is the groundhog.

Update modifies the animals. We’ll learn how to do IDs so users don’t have to know that. I’ll update the groundhog to be something else.

Student: A ferret.

Professor: A ferret. So number 64 will modify the groundhog to be a ferret. It updated the animal. So now you can refresh the admin page and browse. You’ll see that 64 is now a ferret. Let’s delete that so I can prove all this works.

I’ll delete #64. I submit the query. If we refresh the page, ferret should be gone. Someone entered bad data, so I’ll delete “name,” too.

That’s the idea of these examples. I’ll start with the read example and go through each one. Download a copy of that folder for all the Class 7 stuff. Now I’m in Class 7. Go to basic SQL. I’ll open read.php. This is what that file looks like. [On screen.] This is the most basic SQL commands you might want to run. You’re passively reading information.

The first thing that happens is the require statement. You know what that does. It includes one file into another. This is the file that has the information for how to connect to a database. You don’t have to look into this file, but you can if you wanted to. It’s in db_info and a subfolder called db.php. This has the credentials for connecting to the database server. So your web server connects to the database server for data. So your server needs to know which database server you’re using. So these variables hold that credential information.

If you set up your own database on your own server, you have to make sure the credentials match the necessary servers. There are two functions, one to connect to the database and one to disconnect. That’s all in this file. I won’t go through those functions because you don’t really have to read them. But if you’re interested, you can go through them on your own. Go to PHP.net to look up what the functions do.

We can assume that all works. Then run the command to connect to the database. You’re passing the variables that were defined in the other file. There’s the username, password, etc. It gets the variables from the other file and uses the parameters. You don’t have to modify anything but the credentials.

The really interesting part of what I’m trying to show is this query. [On screen.] You’re creating myQuery which is the command you’ll run on the database. Our database server is a MySQL server. MySQL is the free open source version of SQL, which is a Microsoft proprietary product. MySQL is very popular. It takes commands written in SQL. So these are “sequel” commands.

Student: Do you need to have the DB and the read in the same file? Do you have to have both of those in the same file?

Professor: The db.php file? That has to be required into this file.

Student: Here you just have the query.

Professor: I include it in the file at the top. You can think of all those functions being included in this file at the top of the screen. I’m creating the variable myQuery. This is the query we used before to get a list of all the rows in the database. This is just the most basic query you can do. It gets a list of every row. The (select) is to retrieve a list of rows. It could be a list of fields. So I could say just retrieve the name field. “Star” means all the fields. If I wanted two fields, I would separate the names of the fields with a comma.

Recognize that the first area is for which field you want to retrieve. In this case, “star,” means all. If your database isn’t huge with hundreds of fields, you can just repeat all the fields.

Then we have the table name. Select all the fields from this table. Remember that “where” is the conditional that indicates which rows to get. 1 indicates where every table and row matches. So this says to match every single row in the table. The query matches a query versus a row. If I put id=5, that would only look for the row where the ID matches 5. That would only return one row because all the IDs are unique.

Just like in CSS, IDs are unique. It’s the same for variables here. When you say “WHERE 1,” that matches all rows. This is good form.

This is the finding a variable. It’s not doing anything with the variable yet. This now runs the command. [On screen.] This calls a built-in function of PHP called mysql_query. You tell it to run a command and this function automatically does that command. It puts the result of the command into the variable we’re finding called “result.” So far so good?

Result is like a complicated variable. It’s not a simple variable. You have to use special functions to look through the results. You might look through an array or print out the array. You have to use special elements to loop through the result variable. You can think of it like a more complicated version of an array.

So first we want to make sure the command worked and we get something back. That’s what the “if” statement is for. It checks to make sure everything works. This doesn’t mean there’s any data. It’s just the command was successfully run and the database responded.

Then we loop through each row. [On screen.] We talked about while loops. That continues until a condition is no longer true. A while statement takes one parameter which is to check every time you loop. This evaluates to either true, so it continues indefinitely. Or it can be false, which means it never runs because this condition has to be true to run. If we code it true, it runs forever. If we code it false, it never runs.

But if we make it sometimes true, sometimes false, it loops one by one until there are no more rows. Each row goes into a variable called “row” so we can use “row” to refer to that data. It continues only as long as there are more rows in the results. Any questions?

We have a connection to a database being made. We have a query being assembled and stored in a variable. We run the query using a built in mysql_function for PHP and then a looping of the results row by row. There may not be any results, and the loop wouldn’t run.

We know there’s an ID field, name, and created field. So this code is getting that data and storing it to different variables. So the row is a normal array. You might recognize the brackets as being associated to an array because there’s a word there instead of a number. That’s an associative array like we created yesterday. This is exactly the same concept.

You get the ID field of this row array. You’re storing that ID in what I call the variable animalID. AnimalName has the name field. AnimalCreated has the created data. So then I echo out, which is printing out, the data separated by dashes.

This is how you insert one variable by a string. I showed you before by concatenating. That’s how you would have done it yesterday. [On screen.] I recommend you start to do it the way I’ll show you now. It’s more compact. This tells it to insert one variable into this string. You surround the variable with brackets and it inserts the variable. It’s more natural to write it this way.

Every row echoes out each piece of data. At the end of the script, we run the function. That’s it for this example. Any questions?

We’ll do a lot of this kind of stuff. We have to become familiar with the syntax of doing it. That’s the gist of it. You get a list of things and print them out one by one. That’s reading. You’ll eventually attach different conditions to the query. Maybe it’ll be for a particular row or a set of rows. You could use the less than symbol for the most recent rows. For now we’ll just stick with the basics and then eventually get more sophisticated. That’s it for this example.

The next example is creating a row. Let’s open create.php. We’ll bring that up in the browser. [On screen.] The first thing you see is this form. Nothing happens until the user enters data. Down at the bottom you’ll see the HTML document. This is a regular HTML document. It has a form in it. The form uses the get method. So the data shows up in the address bar of the browser. It takes one text field with the animal name. So that’s all you should see when you load the browser. You’ll see the animal name text.

I’ll submit some data. You click submit. The data showed up at top because that’s the form method equal to get. That’s the first thing you do when you come to this page. I’m taking you through the flow of the code. There’s a chunk of code up top. Let’s see what that does.

When you load the page, it includes all the database information. It connects to the database. In the request, it looks for the field “animal_name.” That’s the name we gave the field for “submit.” There are two states that this script is working in. It could be processed when you hadn’t submitted data. Or it could be processed when you had submitted data and it requests a row.

If there is something submitted in animal_name, then and only then it runs the query to insert the data into the database. So the script checks for an animal name every time you come to the page. The first time, you didn’t enter a name. So it didn’t do the query because it was empty.

But if you did submit an animal name, then it’s not empty. It will process the lines of code. Does that flow make sense to you?

If there was an animal name submitted, we create a new query.

We store it in the variable “my Query” which looks like this. Insert in the table name. The fields we want to add data for. And the value we want to put in the fields. You see in the value I have quotes around the value I put in the field then I insert the variable. So the name gets put in this spot. So that replaces “monkey.” Monkey is around quotes because it’s a string, not a number. It looks complicated because you have parentheses – quotes – brackets – but this is what you want in the end.

That’s an insert command it takes the parameters of the data and the fields and values you add in the fields. Then you close the string with a semi colon. Then you run the variable with the select query. The query looks like this line no matter what kind of query. Here we make sure the query works and the database didn’t throw an error. If it works then we do the built in query function that gets the ID of the last inserted ID. That’s the function use. I just concatenate that onto the message. Then you submit the form and you see at the top “inserted new row with ID.”

It’s not a lot of code actually. So run through it on your own

Student: If it is empty then there’s nothing there. If what’s empty?

Professor: The animal name that may or may not have been submitted and put it in a variable called New Animal. So new animal stores that data. We check to see if it’s NOT empty. That’s the exclamation point. The exclamation is a “NOT.” And that’s empty.

Now we look at the update.php. This is really just the same kind of thing. It includes the database and it looks for the ID and name. The ID is stored in a variable called “animal ID” and the name is stored in “animal name.” If they’re NOT empty we run the update and we make sure it worked. If it worked we print out a message, if not we print an error. That’s it for that one. Last but not least is the “delete” which is almost identical. It has an ID and stores it in the variable, makes sure it’s not empty and stores it. Questions? This is all CRUD. It’s database CRUD. It assemblies a query, stores it and runs it.

There’s not much difference in these files. That’s it for these few examples. Let’s take a 10 minute break and come back for more!

[Break]

Professor: So everyone back? Is there someone missing? We’ll wait another 2 minutes.

Student: The projector went out.

Professor: Oh.

So what I want you to do now, we’ll change the structure of the class. Instead of lecturing, I want you to get some hands-on experience. This is a large topic. So I want you to follow some instructions on the blog about creating the table you’ll use to modify the assignment you did yesterday so it’s running from a database.

This is step-by-step instruction for setting up a table in the database. If you go to the assignment yesterday from Class 5 the idea is to have these products pulled from a database and not the array. So make a copy of the files you did yesterday. Go through the latest post which explains how to set up a table. All the details are here but I’ll walk you through it. Once you have the table set up you’ll modify the code from the Read.php to pull a list of the products and display them on the page.

Is that clear?

So I’ll walk you through creating a table and the instructions are here so you can follow along.

To start, we’ll reiterate how we start. It’s mySQL.[server name delete for security].com. You log in. You see the list of databases on the left. You’ll all put your tables into classdb. So click that database. That shows you a list of the tables in the classdb.

So we’ll make a table called your first initial, last name, underscore, products (example: abloomberg_products). It’ll be your own first initial and last name. So start by creating a table. This is the database view that shows all the tables in this particular database.

Go to the bottom of that view. You’ll see this “create new table on database” form. This is where you enter in what the table is called. If you get lost in this interface, there are breadcrumbs up at the top that indicate where you are in the current site. If you want to go back to the top, click back to the server name. That brings you to the root view of all the databases in this database server. Then you select the database you want. Then you can walk your way back to where you wanted to be. Don’t feel like you’re lost.

Go back to the database view where you see all the tables. Go to the bottom of the screen. There’s the section where you enter the name of your database. So enter your first initial, your last name, underscore, and then whatever the table name is. In this case, the table name is “products.” That holds all the products. The number of fields is the number of columns in this table. Every table has an ID and created field. In between those two are whatever relevant tables you want. In this case, we want the title, description, and price of the products. So there will be a total of 5 fields. Click “go.”

It chugs along and you get to a page that looks like this. [On screen.] This is the page you enter in the details of what each field holds. What kind of information will it hold? What are the special properties for each field? This is true of every single table we make until you become an expert, these are the conventions I think are easiest to follow. The first field is ID. The type of data it holds is integer, which is INT. This will hold the number that uniquely identifies this particular row.

If you scroll to the right, it has an extra property in this “extra” field. Select auto_increment. That means the field will be added incrementally. You don’t have to manually enter in 1, 2, 3, etc. It’s automatic. Then click the radio button under the key-looking picture. That indicates this is a primary key. That means this is the unique identifier for every row. We’ll get into more of that stuff after lunch and what that really means.

To reiterate, the ID field for every table always has the same setup. Always called it “id.” Always make the type INT. And always have it set to auto_increment and always have the primary key selected. That’s always true of every table we make.

Another always-true thing is the “created” field is the last field of the table. So call it “created.” This is the date that the field is entered into the database. The type will be TIMESTAMP, which is a way of storing times. You always want to make sure you check the “current_timestamp” box. That tells the server to automatically put the current time in. Whenever you create a new row, it puts the exact date into the field. That’s all you have to do for the “create” field.

These are all outlined on the blog.

The other fields are up to us.

Student: The timestamp is the time for the actual creation? It’s local?

Professor: It’s the time that the database thinks it is. It just needs to be right on the server. We won’t be using this field right now. But it’s a standard I want you to get into the habit of using.

The second field is the title. That will be what stores the title of any product. Leave this as VARCHAR, which means variable character. That means there’s text in it. The maximum number of characters in this field is 255. So just leave that as the maximum. We’ll just say the title of a product won’t be longer than 255 characters. If you wanted it to be longer, you could change it, but for our purposes this is adequate.

The third field is the description. The description may be longer than 255 characters. So don’t use the VARCHAR because it has the 255 limit. We want to use TEXT which is a more expandable way of storing text. It’ll be as big as the text you input. You don’t have to indicate a maximum number of characters.

The fourth field will be the price. Each product has a price. That should be a decimal value. It might be $3.99 or $5.99, etc. That’s usually called a FLOAT in computer terminology. It means you accept decimal points. We put the price as FLOAT and we don’t have to set any length values.

So you have the ID, which is the unique identifier. You have the title, which is up to 255 characters. You have the description, which is as long as we want it to be. The price has the decimal point float. The final field is the timestamp. Any questions?

We just click “save” down on the bottom left. That stores your table into the database. What you see on the screen is the SQL code. You could run that query if you wanted to do it on your own. You don’t need to create tables in PHP code because you do it in the admin base and then use that.

Look on the left column. Your table should show up somewhere there. If you click on the left-hand table, it brings you to the general structure page of your table setup. Make sure everything is correct. The ID is a type INT. The title is VARCHAR. The description is TEXT. The price is FLOAT, etc. Make sure you have current_timestamp and automatic increment set up. This is all on the blog, too.

Down here in the index section, make sure the primary key is on the ID field. So that’s the unique identifier for every row.

Now we have the table set up approximately how we want. Any questions?

Now we integrate this into our PHP code. It’s pretty much set up to accept products. Now we’ll insert a few products manually. Click on the “insert” tab. That brings you to a form where you can manually enter data into this table. By default, it shows you two rows of data. You enter in the title, the description, and price of the two different products. You don’t have to enter in the ID or the “created” fields because those are automatic.

Let’s enter the title of the product. It can be whatever you want to call it. I’ll use a silk handkerchief. Then enter a description. [On screen.] It can be whatever you want. Just put a decimal point price. I’ll put in 3.99. And then repeat that for a second product.

Make two products. Click “go.” The “go” button you want to click is under any of the sections. That “go” button inserts the two rows into the table. You can click either of those buttons. So now you’ve inserted the rows. It gives you feedback that you’ve inserted two rows. The latest ID of the last row is 2. And then you can see the SQL query if you wanted to run that query yourself manually in your code. [On screen.] Generally, we don’t want to run it exactly the same way as it’s coded here, but this gives you an idea of how the SQL language is structured.

Now go to browse. That shows you the list of data in the table. Click the browse tab. You should see that you now have two rows entered into your table. All the fields should be filled. ID is automatically numbered as 1 and 2. That’s auto incrementing. The title field is entered as well as the description and price. Make sure the decimal point is working correctly in the price field.

Now we can read the data and show it in our PHP e-commerce table. Any questions about this before we go to PHP?

I won’t use the framework of the HTML pages we’ve made. I’ll sure you a pure text version of how to get the rows of data and show them on the page. Then you integrate that into your e-commerce assignments.

The first thing we do is make a copy of some code I’ve created. In this case, the read.php is what you’ll want to copy. That is the relevant file for this assignment. Make a copy of that whole file. Save the file in your own folder. I’ll save it in a folder called “ecommerce.”

Create an e-commerce folder in your own folders. Then save this file as index.php. Everyone should have a file index.php in a folder of your own called ecommerce. Then make sure you have the dv.info folder. That has to be in your own project folder, too. So copy and paste that folder from Windows Explorer. So open up Windows Explorer. This is how you browse your files on your hard drive. Go to the folder you created.

Go to the Class 7 basic_sql example folder. Copy the dbinfo and paste that into your own folder. So we need that subfolder in every single folder of our database. Let me show that again.

We start out in myclass7/basic_sql folder. Right-click on dbinfo and click copy. Then navigate to your own folders. Click ecommerce to get into folder. Then right-click and paste that dbinfo folder into your ecommerce folder. You want it to be in whatever folder you’re using for this project. In my class, it’s the class7/ecommerce. That’s where the index.php folder will be. In that folder is the subfolder for ecommerce.

Professor: Set that up now. Should I close the door? [noise outside.] So now … I’ll come help you.

Ok. So you should have now your own indx.php file which is a copy of my read.php file. Mine isn’t highlighted correctly. You have this problem? Not sure what that is.

If I close and open, now the right color code is there. The text editor has some bugs. So now what we’ll do is upload this to the server. Upload it to your OWN folders. Class 7 in my case, ecommerce. [On screen.] now we have an ecommerce folder and your own server folder. Just like for all the other buttons. Now make a new tab and go to [server name delete for security].com [On screen.] You’ll see your subfolders. Click to get to the ecommerce folder. You should see a list of animals. So we copied my example and copied to a subfolder. Let me give you some time to finish. Now we modify it so it reads from your OWN folder not my animal folder. Anyone need help?

So to modify this so it reads from the products table you modify the query. The query is the command that indicates what you want from the database. So if you don’t tell it the proper thing you won’t get what you want. You tell it which table to read from. You select the product table and replace it with your own product table. So this will ask the database to get all the rows and fields of the products table you created earlier. So now we save, re-upload it to the server.

Go to the browser and refresh the page. You should see a list of products. Almost ….

Right. Yes. Good point. So mine shows up blank. It has the ID and created. But the rest of the details are different. So we tell it which fields to look for. It’s looking for the name, not the description and product. So instead of animal ID, let’s say PRODUCT ID. So rename animal to product and the next is “product Title.” That should look for the title field NOT the name field. When we set the table we have ID, price, created field. We don’t have “Name” as a field. It only knows to look for the fields we created. The next is changing “Animal” to “productCreated.”

Student: Lower case?

Professor: Get in the habit of using lower case. So product title is = to row, field title. Created = row and created. Then you want product description. = row description. Copy this template in a variable called “product Description.”

Student: Are we including price?

Professor: I forgot that. We have 5 fields. So let’s make a new variable called productPrice and set that equal to row field price. This row array that we use as you see that’s the row we retrieve from the database. It looks row by row and each row is put in the variable “Row” which is an array that is associated with a field name from the database. It fills in the ID that has the data that’s in the ID field. Same thing with “Title.” This is easy to use. You indicate the row you’re looking for and it retrieves the value for that row.

Now I’ll move to product title, description, and price. So replace animal ID with product ID and animal title with product title and animal created with product created. I’ll add the variable price and description.

Every variable you insert in the echo command needs to be wrapped in curly brackets before and after the variable. So I’ll upload this to the server. If you have trouble with your code you can copy mine.

Ok. Questions so far about the theory of what’s happening?

So let’s load that in the browser. You’ll hopefully see it. Line 24. This says I have an error on line 24. The error gives you SOME indication of where you made the mistake. This is line 24. I actually entered in the number one. Let’s take that out. Typo.

So now I have proven that I can retrieve that data from the database. So it has the ID, title, description, price and created date. Those are all accurately retrieved from the table. You have an error.

Student: It shows the animals still.

Professor: In the code make sure the name of the table is …

Student: I did that.

Professor: Maybe it didn’t upload.

Student: Can I write a query to fix the spelling error in the product description?

Professor: That’s the update.

Student: This is … [helping student]

Professor: You got it. If you want to modify this with PHP code there’s code for updating. So let me show you the loop here that we’re doing.

Again this loop loops row by row of the results that are retrieved from the database. This command loops through each row one by one until there are no more rows. Each one creates a variable and over writes the variable for each row and outputs text associated with each field. That’s the section that outputs data.

If you view page source in the browser, it’s not a valid XHTML. The source looks like this. [On screen.] There’s break tags. But this is not a valid HTML document. So your job is to create a valid XHTML document and use it for your Ecommerce. So rather than outputting the text you want a full XHTML document and position it where it will output the products. So make the output in the right place after you write this in appropriate XHTML. That’s what I want you to do right now.

[Students working.]

Professor: I’ve posted more information on my blog for other posts I had posted previously. So a lot of the information is now on this post in case you want to reference something. There’s an intro to the concepts of the database. What it means to be a database, etc.

Another post that has diagrams for how you create tables, link tables, modify fields. This is a post about select statements. This shows you how to go further into that concept. Debugging. How you figure out your errors. The messages you get for errors aren’t the most intuitive.

Making time stamps explains how to create the data for your rows and making that user friendly. You can make it into a format that says “September 3rd, 1999.” Not just a technical looking time stamp.

Next post is about pagination. You don’t want to display all your data in one list. Sometimes you want them to be manageable for example the first 10 results on one page, the next 10 on the second, etc.

This is a beginning explanation on how you would break up your results into multiple pages. So that’s the latest post for you to read at your leisure. That’s it.

[Students working.]

Professor: Anybody stuck on the assignment? Don’t be afraid to ask for help.

[Students working.]

Professor: It’s 12:50. Let’s take an hour lunch break. I’ll show the relations between two different tables in the relational database. So if you have two sets of data, how do they relate? How do you mark them, etc.? I’ll be discussing that at 2:00.

[Lunch break.]