Creating and Using the Database

In the case of the Smith Science web server, the administrator had to actually create the database instead of my doing it myself. Once the database exists on the server, to start using it, type in the following commands at the prompt.

mysql -p;

This will ask you to enter a password. Enter the appropriate password. Then, to access the correct database, enter the following command:

use cs_list;

You are now in the CS Student Listing Database. To view the tables in this database type:

show tables;

To see the characteristic of each column in the table, type:

describe table_name;

where table_name is the name of the table you wish to view the structure of. To view the actual information in the table, type:

SELECT * FROM table_name;

(SELECT clause information: http://www.mysql.com/doc/S/E/SELECT.html)

To query the database and see the results of that query in a resulting view of the information, type an SQL statement such as the one below:

SELECT * FROM student_info
WHERE class_yr = 2003;

This will get you all of the information about each student in the class of 2003. You could also limit this display by doing something similar to the following

SELECT first_name
FROM student_info
WHERE class_yr = 2003;

To insert some data into the table, you can type the following command:

INSERT INTO student_info (first_name, last_name, class_yr)
VALUES ('Example', 'Student', 2003);

(INSERT clause information: http://www.mysql.com/doc/I/N/INSERT.html)

To create a table in the CS Listing database, type:

CREATE TABLE table_name (first_name VARCHAR(30) NOT NULL);

This will create a table called table_name with one column called first_name that can have at most 30 characters and cannot be NULL. You can create many columns for your table by simply separating each definition with a comma (,).

(CREATE TABLE clause information: http://www.mysql.com/doc/C/R/CREATE_TABLE.html)

Now that you have learned some basic ways to construct and manipulate a database, let's continue and see the many ways in which I was able to insert, update, and delete information in the database.

Adding a New Student to the Listing

One of the basic features of this listing is its ability to allow students to add their information to the database directly. This is the first way in which data can be put into the database. The two files used for this process are cs_add.html and cs_add.php.

The first file, cs_add.html contains a form for new students to fill out and submit. This form asks the user for information relevant to the listing such as first_name, last_name, web address, current employer, etc. There could be any of a number of fields in this form that could be stored in a database with fields existing for each somewhere in the database. In this case, however, the fields used in this form are the only relevant fields for this listing. The code for this form is as follows:

<form name="Data" action=cs_add.php method=GET
onSubmit="JavaScript:return CkInfo();">

First Name: <input type=text name=first_name size=25 maxlength=25>
Last Name: <input type=text name=last_name size=25 maxlength=25>
Class Year: <input type=int name=class_yr size=4 maxlength=4><br>

<br>

Web Page:
<input type=text name=webpage size=50 maxlength=100><br>

<br>

Ada Comstock: <input type=radio name=ac value='1'> Yes 
<input type=radio name=ac value='0'> No <br>

<br>

Current Employer:
<input type=text name=employer size=25 maxlength=55><p>

Other institution attended (if not a Smith student):
<input type=text name=other_college size=25 maxlength=35><br>


<p><input type=submit value="Submit"> <input type=reset value="Reset">

</form>

When the user hits the submit button, all information that she has entered in this form is checked and then sent through the URL to the page listed after action in the initial form tag. The information is checked using a JavaScript function created for this project called CkInfo. This function checks to see whether the information entered by the user fits within certain parameters. If it does not, and error message is displayed in a pop-up window. This method was chosen as opposed to a PHP solution because it was unclear whether there was a PHP function that would create a pop-up window error message which I felt was the most useful. The form calls the function from the initial form tag in the following way:

onSubmit="JavaScript:return CkInfo()

The full code for the CkInfo function is as follows and can be found in the header of the cs_add.html file:

<script language="JavaScript">

function CkInfo(){
if ( document.Data.first_name.value == 0 ) {
alert("You must enter your first name.");
return false;
}
if ( document.Data.last_name.value == 0 ) {
alert("You must enter your last name.");
return false;
}
if ( document.Data.class_yr.value < 1900) {
alert("The year you entered is an incorrect value");
return false;
}

return true;
}

</script>

The page that is sent the form information happens to be cs_add.php. This is the page that takes the information given by the user and inserts it into the database where it belongs.

Here is some of the important code from this page.

 In order to insure that the only people submitting their information from this form are new students, a SELECT statement at the beginning of this page checks to see if the users information already exists in the database. If it does, then she is not a new student and she is not allowed to enter new information in this manner.

<?php
//connect to the host
$db = mysql_connect (localhost, orourke, orourke);   

//select the correct database
mysql_select_db (cs_list);                                          

// check whether information already exists
$selectresult = mysql_query ("SELECT FROM student_info
                                                        WHERE first_name = '$first_name'
                                                        AND last_name = '$last_name'
                                                        AND class_yr = '$class_yr' ");

if ($selectresult)          //if the SELECT query found something
{
echo "<p>You were already present in our list.";
}

else
{
echo "<p><img src='welcome.gif'> new student.";

(mysql_query() information: http://www.php.net/manual/en/function.mysql-query.php)

If the SELECT statement returns a value selectresult then that means that her information already exists.

If the user's information does not already exist. then she is added to the database using an INSERT clause that matches the input from the user with the fields available in a specified table and then inserts that information in the appropriate place.

//insert information into the database

$insertresult = mysql_query ("INSERT INTO student_info (first_name, 
                                last_name,class_yr, web_site, AC,
                                employer, other)
                                VALUES ('$first_name','$last_name',
                                '$class_yr','$webpage','$ac',
                                '$employer','$other_college')");

if (!$insertresult)      //if the insert was unsuccessful
{
echo "<p><b>ERROR</b> - We were unable to add your information. Please contact<br>";
echo "our webmaster at <a href=mailto:orourke@cs.smith.edu>";
echo "orourke@cs.smith.edu</a>.", mysql_error();
}

else if ($insertresult)
{
echo "<p>Your current information has now been added to our list.";

The appropriate message from above is then printed on the PHP page telling the user the status of her action (i.e. her information was successfully added or her information already existed). An email is then sent to the webmaster informing them of what information was just enterred.

//email message

$body = "The following information has been added to the CS Student";
$body = "$body Listing. \n\n";
$body = "$body First Name: $first_name \n";
$body = "$body Last Name: $last_name \n";
$body = "$body Class Year: $class_yr \n";
$body = "$body Web Address: $webpage \n";
$body = "$body Ada Comstock: ";
if ($ac == 1)
$body = "$body Yes \n";
else
$body = "$body No \n";
$body = "$body Employer: $employer \n";
$body = "$body Other Institution Attended: $other_college \n";

mail("orourke@cs.smith.edu", "Information Added",$body,"From: cs_list@smith.edu");

}
}
echo "<p>To return to the Computer Science student listing click ";
echo "<a href='http://websci.smith.edu/~orourke/cs_list.html'> here</a><p>";

include("footer.txt");
?>

(mail() information: http://www.php.net/manual/en/function.mail.php)

What if the user wants to change the information she has enterred? This will be discussed in the next section regarding retrieving and editing information.

Editing Previously Existing Information

If a user would like to edit their information there are two processes involved. One is retrieving her information that is already present in the database. The second step is then updating her record of information in the database with the new information enterred by the user.

In order to retrieve the information entered by the user we must first determine which student wishes to edit her information. This is accomplished is the file cs_edit.html which contains a form where students enter their first name, last name, and class year in order to retrieve their proper record for editing. This form also uses the CkInfo function.

<form name="Data" action=cs_edit.php method=GET
onSubmit="JavaScript:return CkInfo();">

First Name: <input type=text name=first size=25 maxlength=25>
Last Name: <input type=text name=last size=25 maxlength=25>
Class Year: <input type=int name=class size=4 maxlength=4><br>

<br>

<input type=submit value="Submit"> <input type=reset value="Reset">

</form>

When the user submits her first name. last name, and class year, this information is sent to eh cs_edit.php file in the URL.

The cs_edit.php file is unique in that it gets information from the HTML file, uses it to retrieve data and display it in a form, and is then responsible for processing the changes, updating the database, and displaying the results of this task to the user. As we have done so far, information submitted in a form is usually sent to another page but in this case, the page that is the means for inputting the data is also the means for processing it.

This page begins with an update clause, which may seem strange, but it is not utilized unless the submit button has been pushed. This will be discussed further later.

Next, a SELECT clause is used to select the information from the database where the first name, last name, and class year fields match those submitted by the user. If the information is found, it is stored in an array structure wherein the specific values can be accessed directly. If no previous information is found, an error message is displayed.

else
{ 
$selectresult = mysql_query ("SELECT * FROM student_info
WHERE first_name = '$first'
AND last_name = '$last'
AND class_yr = '$class' ");

(mysql_error() information: http://www.php.net/manual/en/function.mysql-error.php )

In this case, you can see that I have set the variable $row equal an array containing the resulting record.

$row=mysql_fetch_array($selectresult)

(mysql_fetch_array() information: http://www.php.net/manual/en/function.mysql-fetch-array.php)

All of the information in that particular record is stored in this variable so I can access this information in the following manner:

$row["first_name"]

In order to make this information useful to the user, we will display it in a form wherein she can edit it and then resubmit it to the database. The code for creating a form using retrieved information is as follows:

echo "<form method=POST>";

echo "First Name: ";
printf ('<input type=text name=edit_first size=25 maxlength=25 value="%s">',
$row["first_name"]);

echo "Last Name: ";
printf ('<input type=text name=edit_last size=25 maxlength=25 value="%s">',
$row["last_name"]);

echo "Class Year: ";
printf ('<input type=text name=edit_class size=4 maxlength=4 value="%s">',
$row["class_yr"]);

echo"<br>";

echo "<br>";

echo "Web Page: ";
printf ('<input type=text name=edit_web size=40 maxlength=25 value="%s">',
$row["web_site"]);

echo "<br>";

echo "<br>";

echo "Ada Comstock: ";

if ($row["AC"] == 1){
printf ('Yes <input type=radio name=edit_ac value=1 checked>', $row["AC"]);
printf ('No <input type=radio name=edit_ac value=0>', $row["AC"]);}

else{
printf ('Yes <input type=radio name=edit_ac value=1>', $row["AC"]);
printf ('No <input type=radio name=edit_ac value=0 checked>', $row["AC"]);}

echo"<br> <br>";

echo"Current Employer: ";
printf ('<input type=text name=edit_employer size=25 maxlength=25 value="%s">',
$row["employer"]);
echo "<p>";

echo "Other institution attended (if not a Smith student): ";
printf ('<input type=text name=edit_other size=25 maxlength=25 value="%s">',
$row["other"]);

printf('<input type=hidden name=update_id value="%s">',$row["id"]);

echo "<p><input type=submit name=submit_changes value='Modify Record'>";
echo "</form>";

Once the user has made her changes, she will want to submit her changes. This is where the UPDATE clause comes into play. In the opening form tag of this particular form, you can see the command <form method=POST>. This tells the form to take the information held within its fields and submit it to itself. When the form does this, the variable isset is set to a value and the page is then reset with this new value. The value of isset is not important, just the fact that it has one matters. Once isset has a value, the UPDATE clause runs and updates the information I the database with the information in the form and continues onward in the code.

//if the submit button has been hit, do this

if (isset($submit_changes)) 
{ $sql="UPDATE student_info SET        first_name='$edit_first',last_name='$edit_last',class_yr='$edit_class',
web_site='$edit_web',AC='$edit_ac',employer='$edit_employer',
other='$edit_other' WHERE id = '$update_id' ";

$updateresult = mysql_query($sql,$db);

if (!$updateresult)  //if the update was unsuccessful
    echo mysql_error(); 

                    //tell the user he information has been updated
else{
    echo "<font size='5'color='#000080'><i><p>Information 
    Edit Confirmation</i>";
    echo "</font></p><br>";
    echo "<b>$edit_first $edit_last</b>, your changes have been 
    submitted.<p>";
    echo "<br>To return to the Computer Science Student listing and 
    see your";
    echo " changes, click      
    <a href='http://websci.smith.edu/~orourke/cs_list.html'>";
    echo "here </a>.";
} 
}

Since isset has a value, the page does not execute the command that displays the form.

Now we have to consider when students wish to be removed from the listing

Deleting Information from the Database

The DELETE clause is a powerful tool in databases and one that should not be used without careful thought. It has the power to permanently remove data that fits a specific criteria. One can run into trouble if they do not carefully specify which information should be deleted and therefore possibly delete information which they intended to keep. Another consideration is how easily this deleting ability is given to users. 

To reduce the risk of inadvertently deleting important information, a DELETE clause was carefully planned and tested in order to delete only the information associated with the first name, last name, and class year as enterred by a user. 

$deleteresult = mysql_query ("DELETE * FROM student_info
                              WHERE first_name = '$first'
                              AND last_name = '$last'
                              AND class_yr = '$class' "); 

To address the second consideration as to the ease with which users could delete information, it was decided that users should not be given this ability. Since there is no real way, at this point, to track who would be doing the deleting, it seemed unwise to give this power to users. Seemingly anyone could come to the site, type in a students name who is on the list and delete her information. In order to avoid this, the power to directly delete information from the database was not given to users and the DELETE clause made obsolete in this case. Instead, users enter their first name, last name, and class year into a form in the cs_delete.html file and this information is sent to the cs_delete.php file which in turn sends an email to the webmaster telling them of the users desire to delete her information. the webmaster then manually deletes the student's information.

<?

//message for user (displayed on screen)

echo "<font size='5'color='#000080'><i>Deletion Request Confirmation</i>";
echo "</font></p><br>";
echo "<b>$first_name $last_name</b>, your deletion request has been ";
echo "sent to our webmaster and will be <br> processed shortly. <p>";

//message for webmaster (sent in email)

$body = "$first_name $last_name of class $class_yr has requested that her";
$body = "$body information be deleted from the CS Student Listing.";

//email command

mail("orourke@cs.smith.edu", "Deletion Request",$body,"From: cs_list@smith.edu");
?>

The CS Student Listing

To display the information in the cs_list database in a logical manner, we first have to extract the data from the database, sort it, and then display it.

Extracting and Sorting the Information

In order to extract the information, we first connect to the database and then, using a SELECT statement, we extract all pertinent information in the database. Within this same statement we are able to sort the information using the clause ORDER BY as seen below:

<?php
$db = mysql_connect (localhost, orourke, orourke);

mysql_select_db (cs_list);



$result = mysql_query ("SELECT * FROM student_info

ORDER BY class_yr DESC, last_name, first_name ");

This orders the information first by class year in descending order and then alphabetically first by last name and then by first name. This was done so that the each class year would be in a group thereby making displaying it easier. If the page could find the information for each class year grouped together as it would be using this ORDER BY clause, then it would be easier to display and delineate the years by simply looking for a change in the class year field.  

The following code displays the information:

$current_year = 0;       //this variable changes when it no longer 
                         //equals the value in the class_yr field. 
                         //This variable is used in the heading for 
                         //each class.

$this_year = date("Y");   //this variable keeps track of this year

if (!$result)

echo mysql_error();

else

{$year_change = -1;       //this variable increases when a year 
                          //change occurs and it used for formatting 
                          //the information. A year change occurs 
                          //when $current_year no longer equals the 
                          //class_yr field of the current row.

$alumnae = 0;              //this variable changes once a class year 
                           //has been reached that is older than this 
                           //year. This is used for adding in the 
                           //Alumnae heading over previous class 
                           //years.

while ($row=mysql_fetch_array($result)) 

//while there is still in the variable $row, print it with the 
//following conditions and formatting.

{ $web = $row["web_site"];

if ($current_year != $row["class_yr"])

{ $current_year =$row["class_yr"];   //change $current_year to the 
                                     //year in the class_yr field so 
                                     //that it can be used in the 
                                     //heading for this year.

$year_change++;

if (strcmp ($row["class_yr"], $this_year) < 0)
$alumnae++;               //if the year in the class_yr field is 
                          //older than this year, start alumnae  
                          //section.

//header for alumnae section

if ($alumnae == 1){
echo "<tr>";
echo "<td colspan=2>";
echo "<hr>";
echo "<p>";
echo "<center>&nbsp;<b><font color='#000099'><font size=+2>";
echo "ALUMNAE<br>&nbsp;<hr width=85%></font></font></b>";
echo "</center>";
echo "</td>";
echo "</tr>";

if ($year_change < 4)     //used for formatting after alumnae header
{ echo "<tr><td>";
  $year_change ++; }
}

if ($year_change % 4 == 0)  //used for formatting after a year change
echo "<tr><td valign=top width=300>";

else if ($year_change % 2 == 0)    //used for formatting after a year 
echo "<td valign=top width=300>";  //change

//header for a new class year

echo "<p><b>Class of "; 

echo $current_year; 

echo "</b><p>";

//information for first student in this class year is printed here. //Information for subsequent students will be added in another part //of the loop.

echo "<img src='sphere05.gif'>";

echo "&nbsp;&nbsp;&nbsp;&nbsp;";

if (!empty ($row["web_site"]))

echo "<a href = '$web'>";

echo $row["last_name"].", ";

echo $row["first_name"]." ";

if (!empty ($row["web_site"]))

echo "</a>";

if ($row["AC"] == 1)
echo " (AC)";

if (!empty ($row["other"]))

{ echo " ";
echo "<i>";
echo $row["other"];
echo "</i>";
echo " ";
}

if (!empty ($row["employer"]))

{ echo " (";
echo $row["employer"];
echo ") ";
}
echo "<br>";
}

else

{ 

//information for subsequent students is printed here

echo "<img src='sphere05.gif'>";

echo "&nbsp;&nbsp;&nbsp;&nbsp;";

if (!empty ($row["web_site"]))

echo "<a href = '$web'>";

echo $row["last_name"].", ";

echo $row["first_name"]." ";


if (!empty ($row["web_site"]))

echo "</a>";

if ($row["AC"] == 1)
echo " (AC)";

if (!empty ($row["other"]))

{ echo " ";
echo "<i>";
echo $row["other"];
echo "</i>";
echo " ";
}

if (!empty ($row["employer"]))

{ echo " (";
echo $row["employer"];
echo ") ";
}
echo "<br>";
}


}

if ($year_change % 4 == 0)  //end formatting for a year change
echo "</tr>";

else if ($year_change % 2 == 0) //end formatting for a year change
echo "</td>";

}

mysql_close ($db);              //close database connection when done
?>
<tr>
<td colspan=2>
<?php
include ("footer.txt");
?>
</td>
</tr>
</table>               //end the table

This code runs every time the CS Listing page is opened. Each time you open the page the newest information is displayed dynamically without any intervention of the webmaster.

Conclusion

This is a fairly effective system because it allows students to have control over the information displayed on the page and does not require the webmaster to do very much to maintain it besides perform deletion actions when necessary. The formatting is also automatic which is a significant time saver as well. To improve this project further one might consider combining many of the functions in the HTML and PHP pages into one page as I did in the cs_edit.php page. It might also be fruitful to device some form of security to insure the integrity of the information in the database more so than has been done here and perhaps in doing so, allow students to delete their own information thereby reducing the interaction of the webmaster even more.

If you have nay questions, please feel free to email me at jbuck@email.smith.edu.