Adding Records to MySQL Databases With HTML/PHP

In this article, we loaded up our new MySQL database with data. Adding records from a CLI tool is a bit cumbersome. PHP is a great way to add records to a MySQL database. For more information on PHP, see our articles here. The first step in using PHP is to set up the database connection. We created a sysdoc directory off of the root web documents for this article. Here is how our connection file is set up:

root@srv-1 sysdoc # cat config.php
<?php include("../../config.sysops.php"); ?>
root@srv-1 sysdoc # cat ../../config.sysops.php
<?php
$dbhost = "localhost";
$dbuname = "root";
$dbpass = "xxx";
$dbname = "sysops";
?>
root@srv-1 sysdoc #

The reason to use the include file is that you don’t want the password file in any directory that your webserver has access to. If you have php code on your site, one of the first thing somebody will try and grab with an exploit is config.php. If that file has your database password in it, you are up a creek. We are using root as the user, because this database is not in production. Certainly you would want to create another user for your site. We need two files to add a record. Here is the HTML document sysdocadd.html:

<html>
<head>
<title>SystemsDoc Add</title>
</head>
<body bgcolor="white">
<form method="POST" action="sysdocadd.php">
<table>
<col span="1" align="right">
<tr>
<td><font color="blue">Manufacturer:</font></td>
<td><input type="text" name="manu" size=100></td>
</tr>
<tr>
<td><font color="blue">Model:</font></td>
<td><input type="text" name="model" size=100></td>
</tr>
<tr>
<td><font color="blue">Address:</font></td>
<td><input type="text" name="addr" size=100></td>
</tr>
<tr>
<td><font color="blue">Zip:</font></td>
<td><input type="text" name="zip" size=100></td>
</tr>
<tr>
<td><font color="blue">Phone:</font></td>
<td><input type="text" name="phone" size=100></td>
</tr>
<tr>
<td><font color="blue">Deployment Date:</font></td>
<td><input type="text" name="deploy_date" size=100></td>
</tr>
<tr>
<td><font color="blue">Serial Number:</font></td>
<td><input type="text" name="sernum" size=100></td>
</tr>
<tr>
<td><font color="blue">Asset Number:</font></td>
<td><input type="text" name="assetnum" size=100></td>
</tr>
<tr>
<td><font color="blue">Machine Name:</font></td>
<td><input type="text" name="machname" size=100></td>
</tr>
<tr>
<td><font color="blue">System Version:</font></td>
<td><input type="text" name="sysversion" size=100></td>
</tr>
<tr>
<td><font color="blue">UID:</font></td>
<td><input type="text" name="UID" size=100></td>
</tr>
<tr>
<td><input type="submit" value="Submit"></td>
</tr>
</table>
</form>
</body>
</html>

Here is the PHP document sysdocadd.php:

<html>
<head>
<title>SystemsDoc Add</title>
</head>
<body bgcolor="white">
<?php
foreach($HTTP_POST_VARS as $varname => $value)
$formVars[$varname]=$value;
require_once("config.php");
$db1=mysql_connect($dbhost, $dbuname, $dbpass);
mysql_select_db("sysops");
echo "Record Added<br><a href=\"sysdocadd.html\">click here</a>
to return to sysops<br>";
$query="INSERT INTO systemsdoc set ".
"manu= \"".$formVars["manu"]."\",".
"model= \"".$formVars["model"]."\",".
"addr= \"".$formVars["addr"]."\",".
"zip= \"".$formVars["zip"]."\",".
"phone= \"".$formVars["phone"]."\",".
"deploy_date= \"".$formVars["deploy_date"]."\",".
"sernum= \"".$formVars["sernum"]."\",".
"assetnum= \"".$formVars["assetnum"]."\",".
"machname= \"".$formVars["machname"]."\",".
"sysversion= \"".$formVars["sysversion"]."\",".
"UID= \"".$formVars["UID"]."\"";
mysql_query($query);
mysql_close($db1);
?>
</body>
</html>

Here is what this looks like:

Let’s verify the record:

u-1@srv-1 u-1 $ mysql -u root -p sysops
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 128 to server version: 4.0.14-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select manu, model, sernum, sysversion from systemsdoc
-> where UID=8;
+-----------------+---------+--------+--------------+
| manu            | model   | sernum | sysversion   |
+-----------------+---------+--------+--------------+
| Sam's Computers | GX 1000 | 230948 | Windows 2003 |
+-----------------+---------+--------+--------------+
1 row in set (0.00 sec)
mysql>

Adding Records to MySQL Databases With HTML/PHP

Information

About