CSCI 2910
Test 3 -- Study Guide

• Notes (PowerPoint)
• Exercise (HTML)

Details: From this material, you should have an understanding of the structure of databases, their relationship within the client/server model, and the benefits of the three-tier architecture. This material also provided an introduction to the format and use of SQL statements. After studying this material, you should be able to write general SQL statements to add, delete, update, and query records. You should also be able to identify the different data types, e.g., if I give you a data type, you should be able to describe what types of values can be stored in that data type.

Cheat sheet: I will provide the following syntax on a cheat sheet. (The select statement syntax is noted in the next row of this table.)

• INSERT INTO tablename (fieldname [, fieldnames]) VALUES (value [, values])
• DELETE FROM tablename WHERE fieldname=value
• UPDATE tablename SET fieldname=value WHERE fieldname=value
• USE database
• SHOW TABLES
• DROP TABLE tablename

I will also provide a list of the data types used on the test, but I will not identify what types of data they will contain.

• BIT[( M )]
• TINYINT[( M )] [UNSIGNED] [ZEROFILL]
• BOOL, BOOLEAN
• SMALLINT[( M )] [UNSIGNED] [ZEROFILL]
• MEDIUMINT[( M )] [UNSIGNED] [ZEROFILL]
• INTEGER[( M )], INT[( M )] [UNSIGNED] [ZEROFILL]
• BIGINT[( M )] [UNSIGNED] [ZEROFILL]
• FLOAT[( M , D )] [UNSIGNED] [ZEROFILL]
• DOUBLE[( M , D )], DOUBLE PRECISION[( M , D )], or REAL[( M , D )] [UNSIGNED] [ZEROFILL]
• FLOAT( p ) [UNSIGNED] [ZEROFILL]
• DEC[( M [, D ])], DECIMAL[( M [, D ])], NUMERIC[( M [, D ])], or FIXED[( M [, D ])] [UNSIGNED] [ZEROFILL ]
• DATE
• DATETIME
• TIMESTAMP[(M)]
• YEAR[(2|4)]
• CHAR( M ) [BINARY | ASCII | UNICODE]
• VARCHAR( M ) [BINARY]
• TEXT[( M )]
• MEDIUMTEXT
• LONGTEXT
• ENUM('value1','value2',...)
• SET('value1','value2',...)

• Notes (PowerPoint)
• Exercise (HTML)

Details: This lecture and set of exercises enhanced your understanding of the MySQL SELECT statement. You will need to know how each of the key words WHERE, BETWEEN, IN, LIKE, NOT, and LIMIT along with the arithmetic operators can be used to enhance the operation of the SELECT statement. You will also be asked to understand how multiple tables can be joined using a select statement, i.e., from table1, table2, etc. (Remember that joining tables without a WHERE condition creates the cartesian product, which is typically a useless result)

When it comes to using LIKE, you will also need to be able to use the wildcards '%' and '_' and also how to "escape" a character using the backslash, '\'..

Cheat sheet: The main thing you need to know from this section is the syntax of the select statement.

• SELECT [ ALL | DISTINCT] *| COLUMN1[, COLUMN2 ] FROM TABLE1 [ , TABLE2 ] WHERE [ CONDITION1 | EXPRESSION1 ][ AND|OR CONDITION2 | EXPRESSION2 ] ORDER BY fieldname [, fieldnames] [ASC|DESC]

• Notes (PowerPoint)
• Exercise (HTML)

Details: In this introduction to PHP we covered the differences between server-side and client-side operation, the format of a PHP file, the syntax of PHP code and similarities between PHP code and JavaScript code, data types, and data type conversion, i.e., what happens when a value of one type is converted to another. We also compared and contrasted echo and print, discussed character escaping for specific characters using a backslash, '\', and printing characters not available on the keyboard using "\x". Be sure to study the material in the lab on creating functions and including files in PHP.

Cheat sheet: There really isn't much to offer in the way of syntax here. If I am going to ask you to use a predefined function such as pow(), I will give you the syntax with the problem. You should probably already be familiar with the syntax of the program constructs such as if-statements, while-loops, switch-case-blocks, etc.

• Notes (PowerPoint)

Details: Material you will be responsible for in this section includes variable scope, global variables, static variables, arrays, and strings. When it comes to arrays, be sure you know the special indexing needs and the use of foreach. Be sure to look over the array and string functions, but if I ask a question regarding one of the functions, I will give you the syntax with the problem.

Cheat sheet: The following syntax will be included on your cheat sheet. For the functions included in this list, be sure you know how the function operates.

• foreach( arrayname as [ indexname => ] varname )
• array_keys($array_name)
• count($array_name)
• array_name = array_fill(integer start , integer count , mixed fill_value )
• bool sort($array_name)
• bool asort($array_name)
• string join(string delimiter, arrayname)
• array explode(string separator, string string [, int limit])
• integer strlen( string )
• string substr( source , start [, length ])
• integer strpos( source , substring [, offset ])

• Exercise (HTML)

Details: Understand the difference between post and get and how to use $_GET and $_POST arrays to retrieve form data.

Cheat Sheet: There really isn't anything to add to the cheat sheet from this section.

• Notes (PowerPoint)
• Exercise (HTML)

Details: Understand the process of accessing a MySQL database using PHP. This includes the PHP functions that allow us to:

• Log onto MySQL
• Select a database to work with
• Send a query to one or more tables
• Access the results of that query
• Exit/close MySQL

Cheat sheet: The following syntax will be available on your test. Be sure you understand how to use them.

• $connection = mysql_connect ("localhost", "zabc123", "password")
• mysql_select_db("dbname", $connection)
• $result = mysql_query(MySQL_statement_string, $connection)
• $record = mysql_fetch_array($result [, MYSQL_NUM |MYSQL_ASSOC | MYSQL_BOTH])
• mysql_close ($connection)
• int mysql_errno($connection)
• string mysql_error($connection)
• void exit([string or int status])

• Notes (PowerPoint)
• Exercise (HTML)

• Notes (PowerPoint)
• Exercise (HTML)

Details: Be able to discuss ways to prevent basic security problems including escaping characters, form validation, validation of referring page, cross-site scripting, SQL injection, and one-way encryption. Also be able to identify the four types of threats to server-side applications and have some examples for each. We also discussed sticky forms, so be able to create elements that can reflect back to the user previous inputs. Also be able to compare and contrast crypt() and md5().

Cheat sheet: The following syntax will be included on your cheat sheet. For the functions included in this list, be sure you know how the function operates.

• string stripslashes(string)
• string addslashes(string)
• bool isset($var)
• integer strlen(string)
• $_SERVER['HTTP_REFERER']
• $_SERVER['REQUEST_METHOD']
• string escapeshellcmd(string)
• string htmlspecialchars(string)
• int mysql_num_rows($result)
• string crypt (string str [, string salt])
• string md5 (string str [, bool raw_output])