PostgreSQL functions

- [Narrator] When we created the PHP server, we had to log in and add support for making PostgreSQL database connections. When we did that, we enabled a number of PostgreSQL functions that can be used to send requests from the web server to the database server. The first lines of the Script 1 PHP file includes some standard HTML header lines that'll set up the page. This includes the page's title Query 1 as well as a link to the style sheet. The body of the page is made up entirely of a PHP script. It starts here with the opening php tag and ends here with the closing php tag. This script contains two PostgreSQL functions. The first function is pg_connect. This is where we fill in the login details that will allow the script to reach out to the database server and make the connection. This function takes a number of parameters. The host would be the IP address or the network path of the database server. In our case, we can use the name of our Docker container since we placed the web server and the database server on the same virtual network. So my host is equal to pg_db_server. The port 5432 is PostgreSQL's default port that's used inside of that virtual network. We're going to connect to the default PostgreSQL database and use the default PostgreSQL user account. Finally, we need to specify the password Adam123456, which was also established when we created the Docker container. We're using the default PostgreSQL user and password as a matter of convenience here. A more secure approach would be to set up a specific and unique user account for the PHP server to use so it has its own login credentials that we put here in the connection string. This does add a bit of complication though because we would need to ensure that the new user account has been granted all of the required permissions. I want to keep things simplified so we'll stick to the default user account for these examples, which already has administrator level permissions. Now, once a connection is made, we can send a query to the PostgreSQL database server. We do that with the pg_query function. The parameter here is simply the SQL that we want to execute. This example creates a new database named test. So these are our first two PostgreSQL functions, pg_connect and pg_query. There are many other functions that we can use to send communications between the PHP web server and the PostgreSQL database server. I've added a link to the full documentation up here in the description. This lists out all of the PostgreSQL functions that we can make use of when we write PHP scripts. We have a long list of them all right here. For instance, we have a function that'll cancel an asynchronous query or that'll close a PostgreSQL connection. This is a great page to bookmark for future reference if you plan on writing your own scripts. Let's go ahead and close this tab and return back to our example. Now, the only other lines in our PHP script are the ones above and below. These simply include the echo command which will print out text into our HTML files. The first one will print out the text Creating a new database named test, and the second one will echo the line Database created. Both of these get wrapped in h3 tags so they'll be formatted by our browser as heading three. Let's go ahead and press the Run button to execute the code. When it runs, we just see those two lines, Creating a new database named test and Database created. We didn't ask for anything to be returned from the PostgreSQL server, so there's no additional information shown on the screen. We can press the Back button to return back to our script. Now, if you press the Run button again, this time, the PHP server is going to try executing the same functions. And this time, you'll see an error message since the test database already exists now. We still get the Creating a new database line as well as the Database created line, so this might be a little bit confusing. Our script could use a little bit of work to better handle the error and we'll tackle that problem in a few minutes, but this is a good start. The fact that this page says that our database already exists confirms that our first execution was successful in creating the database on the PostgreSQL server. So pg_connect and pg_query are probably the two most commonly used functions when you want a PHP web server to communicate with a PostgreSQL database server.