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); 
?>