Transcript select - Personal Home Pages (at UEL)

MS3304: Week 8
Creating a dynamic SQL query
from form input
Overview
• Building an SQL statement
dynamically from form input
• Writing/Testing dynamic code
Review of an SQL statement
SELECT which columns
FROM which table
WHERE conditions
ORDER BY fields to sort by
Our student database fields & search form
Field Name
firstName
surname
studentNumber
group
cw1
cw2
average
books
library
club
cinema
altMed
team
MM_Prog
IT_Prog
Data type
varchar
varchar
varchar
int
int
int
int
tinyint
tinyint
tinyint
tinyint
tinyint
varchar
tinyint
tinyint
Field, element and variable names
Form Element Name
Database Name
PHP Variable Name
firstName
firstName
$firstName
surname
surname
$surname
studentNumber
studentNumber
$studentNumber
group
group
$group
cw1
cw1
$cw1
cw1Op
cw2
$cw1Op
cw2
cw2Op
cwAverage
$cw2
$cw2Op
cwAverage
cwAverageOp
$cwAverage
$cwAverageOp
books, library, club, cinema,
altMed
project
$books, $library, $club,
$cinema, $altMed
team
team
$team
IT_prog
IT_prog
$IT_prog
MM_prog
MM_prog
$MM_prog
sortOrder
$sortOrder
Comparing string input
• Need to make sure to trim off any
extra spaces using trim() function
• Need to make sure to unescape any
special characters using the
stripslashes() function
• May need to use the wildcards for
string comparison
Creating a dynamic SQL string
Q: How do you go about creating a dynamic
SQL statement?
A: Break down the statement into parts!
–
–
–
–
SELECT
FROM
WHERE
ORDER BY
$SQLSelectFrom = " ";
$SQLWhere = " ";
$SQLSort = " ";
$SQLcmd = $SQLSelectFrom . $SQLWhere .
$SQLSort;
Using variables in the query string
Last week we learned that we could use
variable names in our SQL query
SELECT * FROM students WHERE group
= $group
SELECT * FROM students WHERE
surname = “$surname”
SELECT * FROM students WHERE
surname LIKE “$surname%”
The SELECT/FROM string
• For this exercise we assume that the
all fields will be returned
• The name of the table is students
What is the string we need to create?
$SQLSelectFrom =
Creating the ORDER BY string
• Use the value of the Sort by select
object to return pass in the field
name of the column you wish to
sort by
What is the code we need to write?
$SQLSort =
The WHERE string
• This statement can be made up of any
combination of the criteria listed
• Since we may have multiple statements all
statements need to be to be enclosed in
parentheses
• If we have multiple statements they need to be
connected by an AND or an OR operator
• We need to know the name of the PHP variable
so we can get the value and the name of the
database field we will be searching
Comparing string input
• Need to make sure to trim off any
extra spaces using trim() function
• Need to make sure to unescape any
special characters using the
stripslashes() function
• May need to use the wildcards for
string comparison
Creating the WHERE statement:
testing the first criteria
Must have conditional statements to
check to see whether or not to add a
search criteria to the field
What conditional statement do we use to
check to see if we add firstName to the
criteria?
Creating the WHERE statement:
testing the second criteria
Must have conditional statements to
check to see whether or not to add a
search criteria to the field
What conditional statement do we use to
check to see if we add surname to the
criteria?
Adding operators for multiple criteria
Must use conditional statements to check
to see if the statement is the first criteria
listed to decide if an AND operator must
be added to the string to separate two
criteria
Where does this test go logically?
What is the conditional statement to test
for it?
Creating the WHERE statement:
your turn
Write the conditional statements dynamically build the
$SQLWhere statement for the following criteria:
–
–
–
–
–
–
–
–
–
–
–
–
–
studentNumber
group
cw1
cw2
average
books
library
club
cinema
altMed
team
MM_Prog
IT_Prog
Writing dynamic code
• Plan out the logic behind your code
first using comments
• Write your code in small chunks and
test each piece of code that you
write to make sure it works before
you start the next