Checking In, Tweaking the Database, and Creating Dynamic Drop Down Lists

Last week I had a great meeting with the wonderful ladies that head the Student Activities department to show them my progress and to discuss ideas. They gave me some direction on some additional features they would like in the database and changes were made accordingly. I also spoke with UPB’s Database Design professor, Robert Ellison, about the structure of my database and he gave me some great ideas and assistance on how to handle the locations of inventory.

The tricky part of the database is preventing the data from being redundant, but also to be able to accurately keep track of rooms and bins that may similarly be named in different buildings. The new structure of the database addresses these problems.

Student Activities Database Model

My primary objective of the website, which is typically the primary objective of any data-handing website, is to eliminate any possibility of bad data being inserted into the database. One way I’m handing this is by using drop down lists everywhere possible. The least amount of textboxes used the better, as textboxes give the user the opportunity to misspell words and data can possibly get lost.

Because areas of the website have the potential to constantly change, I’m using dynamic drop down lists based on queries of the database. The Building, Room, and Bin tables can all be controlled from the Administrative page. Here is an example:

This is the building table being displayed within the administrative page.

building_table

An administrator has the ability to add new buildings or change the active status of a building (flip from active “Y” to active “N”). Note that a building, once added, cannot be deleted in order to maintain records for years to come. When adding a room, the user is provided with a drop down list of the active Buildings.

room_table

When a user adds a building or changes the active status of that building from “Y” (active) to “N” (not active), the drop down list will change accordingly. To do this, I’ve created some functions that are called that perform a SQL query and insert the results into the HTML code.

For example, below is the HTML code. The <select> tag is technically what the drop down list is called, and normally some <option> tags would reside inside which make up the list. Instead, a function is called here.

<select name=”ddlManagerBuilding” id=”ddlManagerBuilding”>
<?php
DropDownBuilding();
?>
</select>

What is happening with the DropDownBuilding() function is a simple query of the Building table, and for the <option value=> the Building_ID is being inserted while the Building_Name is what is actually being displayed so as to not confuse the user. (People tend to work better with names than numbers.)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s