Functions And Stored Procedures & functions and Protecting a DB AND PHP

1. Functions AndStored Procedures & functions and Protecting a DBAND PHP (Chapters 9, 15, 18)

2. Canned String Functions • Concatenate • Length of a string • Removing leading or trailing spaces • Finding string substrings • Transform string to upper or lower case • Reversing a string • Insert a substring into a string • Right or left padding of a string with a character • Etc.

3. Canned Numerical Functions • Round • Truncate • Absolute value • Square root • Random value • Etc.

4. Dates and Times • Now – local date and time from clock • Current timestamp • Currant date • Current time • Extract second, minute, hour, etc.,, from a date • Formatting dates • Adding intervals to dates • Subtract intervals from dates • Returning elapsed period length • Etc.

5. Specialized Functions • IF (testing an expression for true) • IFNULL (test two expressions and return first or second based on which is non-null) • COALESCE (test a list of expressions and return first non-null)

6. Two ways to use SQL with an Application • Dynamic SQL • Embedded SQL • Making calls to an SQL based DB

7. Dynamic SQL • SQL code can be generated at runtime based on conditions found by the Host application • In particular, to create the appropriate WHERE clauses • The SQL code is put into a Host application string variable • You can use dynamic SQL within a stored procedure

8. EMBEDDED SQL • SQL is placed inside Host application code • The SQL is processed in a first compilation phase • SQL application uses cursors and INTO statements to pass values to Host application

9. Connecting to a DB • PHP: • mysqli (the i is for improved) • or PDO (PHP Data Objects) • Java: there are a set of drivers for the various relational databases, including MySQL • The JDBC drivers are very popular and a lot of GUIs use them • .NET driver

10. Protecting a MySQL DB • Create and delete databases • Grant specific access rights to groups of users • Insert, Delete, Select rights on databases • Administration and other users • You can limit a user to access a DB only from a certain IP address • Separate access rights for altering a schema • Create DBs • Add tables • Change tables • Create views • Create indices • Create triggers

11. PHP and MySQL

12. Continued