Insert data to table faculty

It is now time to insert data to the table faculty after we have created the database and table in the previous tutorial which is the Laboratory activity database and table. To insert data to the database table in MySQL you need to have knowledge in using SQL or structured query language. In this case, the INSERT INTO SQL statement will be used. To insert data in the table faculty using the MySQL command line type the statement shown below.

mysql> INSERT INTO faculty (fid, firstname, middlename, lastname)
VALUES ('F000000001', 'Mario', 'Aberde', 'Sanz');

Using the command line in inserting data to the database table is tedious and difficulty especially for non-Information Technology people. Don’t worry so much, we are going find ways so that it becomes user friendly for our user to insert data to the database. We are going to create a graphical user interface that can be used to insert data to the database table.

HTML will be used to create an interface with a text box and button so that the user can input or key in data to the text box, the click the button in the form to easily store or save the data to database. The logic of inserting the data to database is that the form that contains text boxes and button will be developing using html. PHP script will be used to manage the data, by connecting to the MySQL database, and then make a channel to get the data that is encoded in the form and insert it to the database table. The html code below is for the interface when the user wishes to encode the data. You are going to save this html file with a filename “frmaddfaculty.html”

<HTML>
<HEAD>
<TITLE>Insert data to table faculty</TITLE>
</head>
<body>
<h1>Add Faculty Records</h1>
<hr>
  <form action="addfaculty.php" method="POST">
<table>
<tr>
<td> <b><font size="2">Faculty ID:</font></b></td>
<td>
<input size="10" maxlenght="15" type="text" name="txtfid">
</td>
</tr>
<tr>
<td><b><font size="2">First Name:</font></b></td>
<td>
<input size="20" type="text" name="txtfname">
</td>
</tr>
<tr>
<td><b><font size="2">Middle Name:</font></b></td>
<td>
<input size="20" type="text" name="txtmname">
</td>
</tr>
<tr>
<td><b><font size="2">Last Name:</font></b></td>
<td>
<input size="20" type="text" name="txtlname">
</td>
</tr>
  <tr>
<td colspan=2>
<input type="Submit" name="submit" value="Add">
  </td>
</tr>
</table>
</form>
</BODY>
</HTML>

In the form action, the string "addfaculty.php" is the php file to be look for by the program when the user click the button. That file is need to be created, and the code is listed below. Note, the filename when saving is file is ="addfaculty.php".

<?php
// open database conection
$connection = mysql_connect("localhost","root") or die ("Unable to connect!");

// select database
mysql_select_db("scheduledb") or die ("Unable to select database!");

$fid=$_POST['txtfid'];
$fname=$_POST['txtfname'];
$mname=$_POST['txtmname'];
$lname=$_POST['txtlname'];


if (trim($_POST['txtfid'])==""){
echo "Data was not save. Please input acoount no.";
                exit;
        }

       if (trim($_POST['txtfname'])==""){
echo "Data was not save. Please input first name.";
                exit;
        }

if (trim($_POST['txtmname'])==""){
echo "Data was not save. Please input middle name.";
                exit;
        }

if (trim($_POST['txtlname'])==""){
echo "Data was not save. Please input last name.";
                exit;
        }
  // generate and execute query
$query = "INSERT INTO faculty(fid,fname,mname,lname) VALUES('$fid','$fname','$mname','$lname')";
$result=mysql_query($query) or die("Error in query: $query. " . mysql_error());
echo '<font size="3" color="red">Data was save successfully.</font>';
//close database connection
mysql_close($connection); 
?>

Laboratory activity database and table

This is my first laboratory in my class database management system. In this computer laboratory activity, students are going to create their database and table in MySQL command prompt. It is good for the student to start learning SQL commands using the command prompt before introducing them to some other tools or interface that is use to create database like the Apache xampp.

The student s in this laboratory activity will create a computerized teacher schedule, it is a web database application that can store details about the schedule of a teacher such as the room, day, time and the subject taught. The data will be stored in MySQL database, PHP will be used to manipulate the data in database and to perform operations such insert, select, update and delete. HTML will be used to create the graphical interface to be used by user of the system.

The create database sql statement will be used in creating he database, and the create table command will be used to create the database table. In this application, three tables will be created; the following tables are the faculty, room, and schedule. These tables will serve as the basic storage of data about faculty, room, and schedule. Below is the conceptual design of a computerized faculty schedule.
  • faculty(faculty_id, firstname, middlename, lastname)
  • room(room_id, roomname)
  • schedule(index, faculty_id, room_id, day, time, subject_taught )

For the faculty table, the faculty_id will serve as the primary key.  IN the table room the primary key will be the room_id. The schedule tabe will become the intersection table, index will be the primary key. The faculty_id and room_id in the schedule table are the foreign keys and they are used to established a relationship to the faculty and room table. The syntax or command to create database and table in MySQL command line is:

mysql> CREATE DATABASE scheduledb;

mysql> use scheduled;

mysql>CREATE TABLE faculty (
    fid varchar(10),
    fname varchar(20),
    mname varchar(20),
    lname varchar(20),
   PRIMARY KEY (fid)
);

mysql>CREATE TABLE room (
    room_id varchar(10),
    room_name varchar(20),
   PRIMARY KEY (room_id)
);

mysql>CREATE TABLE schedule (
    index int,
    fid varchar(10),
    room_id varchar(10),
   day varchar(20),
   time varchar(20),
   subject_taught(20),
   PRIMARY KEY (index) auto increment

);