Writing a Front End for Claims_DB Part 3 - add_record.sh

Ben Tasker

2009-06-20 14:10 (updated 2019-05-24 12:28)

This content was originally published on benscomputer.no-ip.org

Creating A Claims_DB Front End Tutorial Part 2 - Index Page

Part 3 - Add Records

In the previous section of this tutorial we created an Index page. The first button takes the user to a CGI script that allows them to add a record. That is what we will be creating here, depending on the functionality you want, you could simply create a HTML page for this, but we are looking for something more advanced

First lets create the script headers

#!/bin/bash
#
# add_record.sh
#
# Part of the Claims_DB Sample front end
# Copyright Ben Tasker 2009
# Released under the GNU GPL
# See http://benscomputer.no-ip.org/LICENSE
#


# Define your variables here

# Where did you install Claims_DB?
CLAIMS_PROGS="/home/ben/programs/Claims_DB/"

# The name of the Database we are using
DBNAME="Sample_DB"



# Tell the browser our MIME Type
echo Content-type: text/html
echo ""


# Generate the HTML

/bin/cat << EOM
<html><head><title>Claims_DB Sample Database - Add Record</title>
</head><body bgcolor="white">
<center><b>Claims_DB Sample Database - Add Record</center></b>
<br><br>
EOM

Now we need to identify what the Serial Number of the record will be. This is the primary key for the Catalogue table, and there are two main ways of achieving it. When we created the database we could have created a second file called Catalogue_PK and told our front end to read and write primary keys to that. Whenever a record is added we would write that Primary Key to it. Then when we want to create a new record we would read the number back and then add 1 to it.

Whilst this method works, it's not compatible with the way that the database engine will create primary keys in the future. So we are going to get our front end to do more or less exactly what the database engine will do.

# What will our Primary Key be?

# Read the entire table
DBROOT="$DBNAME" TABLE="Catalogue" "$CLAIMS_PROGS"/bin/read_records_claims.sh -read > 
/tmp/LOCATIONS.tmp

# PK is in column 1
# Now seperate out Column 1, and remove duplicates
awk -F\, '{print $1}' /tmp/LOCATIONS.tmp > /tmp/LOCATS2.tmp


# Strip out the text 
delimeter - "
sed s/\"//g /tmp/LOCATS2.tmp > /tmp/LOCATS.tmp

# Now lets sort it
cat /tmp/LOCATS.tmp | sort -n -k 1 > /tmp/LOCATS2.tmp

# What is the 
final entry, load it into a variable

LASTKEY=$( cat /tmp/LOCATS2.tmp | tail -n 1 )

# Work out the next key with a simple bit of maths

NEWKEY=$(( $LASTKEY + 1 ))

# Tidy up

rm -f /tmp/LOCATS.tmp
rm -f /tmp/LOCATS2.tmp
rm -f /tmp/LOCATIONS.tmp

Now that we know what the Primary Key will be, lets continue to generate our form. We don't want the user to be able to change the PK (too much chance of a clash) but do want to a) tell them what it'll be and b) send the key with the form. So

# Continue to generate the HTML

<FORM ACTION="add_process.sh" METHOD="GET">
Record ID: $NEWKEY
<br><br>
<INPUT TYPE="hidden" NAME="RecordID" VALUE="$NEWKEY">
Part Description: <INPUT TYPE="text" NAME="Desc">
<br>Part Location: <INPUT TYPE="text" NAME="Locat">
<br>Part Number: <INPUT TYPE="text" NAME="Partno">
EOM

Now for the number type the Database uses two possible values - OEM or Stockist. Lets provide the user with a drop down box to select the valid option, it'll also mean we can reduce the length of the Request URI when the form is submitted

# Use drop down box for Number type
/bin/cat << EOM
Part Number Type: <select name="NoType">
<OPTION VALUE="0">OEM</OPTION>
<OPTION VALUE="1">Stockist</OPTION>
</select>
EOM

We'll convert the ID's 0 and 1 back to their true meaning when we parse the submission later. For now lets finish the rest of the form

# Generate rest of form
/bin/cat << EOM
Stockist: <INPUT TYPE="text" NAME="Stkis">
<br>
Qty in Pack: <INPUT TYPE="text" NAME="PPQ">
<br>
Qty on Bike: <INPUT TYPE="text" NAME="Need">
<br>
Rating: <INPUT TYPE="text" NAME="Rate">
<br>
Bike: <INPUT TYPE="text" NAME="bike">
<br><br>
<INPUT TYPE="submit" VALUE="Save Record">
</form>
<br><br>
</body>
</html>
EOM

# Generation finished
exit

And in those few easy steps, we've created our Add Record form. There's no input validation or anything, which is something you'll probably want to implement, but again that's a little outside the scope of this tutorial.

You can grab a full copy of this source here.

Claims_DB Front End Tutorial Part 4 - add_process.sh

Related Snippets