Intro to PHP and MySQL

Class 4

Welcome!

Remember, we want d.r.y. code

Photo credit: Phil Synder cc

Application structure

Some parts of your code are repeated over and over. We can move these into their own files and use include to call them.

• header.php
• sidebar.php
• footer.php

Styling

Let's improve our coffeeshop page with some styles. Download the starter files as a .zip.

Thanks to Amy Hendrix for the code.

Let's Develop It

Play with the site code.

• Change the page title
• Add a link to the sidebar
• Add your name to the footer

Database relationships

Photo credit: Ben124 cc

Each table row has a unique primary key

Photo credit: Brenda-Starr cc

Tables are linked together through their keys

Photo credit: gnuru cc

The link is called a foreign key

Photo credit: Sylvia cc

MySQL can automatically clean up related data

Photo credit: silkegb cc

Let's Develop It

Let's edit first database. Start up WAMP/MAMP and go to http://localhost/phpmyadmin/ (Windows) or http://localhost:8888/phpmyadmin/ (Mac)

The default username is "root". The default password is either "" (i.e., leave it blank) or "root".

Select the coffee database

USE coffee;

Delete the product table

DROP TABLE product;

Use SQL to recreate the product table

CREATE TABLE product (
  productID int NOT NULL AUTO_INCREMENT,
  companyID_fk int,
  type varchar(255),
  roast varchar(255),
  description text,
  PRIMARY KEY (productID)
);

Use SQL to create a company table

CREATE TABLE company (
  companyID int NOT NULL AUTO_INCREMENT,
  name varchar(255),
  phoneNumber varchar(255),
  PRIMARY KEY (companyID)
);

Create an index on companyID_fk

ALTER TABLE product ADD INDEX (companyID_fk);

Open the product table and select relation view

Add a relationship

ALTER TABLE product
ADD CONSTRAINT FK_company FOREIGN KEY (companyID_fk)
REFERENCES company (companyID)
ON DELETE CASCADE ON UPDATE CASCADE;

Add some sample data

INSERT INTO company SET  name='Starbucks', phoneNumber='(800)555-7282';
INSERT INTO company SET  name='Bean Traders', phoneNumber='(919)555-5895';
INSERT INTO company SET  name='Mean Beans', phoneNumber='(303)555-8475';
INSERT INTO product SET  companyID_fk=1, type='French Vanilla', roast='dark', description='Strong coffee flavor with a hint of vanilla';
INSERT INTO product SET  companyID_fk=1, type='Pumpkin spice', roast='medium', description='Seasonal';
INSERT INTO product SET  companyID_fk=2, type='Dip Into Decaf', roast='light', description='You will barely notice it\'s coffee!';

Updating our submission forms

Right now, our product entry form is a free text entry. It looks like this:

Company: <input type="text" name="company"/><br/>

Restricting choices

We want it to only show approved companies from our DB, so looks like this:

Company: <select name="company">
  <option value="2">Bean Traders</option>
  <option value="3">Mean Beans</option>
  <option value="1">Starbucks</option>
</select>

Using DB information in forms

How can we do that? We can use SQL to ask the database for information:

SELECT companyID, name FROM company ORDER BY name;

Using DB information in forms

Then use a while loop to add this to our page

$sql='SELECT companyID, name FROM company ORDER BY name';
$result = mysqli_query($link, $sql);
if (!$result) {
  $error = 'Error fetching data: ' . mysqli_error($link);
  echo $error;
  exit();
}
while($recording=mysqli_fetch_array($result)){
  //Some awesome PHP code here
}

Let's Develop It

Your sample code has a file called add-products.php. It is almost complete. Add code to the commented sections to get it to work.

Get stuck? Try looking at view-products.php to see how the while loop works there.

Passing information

Photo credit: Mykl Roventine cc

URL parameters

Up until now, we have only passed data via forms. We can also pass data in the URL itself. This is called a parameter.

http://www.website.com/file.php?parameter=value

Using parameters

You can retrieve a parameter's value and store it as a variable

If you use a URL like this:

http://www.website.com/file.php?parameter=value

You can retrieve the values like this:

$variableName = htmlspecialchars($_GET["parameter"]);

Using parameters on our site

How can we use this? On the view products page, I have set up a delete link for each item.

This link uses a parameter to send the product ID to the delete page.

Let's Develop It

Your sample code has a file called delete-products.php. It is almost complete. Add code to the commented sections to get it to work.

Putting it all together

Photo credit: echerries cc

Editing data

Let's combine queries, forms, parameters, and logic all into one. We will

1. Pass a product ID to a page using parameters
2. Save that parameter as a variable
3. Use a query to pull the associated data
4. Pre-load that data into a form
5. Send the form to processing script
6. Update the database.

Let's build it - Step 1

On the view products page, I have set up an edit link for each item.

This link uses a parameter to send the product ID to the edit page.

Let's build it - Step 2

In the file edit-products.php, we will:

1. Retrieve the product ID and save it as a variable
2. Use the product ID in an SQL query:

   SELECT fields FROM tablename WHERE field='value'
   

3. Pre-load that data into a form that the user can edit
4. Send the form data to processing script

Let's build it - Step 3

In the file product_edit_result.php, we will:

1. Take in data from the form
2. Update the database
3. Send the user back to the table of products

Let's Develop It

Your sample code has a file called edit-products.php and one called product_edit_result.php. They are almost complete. Add code to the commented sections to get it to work.

You did it!

Photo credit: sunface13 cc

Resources

• PHP Manual, the official PHP documentation. Check the comments; they are useful.
• Code Academy, with interactive PHP lessons to help you review.
• W3Schools SQL Tutorial, a great guide to basic SQL.
• OverAPI, a collection of quick-reference guides.
• The completed site code for today's exercises.