Week 7: Creating the Events Page With the Database

This week, I finished the event calendar for our Events page on the website. As described in a previous post, the page already has its events buttons, which took a long time to program. The main functionalities of the category and date buttons involve:

• Selecting one or several categories

• Selecting a start date that must not be after the end date

• Selecting an end date that can only be after the end date

This week was all about doing the database request, so that the events could be displayed according to the category/categories and timespan selected by the user.

---

With the help of the very good event calendar of uitinenschede.nl, I researched all the events that will take place between July and October 2019. I found 75 events that I inserted manually into a mySQL database (manually = using SQL but writing all the lines of code by hand):

The total amount of events in the database is 75 for the chosen time span. Some of the events appear multiple times because they last over several months. The following columns are used for the SQL select statement:

• The column “event_id” is used to detect identical entries.

• The column “month” is used to select events that lie within the time span selected by the user.

• The column “sort_date” is the start date of the event and used to sort the events in ascending order by date as they are displayed by the user.

• The column “category” is the category by which the user can select events based on one or several chosen categories.

On the presentation layer of the website, the user sees the events page with five categories that can be selected from. Here, the user can select one or several categories of interest, based on which the events will be retrieved from the database. Moreover, there is the possibility to select a timespan from July until October with three extra functionalities:

• A start month has to be selected before the end month.

• If a start month has been selected, the drop down menu for the end month only displays months equal to or after the start month.

• If a new start month is selected, the end month button returns to its default state, preventing the user from selecting a new start month that takes place after the already selected end month.

For the dropdown menus of the dates, an array with all possible months is used - for the first date, the dropdown menu is created with a for loop picking all possible dates. For the second date, the dropdown menu is created with another for loop, which starts at the date that was selected for the first date.

Checking the month on the start date is achieved by checking the new HTML content of the start date button, which has been changed from “From” to the selected month from the dropdown menu.

First, the respective month is now the new HTML content of the dropdown button.

Then, the content of the first dropdown button is identified.

In order to transmit the data chosen by the user to the database, they need to be translated into data that can be used in PHP code. Therefore, invisible checkboxes for the categories and invisible text inputs were added in a form to the HTML of the website. The search button is the submit button of the form.

When clicking the submit button on the events page, an event results page is loaded. In order to access the database, a database handler is created, which is included once on the event results page. The database handler serves to create a connection variable to establish the connection to the database. Here, due to password sensitivity, the example of the localhost is used. Later, this data will be changed to the access credentials on the web hosting.

On the events result page, an array of the selected categories is created as they are retrieved from the checkbox input fields of the previous page. Out of the selected categories, the first part of the SQL request is created as the variable $where.

For the second part of the select statement, the time span is required. Depending on the selected start and end month, the variable $when is filled with content.

Out of $where and $when, the select statement is constructed as a new variable $sql.

The results are grouped by “name”, which means that identical results will be eliminated. Previously, it was planned to do this with the table column “event_id” but now we are using the “name” because it is also identical. The “sort_date” is now important because depending on their start dates, the events will be ordered chronologically.

Last but not least, the SQL request is carried out using the variables $conn and $sql. The results are put into divisions which are styled in CSS accordingly in order to achieve a visually pleasant result.