Creating Databases for Web Applications

1
Creating Databases for Web Applications

• SQL Select extras
• Listing names of tables
• generalized display of recordset
• simple password handling
• php and other databases
• Homework work on projects, new old posting
  assignments

2
GetImageSize example

• Addition to code shown last time
• sizeGetImageSize(file)
• print ("Dimensions are ".size0." by
  ".size1." pixels.ltbrgt")
• area size0size1
• print ("Area is area pixels.ltbrgt")

3
results (first part)

• uploading file named 91940.jpg
• File size is 56866
• fullname is D\\InetPub\\wwwroot\\users\\jeanine\
  \\91940.jpg.
• Dimensions are 600 by 393 pixels.
• Area is 235800 pixels.
• file successfully uploaded.

4
Select operators

• , gt, lt, gt, lt, ! or gtlt
• IS NOT NULL, IS NULL
• BETWEEN
• IN, NOT IN
• LIKE (has wild card character , others)
• REGEXP
• Also, have DISTINCT
• SELECT DISTINCT category FROM questions

5
Select aggregate functions

• AVG, COUNT, MIN, MAX, STD, SUM
• SELECT AVG(score) in players
• SELECT COUNT() in players WHERE score gt 100
• Get these as 0th field, 0th row of recordset
• SELECT AVG(score), MIN(score), MAX(score),
  STD(score), COUNT(score) in players
• Get these as 0th, 1st, 2nd, 3rd, 4th, 5th fields
  of 0th row of recordset

6
Select control

• grouping
• SELECT order_id, SUM(quantity) FROM ordereditems
  GROUP BY order_id
• limit
• SELECT product_id, quantity FROM ordereditems
  LIMIT 10
• limit starting from 1st record fitting
  conditions and returning 10 records
• SELECT product_name, product_description,
  product_cost FROM catalog LIMIT 1, 10
• For paging, repeat with variables indicating 1st
  and last entries
• "SELECT product_name, product_description,
  product_cost FROM catalog LIMIT FIRST, 10"

7
SELECT order_id, SUM(quantity) FROM ordereditems
GROUP BY order_id
Original data

• Query result
• 11
• 9

8
What are tables in given database

• lthtmlgtltheadgtlttitlegtShow table nameslt/titlegt
  lt/headgt ltbodygt
• lt?php
• require("jeanine\quizphp\opendbq.php")
• query"show tables"
• rsmysql_db_query(DBname, query, link)
• ?gt
• lttablegt lttrgt
• lttdgt Table names lt/tdgt lt/trgt
• lt?
• while (rowmysql_fetch_array(rs))
• print("lttrgt lttdgt")
• print(row0)
• print("lt/tdgtlt/trgt")
• print("lt/tablegt")
• ?gt
• ltbrgt lt/bodygt lt/htmlgt

9
result

• Table names
• catalog
• customers
• history
• ordereditems
• orders
• players
• questions

10

• lthtmlgt
• ltheadgtlttitlegtShow table names and field
  nameslt/titlegt lt/headgt
• ltbodygt
• lt?php
• require("jeanine\quizphp\opendbq.php")
• query"show tables"
• rsmysql_db_query(DBname, query, link)
• ?gt
• lttable border1gt lttrgt ltthgt Table names lt/thgt
  lt/trgt
• lt?
• i 0
• while (rowmysql_fetch_array(rs))
• print("lttrgt lttdgt")
• tablenamesi row0 i
• print(row0)
• print("lt/tdgtlt/trgt")
• print("lt/tablegt")

11
for (j0jltij) query "describe
".tablenamesj print ("ltpgtlth2gt" .
tablenamesj. " table lt/h2gtlttable border1gt\n
") print ("ltthgt Field lt/thgt ltthgt Type lt/thgt
ltthgt Null lt/thgt ltthgt Key lt/thgt \n ")
rtmysql_db_query(DBname,query,link) while
(fimysql_fetch_array(rt)) print ("lttrgt
lttdgt". fi'Field' . "lt/tdgt\n ") print
("lttdgt".fi'Type' . "lt/tdgt\n ") print
("lttdgt".fi'Null' . "nbsplt/tdgt\n ") print
("lttdgt".fi'Key' . "nbsplt/tdgt\n ") print
("lt/trgt") print ("lt/tablegtltpgt")
?gt lt/bodygt lt/htmlgt
12
(No Transcript)
13
Table of queries

• If you have a large set of fixed SQL queries, you
  may make a new table

14
Present to user

• Pick selection

Don't show the user the messy SQL
15
Produce responses

• Make the query the SQL corresponding to the
  user's choice.
• Display recordset in a table
• Now, need generalized code that creates headings
  for tables and extracts names of fields 'on the
  fly' based on information in recordset.
• php
• mysql_fetch_field
• mysql_fetch_array

16
lthtmlgtltheadgtlttitlegtCurrent Favorites lt/titlegt
lt/headgt ltbodygt lttablegt lt?php require("openfirstdb.
php") query"Select from favorites" result
mysql_db_query(DBname, query, link)
fieldnames Array() print ("lttable
border1gtlttrgt") nf mysql_num_fields(result)
for (i0 iltnfi) fieldobj
mysql_fetch_field(result) fieldnamesifiel
dobj-gtname print ("ltthgt".fieldnamesi.
"lt/thgt") print ("lt/trgt\n") while
(rowmysql_fetch_array(result)) print
("lttrgt") for (i0 iltnf i) print
("lttdgt".rowfieldnamesi."lt/tdgt")
print("lt/trgt") mysql_close(link)
?gt lt/tablegt lt/bodygtlt/htmlgt
first for loop to set up headers
Second for loop, in while loop, to extract field
data.
17
(No Transcript)
18
asp version

• recordset.fields.count
• recordset.fields(i).Name

19
lt_at_ LanguageJavaScript gt lthtmlgtltheadgtlttitlegtInpu
t and submit questions to quizasp db
lt/titlegtlt/headgtltbodygt lt!-- include
file"openfirst.asp" --gt lttablegt lt var sq
"SELECT from favorites" rsServer.CreateObject
("ADODB.RecordSet") rs.Open (sq,Conn, 1,3) var
fieldnames new Array() Response.Write ("lttable
border1gtlttrgt") var nf rs.fields.count var
nrrs.RecordCount for (i0 iltnf i)
fieldnamesirs.fields(i).Name Response.Writ
e("ltthgt" fieldnamesi "lt/thgt")
Response.Write ("lt/trgt\n") while(!rs.EOF)
Response.Write("lttrgt") for (j0 jltnf j)
Response.Write ("lttdgt"rs.fields.item(fieldn
amesj)"lt/tdgt") Response.Write("lt/t
rgt") rs.move(1) gt lt/tablegt lt/bodygtlt/htmlgt
20
Authentication using passwords

• Technique is to establish a table of stored user
  names and encrypted passwords
• one way encrpytion
• php's crypt or MySql's password
• use SQL statement that counts the number of
  records with the pair of values. If count is
  greater than 0, then the person is accepted.
• Use session variables or cookies to check that
  user is 'authenticated'.
• Separate procedure for storing values.

21
php crypt

• Can be used with or without a seed
• cipher crypt(password,seed)
• You need to make sure that the seed is the same!

22
SQL
calculated value.

• query "Select count() from passtable where
  name 'user' and pass 'cypher'"
• resultmysql_query(Dbname,query)
• count mysql_result(result,0,0)
• if (countgt0)
• .okay
• else . no good

recordset has one row, one field
23
php and other databases

• php and MySQL have a special set of functions.
  There are also special sets for some other
  databases.
• show some Oracle code
• Alternative is to use a general API (application
  programming interface).
• ODBC open database connectivity
• ADODB active data object data base
• ?

24
lt?php PutEnv("ORACLE_SIDORAS
ID") connection
Ora_Logon ("username","password")
if (connection false)
echo Ora_ErrorCode(connection)."
".Ora_Error(connection)."ltBRgt"
exit
cursor Ora_Open (connection)
if (cursor false)
echo Ora_ErrorCode(connection)."
".Ora_Error(connection)."ltBRgt"
exit
query "select from email_info"
result Ora_Parse (cursor,
query) if (result
false) echo
Ora_ErrorCode(cursor)." ".Ora_Error(cursor)."ltB
Rgt" exit

25
result Ora_Exec
(cursor) if (result
false) echo
Ora_ErrorCode(cursor)." ".Ora_Error(cursor)."ltB
Rgt" exit
echo "lttable
border1gt" echo "lttrgtlttdgtltbgtFull
Namelt/bgtlt/tdgtlttdgt ltbgtEmail Addresslt/bgtlt/tdgtlt/trgt"
while (Ora_Fetch_Into
(cursor, values)) name
values0 email
values1 echo
"lttrgtlttdgtnamelt/tdgtlttdgtemaillt/tdgtlt/trgt"
echo
"lt/tablegt" Ora_Close
(cursor) Ora_Logoff
(connection) ?gt
26
ODBC
DSN

• connect odbc_connect("firstdb", "", "") //
  no user, no password
• query "SELECT title, description FROM
  favorites"
• result odbc_exec(connect, query)
• print ("lttablegt\n")
• while(odbc_fetch_row(result))
• print ("lttrgtlttdgt")
• print(odbc_result(result, 1)."lt/tdgtlttdgt")
• print (odbc_result(result, 2)."lt/tdgtlt/trgt")
  
• print ("lt/tablegt")odbc_close(connect)

Index starts at 1
27
ADODB

• lt?
• include('adodb.inc.php')
• conn ADONewConnection('access')
• conn-gtPConnect('firstdb')
• query "Select title, description from
  favorites"
• recordSet conn-gtExecute(query)
• while (!recordSet-gtEOF)
• print recordSet-gtfields0.'
  '.recordSet-gtfields1.'ltBRgt'
• recordSet-gtMoveNext()
• recordSet-gtClose()
• conn-gtClose()
• ?gt

ADODB needs to be installed
Note -gt syntax
Note syntax
28
ADODB functions

• Metatypes for handling different names for types
  (char versus string, others)
• functions for handling dates
• debugging help
• Source
• http//php.weblogs.com/ADODB_manualinstall

29
Homework

• Post constructive comments on other projects (as
  a reply to posting announcing project).
• Post comments on php versus asp/JavaScript, MySql
  versus Access, Open Source versus
  proprietary/Microsoft.
• Finish projects.