website/webdevelopment/gsfullstack/connect-Windows11.js

import { addBanner, addArticle, addHeader, addParagraph, addSubHeader } from '/scripts/article.js';
import { addInset, addInsetList, addInsetCodeListing, addInsetBulletList } from '/scripts/inset.js';
import { addImageWithCaption, addButtonGroup } from '/scripts/visuals.js';
import { menu } from '/scripts/web_dev_buttons.js';

const main = document.querySelector("main");
main.append(addBanner("<a href='https://www.linkedin.com/learning/getting-started-as-a-full-stack-web-developer?contextUrn=urn%3Ali%3AlyndaLearningPath%3A59370541498ec352a683231c'>Getting Started as a Full Stack Web Developer</a>", "Tom Geller", "Connect to Databases and Other Sites"))
main.append(addArticle())
const article = document.querySelector("article")
article.append(addHeader("Data-Exchange Standards: XML, JSON and Others"))
article.append(addParagraph("If you think about the content of a website, some website have fairly static content, like this one.  That is not to say that it doesn't change or even that it doesn't change regularly.  What it does mean is that once the website has been put on the internet, it doesn't chamge until the web developer modifies the HTML, CSS or JavaScript on the web server.  Wahtever changes are made, a user will then see those changes in a browser.  Abother important aspect of this is that at any given time, anyone viewing the website on a browser will see exactly the same content if they are viewing the same page.  In contrast, a website such as Amazon provides dynamic content and as a result, everyone viewing the site sees individually tailored content.  for example, ifd I go to the home page now, I see items I bought before and can buy again, items I have previously viewed but not bought, video recommendations and so on.  If another user views the page on their own PC, they will see something similar but with items more relevant to them."))
article.append(addParagraph("The amazon home page has a structure with sections that display the data I described in terms of what I see on their home page so this means that the web server has to be able to access the data that tells it what items I have viewed recently, my purchase history and so on and it will generally store this type of data in a database.  As a result, when I send a request to the Amazon web server for its home page, it uses HTML, CSS and JavaScript/PHP to generate the page structure, but a lot of the content is going to come from the database and will be specific to me."))
article.append(addParagraph("Let's think of this in a more specific example.  Imagine that you have some data records containing names and addresses and you want to display this data in such a way that the name is quite prominent.  In HTML terms, we might achieve this by using an h3 tag for the name and a paragraph tag for the address so a typical entry might look like this."))
article.append(addInset("<h3>John Smith</h3>"))
article.append(addInset("401 Nowhere Street"))
article.append(addParagraph("In terms of the HTML, this will look something like"))
article.append(addInset("&lt;h3&gt;John Smith&lt;/h3&gt;"))
article.append(addInset("&lt;p&gt;401 Nowhere Street&lt;/p&gt;"))
article.append(addParagraph("This is fine of you only have a single record or maybe just a few but it could become cumbersome if you have hundreds or even thousands of records.  Another problem you might have is that you may decide to change the HTML.  Perhaps you want to use h2 or h4 tags in place of h3.  If you have hundreds of them, you will have to change the tags for each one which could be a daunting task.  You may be thinking that you could just copy and replace all h3 tags and that is true.  But this can also cause problems because you may be using h3 tags elsewhere that you don't want to change."))
article.append(addParagraph("The solution to both of these problems is to create a presentation template so you can use it to display as many records as you need to and if you need to change the structure, you only have to change this in the template."))
article.append(addParagraph("A presentation template might look something like this."))
article.append(addInset("PRESENTATION:"))
article.append(addInset("&lt;h3&gt;{name}&lt;/h3&gt;"))
article.append(addInset("&lt;p&gt;{ADDRESS}&lt;/p&gt;"))
article.append(addParagraph("Another advantage with a template is that the data can come from any source and that includes databases but it can include partners and public sources.  However, you should also note that in order to use the data, it has to be provided in a standard format.  For example, if you had a data source with first name, surname and address, you wouldn't be able to use that data with the template shown above without first converting it to the standard format."))
article.append(addParagraph("One of the consequences of sharing data in this way across websites or across different sources is that it is important for developers to know how data is transferred between computers and this is simplified by the fact that there are three main formats used for this purpose.  These have different approaches to modelling data but there are some common features including the fact that they all store data in fields, each field is essentially a key value pair in that the field has both a name and a value and each format is line based - one record per line - and has values matching the stated keys."))
article.append(addSubHeader("Delimited Text"))
article.append(addParagraph("Where fields are defined by a delimiter, this just means that there is a character specified as the delimiter and wherever this appears in the record, it is interpreted as the end of one field and the start of another.  A common example of this is a file with comma separated values, commonly known as a CSV file.  It can sometimes use quote marks to make the data clearer and can also include escape values where the character specified as the separater appears inside a data field.  Delimited text can often contain characters and formatting that breaks the system so it is not a very clean format and as a result, developers will often convert delimited text to one of the other formats before using it."))
article.append(addSubHeader("XML"))
article.append(addParagraph("XML stands for eXtensible Markup Language and is related to HTML and you can see that in the way it uses tags to identify data items.  A typical xml data item might look something like the image shown in figure 18."))
article.append(addImageWithCaption("./images/xml.png", "Figure 18 - A typical example of data stores in XML."))
article.append(addParagraph("You can insert XML into HTML, although these days, HTML is more flexible and so it could be argued that this is now HTML and I think that it probably a fair point and you can certainly treat is as though it were HTML and that means that in addition to being able to display it in an HTML document, you can also add HTML attributes to the tags (such as ID or style) and you can style it with CSS."))
article.append(addSubHeader("JSON"))
article.append(addParagraph("JSON is a very common format and is probably the most popular at the moment.  It stands for JavaScript object notation.  With delimited text, we stated that each field had a stated key so you could almost think of this as being a set of key value pairs, but the key is only stated once (like headers in a spreadsheet) and so the key is implicit in the pairing.  A record in JSON is essentially an object and that means that it is self-contained.  Every record has a full set of keys as well as values and these are arranged in key-value pairs.  The main advantage to that is that it makes the format very easy to read for a computer but less so for a human and that is true for XML."))
article.append(addParagraph("If you want to learn more about these formats, you might be interested in the following courses on LinkedIn Learning."))
article.append(addInset("<a href='https://www.linkedin.com/learning/learn-api-documentation-with-json-and-xml'>Learn API Documentation with JSON and XML</a>"))
article.append(addParagraph("This covers the basics of both XML and JSON."))
article.append(addInset("<a href='https://www.linkedin.com/learning/xml-essential-training-2'>XML Essential Tranining"))
article.append(addParagraph("This is obviously covering XML in much more detail and is probably of more relevance to web developers who are looking to incorporate XML into their web sites.  There are one or two other XML courses so it is reasonably well covered."))
article.append(addInset("<a href='https://www.linkedin.com/learning/json-essential-training'>JSON Essential Training</a>"))
article.append(addParagraph("Similarly, this is a much more detailed look at JSON and is probably a good choice for web developers."))
article.append(addHeader("Connect to a Database"))
article.append(addParagraph("It's probably fair to say that in general, programming on the server side is more complex than programming on the client.  Side put another way, programming languages like PHP are not quite as user-friendly as JavaScript, HTML and CSS.  Beginners often start to see code that is harder for them to understand when they start to look at PHP and as often as not, when they do start to do that, it is for the purpose of setting up a database for their website."))
article.append(addParagraph("Just a quick aside here, I'm not sure if I even had a database installed on my web server.  I tried installing phpmyadmin because it looks like Tom will use that in the course, but I had problems with that.  It didn't seem to want to connect to my database which suggests that I either haven't got one installed or it is misconfigured.  I'm not too worried about phpmyadmin, this is just an interface that makes it easier to create and modify databases, especially if you have little or no experience of SQL."))
article.append(addParagraph*"I actually ran into a problem when I installed mariadb.  When I tried to login, I got an error")
article.append(addInset("ERROR 1698 (28000): Access denied for user 'root'@'localhost'"))
article.append(addParagraph("I found the solution to this problem on <a href='https://askubuntu.com/questions/763336/cannot-enter-phpmyadmin-as-root-mysql-5-7'>askubuntu.com</a>."))
article.append(addParagraph("First, I connected to mysql with the command:"))
article.append(addInset("sudo mysql --user=root mysql"))
article.append(addParagraph("Next, I created a new user which I called phpmyadmin since it is the account I will probably use in future to administer databases for the website.  The command to create the user is:"))
article.append(addInset("CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'password';"))
article.append(addParagraph("Obviously, you will want to replace password with an actual password and you may also want to choose a different username.  You can then grant privileges to that user with the command:"))
article.append(addInset("GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;"))
article.append(addParagraph("In this command, I am assuming the username is phpmyadmin so if you have changed it in the CREATE USER command, you will need to change it here as well.  Finally, you should flush privileges:"))
article.append(addInset("FLUSH PRIVILEGES;"))
article.append(addParagraph("You should then be able to connect to mysql with the command:"))
article.append(addInset("mysql -u phpmyadmin -p"))
article.append(addParagraph("You will be asked to enter the password you gave the user in the CREATE USER command and you should then see the sql prompt and it might be worth playing around with it a little to make sure it's working and, if you need it, to get you used to using SQL commands.  Let's start by checking what databases exist which we do with the command:"))
article.append(addInset("SHOW DATABASES;"))
article.append(addParagraph("Incidentally, SQL commands are not case-sensitive but names (database names, table names, field names) are.  In any case, this will provide us with a list of the databases that currently exist."))
article.append(addParagraph("One of the databases is called mysql and presumably that contains useful information relating to the configuration of mysql so let's take a look at that.  We can connect to this database with the command:"))
article.append(addInset("USE mysql"))
article.append(addParagraph("Incidentally, you may have noticed that SQL commands terminate with a semi-colon and usually, it will refuse to go any further until you type it in, but the USE command is an exception to that rule.  We can see what tables are in there with the command:"))
article.append(addInset("SHOW TABLES;"))
article.append(addParagraph("One of the tables in the mysql database is user, so let's take a look at that.  We can use a command such as:"))
article.append(addInset("SELECT * FROM user;"))
article.append(addParagraph("This gives us all of the data from the table but it is not formatted so it looks quite messy.  We can get a formatted version of this by replacing the semi-colon with \G"))
article.append(addParagraph("This puts every field on its own line so it is much easier to read.  From there, we might decide that there are certain fields that we are more interested in and selecting just this data from the table makes it much easier to understand.  As an example, let's say that we want to see the values in the first three fields (these fields being Host, User and Password.  We can do that by specifying these fields in the SELECT clause.)"))
article.append(addInset("SELECT Host, User, Password FROM user;"))
article.append(addParagraph("That gives us a nice, neat little table so we can easily see what mysql users are in the database and we can compare them.  Notice that user passnames are hashed, so you won't be able to see the actual password but this can still be interesting.  For me, I am seeing four users, mariadb.sys, mysql, root and phpmyadmin.  The password field is blank for mariadb.sys, presumably because you can't connect with that username.  For the user, phpmyadmin, I see the hashed password.  For the other two, mysql and root, the password is shown as invalid, so that is probably why I was unable to log in as root earlier."))
article.append(addParagraph("We will now switch to using the database with php.  For our hotel website, we would like to have a database in which we can store our reservations and we will call that webdevcourse and we will create a table inside it called reservations which will hold the actual reservations data."))
article.append(addParagraph("The image in figure 19 shows a PHP file for connecting to the database."))
article.append(addImageWithCaption("./images/dbconnect_php.png", "Figure 19 - the dbconnect.php file."))
article.append(addParagraph("The PHP file shown in figure 19 contains three directives and we will look at each one in turn."))
article.append(addInset('include "constants.php";'))
article.append(addParagraph("This includes the constants.php and as the name suggests, this file contains some constant values and these are the name of the database and the password.  For security reasons, these are kept in a separate file.  The next line"))
article.append(addInset('$db_connection = new mysqli("localhost", DB_USERNAME, DB_PASSWORD, "webdevcourse");'))
article.append(addParagraph("is the code we will use to connect to the database.  Note the new keyword which suggests objects and in this case, we are creating an instance of the mysqli class with the arguments we provide here and sets $db_connection to that value.  In short, $db_connection is an instance of mysqli that contains all of the data required to make a connection to a specific database."))
article.append(addParagraph("You can get more info on the mysqli class in the online <a href='https://www.php.net/manual/en/book.mysqli.php'>PHP Manual</a>, including the query method which we can use to perform a query on the database.  In the last part of the code in the database"))
article.append(addInset("$db_connection->query"))
article.append(addInset("    \"INSERT INTO `reservations` (`name`, `date`) VALUES ('Chalmers', '2021-04-20');\""))
article.append(addInset(");"))
article.append(addParagraph("One of the nice things about SQL is that its commands are designed to read like English as far as possible so even if you haven't used it before, you will be able to get a good idea of what this command is doing.  It is inserting a record into the database with the details shown here."))
article.append(addParagraph("Before I go any further, I have created the database with the command"))
article.append(addInset("CREATE DATABASE webdevcourse;"))
article.append(addParagraph("and we add the table with the command"))
article.append(addInset("CREATE TABLE reservations (name varchar(60), startdate date);"))
article.append(addParagraph("I would like to add code to the registration form so that the databases is updated when the form is completed and I have experimented with that without success, but I hope that the course will cover how to do that later."))
article.append(addHeader("Embed an Informative Widget"))
article.append(addParagraph("Earlier, we saw how easy it was to embed a YouTube video into a website and this is because YouTube makes it really easy for us to do that.  Essentially, they give you the code to enbed a video and don't place any restrictions on how this is used.  For example, you don't have to pay in order to display that video in your website regardless of how often it is viewed."))
article.append(addParagraph("We can think of this as a type of widget and there are a number of other widgets you can incorporate into your website and some of these have more restrictions.  In general, the widget's creator will provide you with an API key that they can use that to track how you use their information and can also use it to charge you.  To illusatrate this, we will look at another widget that can be embedded in your website to provide weather information and we will add it to our project."))
article.append(addParagraph("We will get an API for the weather widget from <a href='https://openweathermap.org/api'>https://openweathermap.org/api</a>.  I have created an account on the site and I want to get an API key which you can get by going to their <a href='https://openweathermap.org/price'>pricing</a> page.  From here, you can subscribe to various plans including a free plan thay includes 60 calls per minute which is more than adequate for our needs so we can click on Get API key under the Free plan.  You can then click the drop down menu next to your username (assuming you are signed in) and select My API keys to see the key that you have generated.  If the widget is added to my website, it will incorporate this API key.  Whenever the page is loaded, the key is used to make an API call to get the information needed to display the appropriate weather data."))
article.append(addParagraph("We are going to add the widget to our index.html page in the project so we will start by putting a couple of comments in the code to mark the start and end of the code we will insert to make the widget work, like this."))
article.append(addInset("&lt;!-- Begin weather info --&gt;"))
article.append(addInset("&lt;!-- End weather info --&gt;"))
article.append(addParagraph("Like YouTube, <a href='https://openweathermap.org/api'>https://openweathermap.org/api</a> provides the code for you to embed in the website and you can get that by clicking on the widgets link which is usually found at the bottom of the page.  There are three different themed widgets and several versions for each theme.  You can also select a city for the widget which will be the location for which werather info is being provided as shown in figure 20 below."))
article.append(addImageWithCaption("./images/widgets.png", "Figure 20 - The widgets page from openweathermap.org."))
article.append(addParagraph("All we need to do is to insert the API key into the box shown in Figure 20, select a city if you don't want to accept the default value (for me that is London and since the project website relates to a London hotel, I'm happy to accept that) and then click 'Get a code' for whichever style of widget you prefer.  You can then copy and paste that code into your website.  Figure 21 shows the website with the weather widget displayed."))
article.append(addParagraph("So that involves a little bit more work than embedding a YouTube video, but aside from the need to have an account and generate an API key, the porcess is very similar.  However, you can also use the API without using a widget in order to get only the information you need and we will look at that next."))
article.append(addImageWithCaption("./images/weather.png", "Figure 21 - The weather widgets embedded in the project website."))
article.append(addHeader("Application Programming Interfaces (APIs)"))
article.append(addParagraph("We are now going to delete the widget and replace it with some custom PHP code.  Now, since we are inserting PHP code into a file with an HTML extension (index.html), we will need to also rename it to index.php.  We will then insert a simple PHP statement to test whether we can execute PHP in the page.  The PHP statement we will use for that is"))
article.append(addInset("&lt;?php echo ('Hello, world!'); ?&gt;"))
article.append(addParagraph("For this to work, you need to have PHP running and you can check that by typing this command into a terminal on the web server."))
article.append(addInset("php -v"))
article.append(addParagraph("That should return a version number.  If it returns an error, you might have to install PHP which you can do with the command."))
article.append(addInset("sudo apt install php7.4 libapache2-mod-php7.4 php7.4-mbstring php7.4-mysql php7.4-curl php7.4-gd php7.4-zip -y"))
article.append(addParagraph("That will install PHP 7.4 and you may wish to edit the command or look up more current instructions if you want to install a newer versions.  Just a reminder, these instructions are relvant if you are using a raspberry pi web server you can access via ssh and may also work for other Linux servers accessible by ssh."))
article.append(addParagraph("Now, if we save this file and reload our project page, we can see that instead of the widget, we have the output of this PHP command and you can see that in figure 22."))
article.append(addImageWithCaption("./images/hello.png", "Figure 22 - The output of our PHP test command in the project website."))
article.append(addParagraph("The next step is going to be to replace this test PHP code with an actual API call and to do that, we will need to look at the <a href='https://openweathermap.org/current#geo'>API documentation</a> which provides an example call and also tells us what parameters the call requires as well as listing the optional parameters we can add.  In this case, the required paramters are both lat and lon (latitude and longitude) in addition to our API key"))
article.append(addParagraph("As a test, I will generate a URL byt adding the required parameters and plugging the URL this gives me into a browser and the result is shown in figure 23."))
article.append(addImageWithCaption("./images/apicall.png", "Figure 23 - The output of our test API call."))
article.append(addParagraph("Incidentally, the latitude and longitude are for my current location in Edinburgh and the API key has been removed from the screenshot.  This raises a couple of interesting points about the API call, not least of which is the fact that it returns data in the JSON format and it is not presented in a format we can use for our hotel's website so we will need to do a bit more work with it.  The purpose of this course is not to cover the PHP or even API calls, we are really just demonstrating the fact that this is something we can do.  The exercise files include the files needed to display a 7 day customised weather forecast and we will not study these in any great detail, but I will briefly describe what they do.  In the index.php file, we have two lines of PHP code"))
article.append(addInset("include 'php/weather.php';"))
article.append(addParagraph("and"))
article.append(addInset("show_forecast();"))
article.append(addParagraph("The first line is giving this file access to the code in the <a href='./weather_php.html' target='_blank'>weather.php</a> and you can see that file by clicking on the link (this will open in a new tab)."))
article.append(addParagraph("The weather.php file only contains one function and that is show_forecast which generates the weather forecast data to be displayed.  It does this by creating an API call using some values for lon, lat and appid which it gets from constants.php and then it executes that call."))
article.append(addParagraph("Remember that the API returns data in JSON format, so it the function then converts this to a format to a PHP object with"))
article.append(addInset("$forecast = json_decode($forecastRaw);"))
article.append(addParagraph("It then outputs some headings and loops through the PHP object 7 times to create a forecast for each of the next 7 days which it then outputs."))
article.append(addParagraph("Having saved the file and refreshed the page, I found that I am getting zero values and regenerating the API call and executing shows an invalid API key message.  In this type of situatoin, it is helpful to know exactly what the API call is so I have added a line to weather.php"))
article.append(addInset("echo \"The API call is \".$forecastRaw;"))
article.append(addParagraph("This should output the message and the value that has been generated for $forecastRaw and this seems to show that $forecastRaw doesn't have a value so it looks like the API call is not being generated."))
article.append(addParagraph("Actually, looking at the code again, that is not quite right.  The variable $forecastRaw is, I think, being set to the json returned by the API call which still shouldn't be blank, but this doesn't tell me if the API call has been made correctly so I am going to amend the code to generate an API call and set it to a variable called $apiCall.  I will then use that variable to make the API call and set the value of $forecastRaw to whatever is returned like this."))
article.append(addInset("$forecastRaw = file_get_contents($apiCall);"))
article.append(addParagraph("We will then echo the value of $apiCall and see if that tells us what is going wrong.  Copying the API call into a browser gives me an invalid API key and comparing this to the example in the documentation reveals a difference.  Wen corrected, the API call seems to be made correctly but I am still not getting correct output."))
article.append(addParagraph("Checking the doucmentation, I found an example API call like the one presented in the exercise files so it looks as though that syntax is still valid so I tried the sample call with my own API key and I an getting an Invalid API key error which most likely means either I got the wrong API key or the free account no longer allows calls in that syntax.  Based on the fact that under One Call API 3.0 there is a subscription button (even though I am signed in), I think the latter is likelier.  Also, looking at the comparison of pricing plans, it looks as though a daily forecast which is what we are trying to add to the site is only available on a paid account so I would conclude that it is not going to be possible to do this."))
article.append(addParagraph("The course, <a href='https://www.linkedin.com/learning/introduction-to-web-apis'>Introduction to Web APIs</a> goes in to this topic in much more detail and may be worth a view, it is a fairly short course (about an hour long) by Andrew Probert, released in Octpber 2019."))
main.append(menu("fullstack"))