Class 4 PHP MySQL

1. Class 4PHP MySQL Robert Mudge Reference: http://www.w3schools.com/php/

2. MySQL Class 4 • Homework 3 • SQL Insert • SQL Join • dbDesigner 4 & MySQL WorkBench • PHP MySQL Functions • Application Design • Homework 4

3. SQL Insert • Syntax INSERT INTO table_nameVALUES (value1, value2,....) INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

4. SQL Update • Syntax UPDATE table_name SET column_name = new_valueWHERE column_name = some_value UPDATE table_name SET column1 = value1, column2 = value2WHERE column = some_value

5. SQL Delete • Syntax Row DELETE FROM table_name WHERE column_name = some_value All Rows DELETE FROM table_nameor DELETE * FROM table_name

6. SQL Join • We can select data from two tables by referring to two tables SELECT Users.name, RoleTypes.name FROM Users, RoleTypes, Roles WHERE Users.id=Roles.Users_id AND RoleTypes.id=Roles.RoleTypes_id;

7. SQL Inner Join • The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed. • Syntax SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

8. SQL Left Join • The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed. • SyntaxSELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

9. SQL Right Join • The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed. • Syntax SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

10. dbDesigner 4  MySQL Workbench • ERD Diagram, adding access table • Design view • SQL view • Database create • Database synchonization

11. Application Design • Application Tree • includes • languages • design • docs • install • Web References • http://www.phpguru.org/static/ApplicationStructure.html • http://devzone.zend.com/node/view/id/31 • Examine “Model” Projects • PHPBB, Mantis, PHPMyAdmin

12. PHP MySQL Functions • Web References • PHP Manual (http://www.php.net/manual/en) • W3Schools (http://www.w3schools.com/php/default.asp)

13. PHP Frameworks http://www.phpwact.org/php/mvc_frameworks http://www.phpit.net/article/ten-different-php-frameworks http://digg.com/programming/Top_10_PHP_Frameworks

14. Homework 4 • Using Login from homework 2, the code and database from homework 3, design and build a base application which performs a user login with database lookup of password by user name, set the roles in session. • Upon a failed login, popup an error dialog and return to the login screen. A user can only login one time. • Upon successful login, display an opening screen showing user name, roles and session id along with a menu of links to pages that execute the 4 queries implemented in homework 3 presenting the results of each query in table form. • Turn in – • Screen shot Login successful • Screen shot Login unsuccessful • Screen opening screen • Screen shot of each query result • Source code of the project