Server-side scripting and databases

One of the major uses of server-side scripting is to interact with a database which resides on the server. By interacting with this database it is possible to change the content displayed to the user on a web page without updating the HTML with new information. These means that a single template page can be used to display many different pages of information if the information is stored in the database.

In addition, any data that is submitted via a form can be processed by a server-side script and then stored in the database.

MySQL

The database management system that we use with our server-side scripts is called MySQL. You can think of MySQL as being a database server: you make requests to that server and then it responds by sending the appropriate data back to you. Many databases may be stored on a single MySQL server.

However, for our purposes it is very similar to the SQLite databases you have used previously, however it contains many more features.

One important feature is access control: you must have an appropriate username and password to access the contents of particular databases. This prevents unauthorised access to content, unlike SQLite which does not have this functionality.

Accessing the MySQL server

There are a few different methods of accessing MySQL:

  • Python scripts
  • The command line
  • By using a web application called PHPMyAdmin

We will be mainly interacting with MySQL using Python scripts as access to the command line and PHPMyAdmin varies depending on whether you are using XAMPP or another server-side set-up.

If you are using a XAMPP installation then you will need the following to log into the server:

  • Host Name: localhost
  • User Name: root
  • Password: there is no password

Clearly in the real world you would not access MySQL using the root account but since it is merely going to be used locally for learning there is no real need to set-up an additional account.

If you are accessing MySQL in a different way you will be provided with the appropriate details.

Creating a database

Now that you have some understanding of MySQL it is time to create your first database. You will create a database called exampledb and once created you will add an entity to this database called person.

The SQL for creating a new database is:

  • create database exampledb;

The person entity can be defined as:

  • person(personid, firstname, lastname)

The video below demonstrates how to create the database and the entity:


Task 7

Using the information above, create the table and entity described.