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_name VALUES (value1,
  value2,....)
• INSERT INTO table_name (column1, column2,...)
  VALUES (value1, value2,....)

4
SQL Update

• Syntax
• UPDATE table_name SET column_name new_value
  WHERE column_name some_value
• UPDATE table_name SET column1 value1, column2
  value2 WHERE 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.idRoles.Users_id
• AND RoleTypes.idRoles.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_
  IDOrders.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_keyfie
  ld

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.as
  p)

13
PHP Frameworks

• http//www.phpwact.org/php/mvc_frameworks
• http//www.phpit.net/article/ten-different-php-fra
  meworks
• 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