We want to direct “traffic” to where we want them to go, so we are going to add a navigational component called a “switchboard”.
Any page that’s called index.htm or index.php is going to show up first, when someone enters that URL.
That looks more professional than just seeing a list of files in that folder.
A website is just a folder on a computer, and in order that you don’t see all the files in that folder, you are automatically sent to the index page.
So we create a main switchboard index page with a php extension, because I probably want to program it in the near future, otherwise we could have just used a basic htm page.
The new page we are planning to send the user to is the doctors.py page, which is basically just a copy of the providers python script that we created earlier.
Now here is the code for doctors.py
#!/usr/bin/python
print "Content-type: text/html\n\n"
print "<html>"
print "<head>"
print "<title>Doctors</title>"
print "</head>"
print "<body>"
#GLOBAL VARIABLES
USERNAME = 'username'
PASSWORD = 'password'
DB = 'db_name'
import MySQLdb
import os
# Import modules for CGI handling
import cgi, cgitb
def init():
#read the query params
params = os.environ.get('QUERY_STRING')
#RECORD ALREADY EXISTS
#------------------------------------------------------------------------------------
#["id"] - just selects the "id" parameter, but the [0] selects the actual value
arg = cgi.parse_qs( params )["id"][0]
#["action"] - just selects the "action" parameter, but the [0] selects the actual value
action = cgi.parse_qs( params )["action"][0]
#["ltype"] - just selects the "ltype" parameter, but the [0] selects the actual value
ltype = cgi.parse_qs( params )["ltype"][0]
#------------------------------------------------------------------------------------
#Python has no switch case statement
if action == 'edit':
#---------------------------------------------------------------
#1. show the clicked record.
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql = "SELECT * FROM tblDoctors WHERE doc_id=" + arg
cursor.execute(sql)
for row in cursor:
fname = row[1]
city = row[2]
print "****************EDIT RECORD****************"
# Close the connection
conn.close()
edit_form(arg,fname,city)
#show_table()
if action == 'update':
#---------------------------------------------------------------
#2.Update the new values
#---------------------------------------------------------------
form = cgi.FieldStorage()
# Get data from fields
if form.getvalue('fname'):
fname = form.getvalue('fname')
else:
fname = "Not set"
if form.getvalue('city'):
city = form.getvalue('city')
else:
city = "Not set"
update_values(fname,city,arg)
edit_form(arg,fname,city)
#show_table()
if action == 'delete':
fname = ''
city = ''
delete_values(arg)
edit_form(arg,fname,city)
#show_table()
if action == 'create':
if ltype == 'b':
#print 'create the new record.<br>'
fname = ''
city = ''
form = cgi.FieldStorage()
# Get data from fields
if form.getvalue('fname'):
fname = form.getvalue('fname')
else:
fname = "Not set"
if form.getvalue('city'):
city = form.getvalue('city')
else:
city = "Not set"
insert_values(fname,city)
edit_form(arg,fname,city)
elif ltype == 'l':
add_form()
#print ltype + ' showtable<br>'
#show_table()
if action == 'list':
add_form()
#show_table()
show_table()
def edit_form(arg,fname,city):
#read the values from the database
print "<h2>Edit Record</h2>"
print "<form action = 'doctors.py?id=" + arg + "&action=update<ype=0' method = 'post'>"
print "<table width='100%' border='0px' bgcolor=lightgreen>"
print "<tr>"
print "<td><strong>First Name:</strong></td>"
print "<td><input type = 'text' name = 'fname' value='" + fname + "'></td>"
print "</tr>"
print "<tr>"
print "<td><strong>City:</strong></td>"
print "<td><input type = 'text' name = 'city' value='" + city + "'></td>"
print "</tr>"
print "<tr>"
print "<td><input type = 'submit' value = 'Edit' /></td>"
print "</tr>"
print "</table>"
print "</form>"
#show_table()
def add_form():
#CREATE THE NEW RECORD ONLY IF THE ACTION IS CREATE
#default values
fname=''
city=''
print "<h2>New Record 3</h2>"
print "<form action = 'doctors.py?id=0&action=create<ype=b' method = 'post'>"
print "<table width='100%' border='0px' bgcolor=lightgreen>"
print "<tr>"
print "<td><strong>First Name:</strong></td>"
print "<td><input type = 'text' name = 'fname' value='" + fname + "'></td>"
print "</tr>"
print "<tr>"
print "<td><strong>City:</strong></td>"
print "<td><input type = 'text' name = 'city' value='" + city + "'></td>"
print "</tr>"
print "<tr>"
print "<td><input type = 'submit' value = 'Submit' /></td>"
print "</tr>"
print "</table>"
print "</form>"
#show_table()
def update_values(firstname,cty,arg):
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
#Python will handle the escape string for apostrophes and other invalid SQL characters for you
sql = "UPDATE tblDoctors SET doc_name = %s, doc_city = %s WHERE doc_id =%s"
cursor.execute(sql, (firstname, cty,arg))
print '**************Record Updated**************'
#Commit your changes
conn.commit()
# Close the connection
conn.close()
def delete_values(id):
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
cursor = conn.cursor()
sql="DELETE FROM tblDoctors WHERE doc_id=" + id
cursor.execute(sql)
#Commit your changes
conn.commit()
print "****************RECORD DELETED****************"
# Close the connection
conn.close()
def insert_values(firstname,cty):
conn = MySQLdb.connect('localhost',USERNAME,PASSWORD,DB)
conn.autocommit(True)
cursor = conn.cursor()
#Python will handle the escape string for apostrophes and other invalid SQL characters for you
sql = "INSERT INTO tblDoctors (doc_name,doc_city) VALUES (%s, %s)"
cursor.execute(sql, (firstname, cty))
print '**************Record Added**************'
#Commit your changes
conn.commit()
# Close the connection
conn.close()
def show_table():
conn = MySQLdb.connect('localhost', USERNAME,PASSWORD,DB)
curs = conn.cursor()
sql="SELECT SQL_NO_CACHE * FROM tblDoctors ORDER BY doc_id"
curs.execute(sql)
#rows = cursor.fetchall();
# Get the number of rows in the result set
rows = curs.fetchall();
numrows = curs.rowcount
print "numrows = " + str(numrows) + '<br>';
print "<table width='100%' border='1px'>"
print "<th width=3%></th><th width=3%></th>"
print "<th>First Name</th><th>City</th>"
# Get and display all the rows
for row in rows:
id = row[0]
print '<tr>'
print '<td>'
print "<a href='doctors.py?id=" + str(id) + "&action=edit<ype=0'>Edit</a>" #need to convert the index to a string
print '</td>'
print '<td>'
print "<a href='doctors.py?id=" + str(id) + "&action=delete<ype=0'>Delete</a>" #need to convert the index to a string
print '</td>'
print '<td>' + row[1] + '</td>'
print '<td>' + row[2] + '</td>'
print '</tr>'
print '<tr>'
print '<td colspan=3>'
print "<a href='doctors.py?id=0&action=list<ype=0'>Add New Record</a>"
print '</td>'
print '</tr>'
print '</table>';
cursor.close()
# Close the connection
conn.close()
#start here:
init()
print "</body>"
print "</html>"
Watch how it’s done:
