• Getting Started
• Basics
• Arrays
• Loop/Branch
• Functions
• OOP
• Libraries
• Files
• MySQL
• SQL
• PHP/MySQL
• Regex
• Email
• Date/Time
• Web
• Forms
•  
•  
•  

Using PHP to Interact with a
MySQL Database

• Understanding the Different MySQL Database Extensions

• Reading Data from a Table

• Writing Data to a Table

• Updating Data in a Table

• Deleting Data from a Table

• Reading Data from Multiple Tables

• LAB: Displaying Products and Members Using PHP and MySQL

Understanding the Different MySQL Database Extensions

• What is a PHP database extension?

  
  

  A PHP database extension is a standard PHP extension which specifically manages database connectivity

• What are the MySQL database extensions?

  
  

  mysql, mysqli, and PDO MySQL

• What is the "mysqlnd" MySQL Native Driver?

  
  

  "mysqlnd" is not used directly when developing your website:
  it is a library which is used by the other PHP MySQL database extensions

• Why is the "mysql" extension still included with PHP?

  
  

  Although there is no longer support for the "mysql" extension,
  it is included for backwards compatibility.
  Eventually this extension will no longer be available.

• What is the difference between pdo_mysql and mysqli?

  
  

  Both extensions are highly rated, and each extension has its ardent supporters.

• Why was PDO chosen for the examples used in this course?

  
  

  PDO offers a more streamlined set of commands which can also be used with other database technologies such as Oracle, Microsoft SQL Server and postgres

• What is the difference between the PDO and the PDOStatement classes?

  
  

  The PDO class represents the actual database connection.
  The PDOStatement class pertains to fetch results

• What are examples of reading a table using mysqli and PDO?

  
  

  $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
  foreach($dbh->query('SELECT * FROM users') as $row) { echo implode(':',$row); }
  $dbh =new mysqli('localhost', $user, $pass, 'test');
  foreach($dbh->query('SELECT * FROM users') as $row) { echo implode(':',$row); }

Reading Data from a Table

• How can you read data from a table using PHP?

  
  

  $dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass);
  foreach ($dbh->query($sql) as $row) {}

• What is a "prepared statement"?

  
  

  A prepared statement is an SQL statement with placeholders in place of actual data values.
  It is sent to the database server in advance to be pre-compiled.
  The actual values are supplied when the statement is executed.

• Why would you want to use a prepared statement?

  
  

  Prepared statements give you a tremendous performance improvement when executed inside a loop.
  Prepared statements help protect against SQL injection attacks.

• What is an SQL injection attack?

  
  

  An SQL injection attack occurs when somebody attempts to add
  malicious SQL statements after values typed into an input form on your website.

• What are the steps involved in using prepared statements?

  
  

  1. Create the PDO object $pdo
  2. Formulate the SQL statement $sql using placeholders
  3. Send the prepared statement to the server: $stmt = $pdo->prepare($sql)
  4. Execute the statement supplying values: $stmt->execute(array(val1, val2, etc))
  5. Fetch the results: foreach($stmt->fetch(PDO::FETCH_ASSOC) as $row) {}

Writing Data to a Table

• How can you write data into a table using PHP?

  
  

  1. Create the PDO object $pdo
  2. Formulate the SQL statement $sql using placeholders
  3. Send the prepared statement to the server: $stmt = $pdo->prepare($sql)
  4. Execute the statement supplying values: $stmt->execute(array(val1, val2, etc))

• How would you insert one row of data into a table using PHP?

  
  

  1. Create the PDO object $pdo
  2. Formulate the SQL statement $sql using placeholders
  3. Send the prepared statement to the server: $stmt = $pdo->prepare($sql)
  4. Execute the statement supplying values: $stmt->execute(array(val1, val2, etc))

• How about many rows?

  
  

  1. Create the PDO object $pdo
  2. Formulate the SQL statement $sql using placeholders
  3. Send the prepared statement to the server: $stmt = $pdo->prepare($sql)
  4. Execute the statement supplying values: $stmt->execute(array(val1, val2, etc))

• How would you insert data from a CSV file into a table?

  
  

  Use fopen() and fgetcsv() to pull the each row from the file into an array, and then use prepared statements

• How would you insert data into multiple tables where there is a constraint?

  
  

  Insert data into the parent table first, and then into the child tables.
  Be sure to turn on error reporting when first developing your application!

Updating Data in a Table

• How do you update data in a table using PHP?

  
  

  1. Create the PDO object $pdo
  2. Formulate the SQL statement $sql using placeholders
  3. Send the prepared statement to the server: $stmt = $pdo->prepare($sql)
  4. Execute the statement supplying values: $stmt->execute(array(val1, val2, etc))

• What happens when you try to update a row where there is a constraint?

  
  

  If you update the parent row, and you have ON UPDATE CASCADE, changes will be reflected in the child table

• How do you update purchases for a user when you do not know the user ID?

  
  

  Use SELECT to find the ID number from the users table, then use UPDATE on the purchases table

Deleting Data from a Table

• How do you delete one row from a table using PHP?

  
  

  1. Create the PDO object $pdo
  2. Formulate the SQL statement $sql using placeholders
  3. Send the prepared statement to the server: $stmt = $pdo->prepare($sql)
  4. Execute the statement supplying values: $stmt->execute(array(val1, val2, etc))

• How do you delete child rows?

  
  

  Use SELECT to find the ID number from the parent, then use DELETE on the child table

• How do you delete a parent row where there is an ON DELETE constraint?

  
  

  For ON DELETE RESTRICT: follow the standard procedure to delete (as above) if there are no child rows.
  If there are child rows, use SELECT to find the ID of the parent, then use DELETE on the child table
  For ON DELETE CASCADE: Delete the row from the parent,
  and the child rows will be automatically deleted as well

Reading Data from Multiple Tables

• How do you read data from two tables in PHP?

  
  

  1. Create the PDO object $pdo
  2. Formulate the SQL statement $sql using placeholders
  3. Send the prepared statement to the server: $stmt = $pdo->prepare($sql)
  4. Execute the statement supplying values: $stmt->execute(array(val1, val2, etc))
  5. Fetch the results: foreach($stmt->fetch(PDO::FETCH_ASSOC) as $row) {}

• What kind of data comes back from SELECT ... JOIN?

  
  

  You will see exactly the same results as when perform SELECT without the join

• How do you read data from more than two tables?

  
  

  Use SELECT ... FROM table1
  JOIN table2 ON condition1
  JOIN tableX ON conditionX etc.

LAB: Displaying Products and Members Using PHP and MySQL

• What is the purpose of this lab?

  
  

  In this lab you will integrate database queries to display products and members

• Modify Model/Products.php ...

  
  

  • In your editor open the file sweetscomplete/Model/Products.php
  • Modify the __construct() function
  • Instead of reading products from a CSV file, have it read from the "products" table
  • Test and make sure everything works OK

• Create Model/Members.php ...

  
  

  • In your editor create a file sweetscomplete/Model/Members.php and a class "Members"
  • Retrieve the SQL statements defined in the SQL lab
  • Define a function getAllMembers() which retrieve an array of members from the database
  • The other SQL statements will be used in a later lab

• Modify View/View.php and members.php ...

  
  

  • In your editor open sweetscomplete/members.php
  • In your editor open sweetscomplete/View/View.php
  • In View.php create a function "displayMembers()"
  • Have the new function define a variable $members which represents Members()
  • Have the new function pull its data from $members->getAllMembers()
  • Move the HTML to display one member from members.php into the new function
  • Remove the extraneous HTML code from members.php
  • Substitute a PHP echo $view->displayMembers()