I am mucking around with creating a mysql web application

When trying out all sorts of queries it’s sometimes nice to have them output to HTML for viewing in a web browser

This file (named myscript.sh) shown below goes in your cgi-bin or other directory which has a mapping for executing bash scripts.

If you put it in your cgi-bin then you should be able to access it from http://yourhost/cgi-bin/myscript.sh

check the comments in the file.

#!/bin/bash
 
# tell the webserver what script interpretor to use. bash in this case.
 
# tell the web client what to expect (html)
echo -e "Content-type: text/html\r\n\r\n";
 
TITLE="Map History 2"
#
# uncomment env to see what your environment is like
# env
 
# this function chops the query string up and sets variables to use later in script 
function getArgs {
	#	echo $1
        # set bashes internal field separator to the `&' character
	IFS=\&
 
	for i in $QUERY_STRING
	do
 
		KEY=`echo $i | cut -f1 -d=`
		# echo KEY = $KEY
		VAL=`echo $i | cut -f2 -d=`
		if [ "$KEY" == "$1" ] ; then
			# echo Inside if 
			# echo $VAL
			echo $VAL
			break
		fi
	done
 
}
 
# tell the getArgs function you want the MAPNUM key value of the query string
# e.g. if you call the script as http://yourhost/cgi-bin/myscript.sh?MAPNUM=1-02&BLAH=12&FOO=bah
# it will grab out MAPNUM and set the value 1-02 in the variable MAP.
MAP=$(getArgs MAPNUM)
 
# now echo out the html for the page
echo ""
 
# this is just so you can see MAP has been set properly
echo MAP = $MAP
 
echo 'Go to index
'
 
echo "
$TITLE
"
 
# echo the SQL query straight into mysql client with -H to tell mysql client to output in HTML.
# note the use of connection user and password isn't a great idea for security
echo "select t1.TerrNo, t2.suburbName as Locality, t3.dateLoaned, t3.dateReturned from maps as t1 left join suburbs as t2 on t1.suburbIDFK = t2.suburbID RIGHT JOIN mapHistory as t3 ON t3.mapIDFK = t1.mapID WHERE t1.TerrNo =" \'$MAP\'" ORDER BY t3.mapHistID ASC ;" | mysql -hlocalhost -pdbpass -udbuser mydb -H
 
# finally end the html page.
 
echo ""