Mandate Analysis and Data Model
The app we will build together here is a simple one. It is called Hello World! It takes names, provided by the user in a simple text field, and displays a greeting message to all the names found in the database. Pretty simple.
You can find a working example of the app here: Hello World!
To make this app work, we will need a very simple table to store the names. Here is the data model for this table which I called HelloWorld:
You notice that I have included a third column with a data type you have not seen yet. The value of a timestamp column is automatically provided by the system from the date and time when the entry was created. For this app we do not really need this column, but it is very useful. So I made an example here to show it to you.
Now, let’s start thinking about the queries that our app will require. First we will need to collect some data from the user, and store it in the database. What is the SQL query to do this? Also, we will need to get the data from the database and show it to the user. What is the query for this?
Remember that INSERT is the SQL command we use to add entries in our databases, and SELECT is the command to get the values from the columns. So our app will need an INSERT query, and a SELECT query.
The INSERT will look like below. The question mark will be replaced by what the user types in the HTML field. We do not yet know this while we are developing the app, so we indicate it with a question mark. (You’ll see later it will become clearer.)
INSERT INTO HelloWorld (name) VALUES (?);
And the SELECT query will look like this:
SELECT ID, name FROM HelloWorld;
Notice how the names of the columns in the queries match the names in the data model above. The names of things are SUPER important. You want to make all of them match to make sure the values follow the correct path through the whole system. This is fundamental to the successful completion of your apps.
You will see below that these two basic commands will be represented in the code of the app.
At this point, you need to create the table we just described. You will need it for the app. So go to Adminer, and create a new table in your database using the data model above.
I am not going to discuss how to do this here as it is already explained in the previous tutorial you completed.
When you are done creating the table, come back and continue with this tutorial.
All the apps built in this course will follow the same logic. They will be built using the same techniques, and infrastructure which I describe here.
If we look at the flow of the data in the system, it looks like this:
HTML → JS (AJAX) → PHP (SQL) → DB
JS (AJAX) → PHP (SQL) → DB → PHP (JSON) → JS → HTML
You see that the data passes back and forth between the HTML, JS, PHP and DB. We will need to write the code for the app to make sure the data gets through all this smoothly. Following the data like this through the app is the most important tip to make sure your apps work properly. Here is a schema of the architecture of the system.
There are other ways of building apps. But all the apps are going to be built like this in this course. This may seem abstract to you, but understanding how the elements of the apps are related is going to help you build your apps, and it is going to help you debug them too.
For your reference, I will define all the acronyms you find in the chart above. If you want more details, look these up online. But you don’t need to absolutely know all of these by heart.
DB Database. This is not an acronym, but it makes it easier to write the word database in a graphic.
SQL Structured Query Language. This is the language we use to talk to the database.
PHP PHP: Hypertext Preprocessor. This is a scripting language that runs on the server. We use it to send the SQL queries to the DB and interact with the JS front-end.
HTML Hypertext Markup Language. This is the markup language used to describe the content of web pages.
CSS Cascading Style Sheet. This is a language used to describe the appearance, layout, and basic behaviour of the content of a document. It is mostly used on the web, but not exclusively.
Let’s Build an App
The first thing you’ll need to do is get the files for this app from my GitHub repository.
Click on the green “Code” button, and select “Download ZIP”. Then extract the content of the ZIP archive, and put the HelloWorld/ folder somewhere inside your folder for this class. The files look like this:
You notice right away that there is an index.html file in the folder. This will contain the user interface of the app. You also notice that there are two sets of files named after the SQL commands we will use: insert_item and select_items. For both commands, we have a JS file and a PHP file. The apps I show you are all built in such a modular fashion.
Finally, you notice that in the php/ folder, there is a third file called db_connect.php. This file will contain the code to connect to the DB. We will look at it later.
You can ignore the README.md file. It is used by GitHub but it does not serve any purpose in your app. You can even delete this file, if you want.
Pay attention to details!
In what follows, I will go through each file and explain what you need to pay attention to in order to make this app work. I will explain the parts you need to focus on, and the parts you can ignore. It is important to follow and to be careful of the names of things. A computer does not know what “firstName” means. It only knows that it is a series of specific characters, and when it recognizes them, it makes the connection. For a computer, therefore in programming genrally, firstName is not at all the same as first_name or firstname or 1stname or nameFirst. A human (who understands English) can easily understand the similarity between these, but not a computer. To write code that runs, you need to be precise.
Another thing to keep in mind, most characters and symbols mean something in programming. A " is not the same as a “ or a ’. The number 0 is not the same as the letter O. Spaces also mean something in programming. In JS code for example, writing let p = 1;, does not do the same thing as writing letp = 1;. One creates a new variable called p and assigns it the value 1, the other assigns the value 1 to a variable called letp.
Alright, so let’s make it happen!
We will begin by looking at the feature of the app where we collect a name from the user, and store it in the DB. This part concerns the INSERT query. We will look at each file one after the other following the data flow indicated in the architecture section above.
We will start with the HTML document. It looks like this:
This should look familiar to you as it is a simple, plain HTML document. The first thing you need to notice starts at line 17. This block will contain the results of the SELECT query. We will look at this part in more details later. But for now, you can see that at line 18 we have an element #listContainer which will be filled with the names found in the DB. And at line 19 we load the JS file called select_items.js which will handle this feature of the app. Like I said, we will look at these later.
The module we will build first is the INSERT query, and this starts with the form at line 24. I do not know how familiar you are with HTML forms, so I will write a short intro. The form element in HTML lets you collect input from the user. It can take all kinds of input: checkboxes, text fields, drop-down menus, password boxes, etc. It can also include invisible fields that have values created by the system. The values of each input of the form will be passed to the server. In our case, to the PHP file. More information about HTML forms.
Here is that section of the file again, with some highlights.
In this particular form, at line 25, we have a simple text input. The user will write a name in it, and this name will be added to the table in the database. Notice how the name attribute and the id attribute of the input both have the value "name". This will be important when we look at the JS code below. (Be careful not to confuse the attribute name with the value "name". The attribute comes before the =, the value comes after.)
The name and id you assign to the input here should match the name of the column that will receive the value in the database table. If you recall the data model, we do have a column called “name” in the DB. And this is where we will store the data collected in this HTML form. Following the data and matching the names will be the most important thing you need to focus on while developing your apps.
You must also remember the id of the form itself (line 24): addForm. This will come up too in the JS file.
The last thing to notice in this file for now is that we load the JS file for the INSERT command (line 28): insert_item.js. It is important that this comes after the form.
Now let’s look at this JS file.
The first JS file we look at handles the collection of the data from the HTML form, and sends it to the PHP on the server. The file looks like this.
Even though it may look like a complex file, it is quite simple, and most of it is standard and will remain exactly the same in all the apps you build. So only focus on the lines and sections I describe, and leave the rest as is.
The first thing we will look at is at line 5. Here we create a function. This is the main container of the commands we use for this part of the app: the insert feature. Notice that the function created at line 5 is called at the end of the file, at line, 36. All the JS files will be built like this: create a function, then call the function. This allows us to call these commands from another file. You will see this happen later.
Then, notice line 7. Here we make a reference to the HTML form, you may recall the getElementById() function that selects an HTML element. Remember the name and id we gave the form in the HTML: addForm. Here we make a JS variable that contains a reference to this form. This will allow us to send the content of the form, the values entrered by the user, to the PHP.
At line 14, we determine the PHP file that we will send the data to. In this case insert_item.php. I will describe this PHP file in the next section.
The block of code between lines 21 and 33 is the AJAX code. This is where we open a connection to the PHP file (line 21), determine what we do during the interaction (lines 22 to 32) and finally send the data to the PHP (line 33). Since the server will only send us back a confirmation of the INSERT, this section is relatively empty. The only other element to note is at line 30 where we call the function, we will describe it later, that refreshes the list of names.
That’s it for this file. Next we will look at the PHP which receives the data from the HTML through the call at line 33. But before, you need to setup your DB connection.
Before we continue with the PHP code, we need to make a small a parte. You will need to update the values in the file called db_connect.php in order to connect to your specific database. All the PHP files in your apps will use that db_connect file. It looks like this:
At lines 9, 10, and 11, you need to enter your details: your student ID, your password, and your database. Replace the asterisks with your values. Do not touch anything else in the file. Make sure you keep the prime characters: "". Just replace the asterisks: *. This will make your specific app connect to your specific database.
You only need to do this once for every app. That’s it, save the file and we can continue.
Alright, back to the app. Rember that until now, everything happened in the browser, on the client side, in the front-end. Now, we start looking at the app’s server side, the back-end. Note that PHP files cannot run on your computer. To test PHP code, you need to upload to the server. The code looks like this.
The first thing you should notice about this file is how different PHP code looks compared to JS code. Different programming languages have different ways of writing things. In JS to create a variable you write a command like let or var followed by the name of the new variable. In PHP, you just need to name the variable to create it, but variable names always start with a dollar sign: $variable. All programming languages have pretty much the same constructs (variables, functions, conditions, etc.), but their syntax, or the way you write these things, are different. Be careful not to confuse JS and PHP code. You will get used to this, but at first, pay special attention to it.
Alright, let’s look at the details. Line 12 is the first important thing to note. This is where we collect the data that was written in the HTML form and passed by the JS through the formData. Notice how stuff is still called name. Again, match the names of things and all will be well. So we get the value of the name HTML input from the request (or the URL) and store it in a local variable called $name.
Next is line 14. Here we prepare the SQL query that we will use to insert the value into the database. You may remember this query from the very beginning of this tutorial. Remember that I mentioned how we will replace the question mark in the query with the value given by the user. We do this in this file. So here at line 14, we simply write the query. It gets prepared to be sent to the DB at lines 20 to 26.
The substitution of the question mark for the value is done at line 23. This command, bind_param(), is important. You will see this in many of the app files. Let’s look at it more specifically.
The command takes two parametres (stuff in the parenthesis). The first one is a code that determines the data type of the value provided. In this case, it is a string, so "s" (see the comment above this line for other values you can use). A string is like a varchar. It is more the other way round, varchar is a string, but anyway remember that when you have a varchar column, you want to send it a string value. The other parametre is the value we send. This is what will replace the question mark in the query. In our case here, the variable $name which contains the value that the user entered in the HTML form’s input.
You do not need to pay attention too much to the rest of this file. This should remain pretty much the same in all your apps.
Now, you should be able to test this part of your app.
Note that the app must absolutely run on the server. Unlike other apps and sites you have been building so far in the program, you cannot test apps that rely on PHP code on your personal computer. You must upload all the files on the server, and test the app there. Your personal computer cannot connect to the database which is at 2022.micromediaweb.com, and it also cannot run PHP code. So everything must be on the server to test.
Upload your files like you would any other web file you create. And navigate with your browser to the location of the index file of this app. Note that PHP files do not work in your browser. The app is the index.html.
To test the INSERT, open the index file of this app’s folder (on the server). It should look like this:
Type anything in the text box, and press Add Name. Note that you may see nothing here. We have not yet developped the part of the app that shows the list of name. But it could work if you have setup your table and everything else exactly like mine.
If you don’t see the names, it may still have worked. You will need to connect to Adminer, and check if the name was added to your table. Navigate to your table, and click Select data. If the name you just entered is there, your app works. If not, something is wrong. Read the section above again, and try to see what you are missing.
A note about errors. You may have gotten used to checking the console in your browser for JS errors. This is great. But unfortunately, PHP does not trigger errors in the browser. So sometimes, it is difficult to determine what is wrong because you do not get clear error messages. The code is written so at least some PHP error messages are posted in the console. But we can’t get them all.
When this part works, move on the the next part.
Now that we have the INSERT command setup, there should be some rows in your table. We will be able to show them to the user. For this part of the app, we will begin with the JS file. Again we follow the data flow determined early in the tutorial.
js/select_items.js — first visit
For the SELECT query in this app, the user does not provide data. Instead the app code will get the data from the database and will display the result in the HTML. The file looks like this.
This file is a little bit longer, because the JS will not only request the data from the PHP, it will also generate HTML elements in the DOM for each item that is found in the database, in order to show them on the interface.
First, look at line 5 where we again create a function. This function is then called at the end of the file, line 54. Remember that this function is also called by insert_item.js after adding the item to refresh the list. Grouping commands in a function like this makes calling them from another file possible.
Line 8 specifies the PHP file that we will request: php/select_items.php.
At line 14, we create a variable, itemRaw, which will contain the values returned by the PHP. In this case, all the data found in the database is encoded in JSON (we’ll see this later), and here in the JS we parse it again, and store it in an array (at line 24). (An array is a variable that contains a list of values, instead of a single value.)
For now, we will leave the discussion of the JS file here. I will explain the section of code from lines 27 to 49 after we look at the PHP.
The PHP file that handles the SELECT query looks like this.
Notice at line 3 that this file also loads the db_connect.php database connection.
And again, like for the INSERT, we prepare an SQL query at line 12. In this case though, there are no question marks because no data comes from the user or the system. We are simply making a SELECT. You may notice that the query has option code at its end you have not yet seen. Let’s look at it.
The first part you can recognize: the command, the column names, then FROM and the name of the table. After this you have ORDER BY. This option will sort the results according to the provided parametre. In this case, we ask to sort the results using the value of the ID column. And DESC means in a descending order: most recent first. ORDER BY is very useful, you can use it to sort the results of any SELECT query.
Continuing in the code of the file, at line 15, we create a variable, an array. This array, $rArray, will contain the results table from the query. We will then encode this in JSON (at line 31).
At line 21 we have another important command: bind_result(). This is the counterpart to bind_param() we used for the INSERT query. This one collects the results returned from the DB, and stores them in the provided variable. In this case $rName. If we collected more than one column, we would need more than one variable here. You will see this in other apps. Also, notice how the variables here are named starting with r. This is just to differenciate them from the other variables: these are the results from the DB.
At lines 24 to 29, we store the results from the DB, one row at a time, into the array we created at line 15.
And finally, we encode the array in JSON, and send it back to the JS (line 32). The command echo is used to return values to the JS. Notice that it is used also in this file to return errors, if there are any.
At this point, we can go back to our discussion of the JS file to finish this portion of the app.
js/select_items.js — second and final visit
We will now look more specifically at the code in lines 27 to 47 of select_items.js which we skipped in the section above.
Alright, remember that here in this file, the variable itemRaw contains the results of the query. From within this variable, we can access the values returned by the database. We do not know how many rows the DB contains when we write the code. So we need a way of creating HTML elements to display however many items are found in the database.
So at line 28, we create a JS variable that contains a reference to the HTML element listContainer. If you remember from the discussion on the HTML file, this is the element we have prepared to contain the names found in the DB.
At line 30, we empty this container. We basically remove all the code and text from within the tag. Every time we refresh the list, we want a new list, so we need to empty the container before building the list again. This is an important step. If we did not do this, the lists would repeat themselves in the container.
The block from line 33 to line 49 is a loop that passes through every value in itemRaw. This means that it goes through every row returned by our SELECT query. For each one, we will create HTML elements, and we will add these elements to the list container.
We dynamically create elements in the DOM: in the JS representation of the HTML document. First, line 38 creates a new <div>. This element will contain each row, each person. Then, line 39 assigns the class item to the new element. We assign a class to this so it is easy to write CSS to define the appearance of these elements. In our example app, there is no CSS. But you might wat to do some in your apps.
Line 42 creates a new <p>. Then, line 43 generates the content of the new p element. Using JS, we progressively build a line that will be written in the <p>. It starts with the string "Hello ". Notice the space at the end, inside the string. We need to include the space otherwise there would be space between the words. Then, we have a + sign which adds to this string. (In programming parlance, what we are doing here is called concatenate strings.) After the + we add the value of itemRaw[c].name which is the value of the name column from the DB. And then we have another + followed by a short string: "!" which finishes the line with an exclamation point. Voilà!
Note that even though we included it in our query, so we have access to it, we are not using the value of the ID column in the JS in this example. If we wanted to use the ID value, it would be found in the variable itemRaw[c].ID.
The last two lines of this block are super important. So far we have created two new HTML elements: a <div> and a <p>. These elements are referred to in the JS as itemDIV and nameP, respectively. We have created them in the DOM, but we have not yet added them to the HTML. They do not appear yet in the window. So we need to append them. First, we put nameP inside itemDIV (line 46). And then, we put itemDIV inside the list container (line 47). This will make the new elements appear in the browser window.
After this, the block loops to the next item in the results, until there are no more items.
If you want to see the results of this process, look in the Elements tab of the developer tools in your browser. You will see the generated elements inside the #listContainer box.
This is it for today. Your app should be fully functional now.
Now that you have a working app, and that have a certain level of understanding of how it is built, you will have a better experience building the other apps in the projects you will develop in this class.