Class 2 Notes

solution file from Dubi:
NOTE: We skipped around in the slides for the first two classes in the zip file above to copy out example code and create files that would become our class exercises. We did NOT use any of the example code in the zip files, even if they had the same names.  

Some people had issues opening the ppt files and/or copying from the pdfs and retaining the formatting, so I'm including the code with the exercises below this time. 

I might not be remembering these in the exact right order, I believe these are the exercises we did in class:

php Exercises

This week we started by creating a couple functions, then jumped to using premade functions to make calls to a MySQL database in the second part. A LOT of php will involve using other people's functions, so it's a good idea to learn how to read them. We didn't go into syntax for this yet.

Exercise 1

What we learn to do: create a function that does math with hard coded values or variables
Source: This code is found on slide #17 of PHP_Slide1 of the Girls Develop IT materials linked above. 

How to get started:
  1. Copy the code below into your text or code editor (I like Komodo Edit)
  2. Save the file into the root directory for your local server 
    1. This location is defined by the user for MAMP, named htdocs for XAMPP
    2. You can also create subfolders in that location and use that to organize your class files, you will just need to remember to add the folder after the url, like this: http://localhost/week2/filename.php
  3. Name the file something that ends in .php
  4. Open the file from a browser
function multiply($a, $b){
$c=$a*$b;
echo $c;
}

Why isn't it working? 
  • You need to add <?php and ?> around the code or it won't execute as php, especially in XAMPP.
  • If you don't have any values for $a and $b, it won't have anything to output to the screen with the echo function.

Solution

Go to the next slide (#18) and you'll see they add several variables underneath the function, assign them values, then use the function to keep doing math three different times:

function multiply ($a, $b)
{
$c=$a*$b;
echo $c;
}

$number1=6; //creating a variable and giving it the value 6
$number2=17; //creating a variable and giving it the value 17
multiply (3, 5); //using our function with hard coded values
multiply (4, 2); //using our function with different hard coded values
multiply($number1, $number2); //using our function with variable values

You can find my file for this exercise (it has even more explanatory comments) attached below, titled "week2-functions-multiply.php"

Exercise 2

What we learn to do: create a function that converts temperatures
Source: I've put this together from the different files I created trying to keep up in class. I didn't see it in this form in any of the slides, so I'm just putting up my solution here instead of an initial file & fixed version. Credit to Debbie & Mirsa for helping me keep up with the screen & figure out what wasn't working!

How to get started:
  1. Copy the code below into your text or code editor 
  2. Save the file into the root directory (or a folder you've created in there) for your local server
  3. Name the file something that ends in .php
  4. Open the file from a browser
<?php

function convertHeat($celcius)
{
$f=32+($celcius*1.8);
return $f;
}
echo convertHeat(100);
?>

You can find my file for this exercise (with explanatory comments) attached below, titled "week2-functions-multiply.php"

Exercise 3

What we learn to do: Pull in a value entered using a different file and apply functions to it
Source: This code is found on slide #20 of PHP_Slide1 of the Girls Develop IT materials linked above. 

How to get started:
  1. Copy the code below into your text or code editor
  2. Save the file into the root directory (or a folder you've created in there) for your local server
  3. Name the file: form.html 
  4. Open the file from a browser 
<html>
<head></head>
<body>
<form action="result.php" method="get"> //you must have this file for the form to work
<label>Enter Degrees in Fahrenheit</label>
<input name="degrees" type="text" /> //this passes the value of the form with the name degrees, which can be used as a variable in the php file
<input type="submit" value="Get Degrees in Celsius"/>
</form>
</body
</html>

Why isn't it working?
  • The form by itself won't work until you have a file in the same folder named result.php that's also working

Exercise 4

What we learn to do: Use an html form with a php file to process data. 
Source: This code is found on slide #23 of PHP_Slide1 of the Girls Develop IT materials linked above. 

How to get started:
  1. Copy the code below into your text or code editor
  2. Save the file into the root directory (or a folder you've created in there) for your local server
  3. Name the file: result.php 
  4. Open the form.html file from your browser and enter text into it.
<?php
$f = $_GET['degrees']; //this pulls in the value entered in the html form
$f = htmlspecialchars($f, ENT_QUOTES, 'UTF-8');

echo "You entered " . $f . " degrees Fahrenheit. ";

$c= ($f-32)*5/9;
$c = round( $c, 2);

echo "That is " . $c. " Celsius.";
?>

Why isn't it working?
  • If you enter a numerical value, it should work
  • If you enter a text value (for instance, "cold") it will not work. We aren't validating the data entered by the form yet, so it tries to do math on things that aren't valid numerical values. 
Thanks to Fran Bailey for the solution!
I'd posted a not-very-clear description of why I didn't have a working solution yet here, she sent me this:

<?php

$f = $_GET['degrees']; //this pulls in the value entered in the html form

    if (is_numeric($ f))
    {
        echo "You entered ".$f." degrees Fahrenheit.";
        $c = ($ f-32)*5/9;
        $c = round($ c,2);
        echo "<br />That is ". $ c . " degrees Celcius.";
    }

    else
    {
        echo "You entered a non-numeric value. Try again.";
       }

?>

Now, on to MySQL!

NOTE! Before you get started creating a database, review slides 3-8 of the PHP_Session2 slides to get a basic introduction to why you'd use a database and what the content types are that you'll use as you create your data structures.

Exercise 5

What we learn to do: create a MySQL database
source: the example is given on slides 9- PHP_Session2 of the Girls Develop It materials linked above.

How to get started:

1. Go to your local server through the browser and look for phpMyAdmin in the menu:

NOTE: phpMyAdmin is a free program that has different versions. Keep in mind the "Graphical User Interface" may vary and change what you see on each screen but the queries shouldn't change because of your version of phpMyAdmin - they are running using MySQL and phpMyAdmin just makes it easier to get started.

  • MAMP includes version 3.5.1 (newer is better! let's all use XAMPP! :-P)
  • XAMPP includes version 3.5.2.2

2. (Slide 9) Go to the databases tab and create a new database
  • The example name in the slides is "coffee" - you can make it anything you like, but will need to update the next exercise to match your choice
  • You can ignore the "Collation" drop-down - the default will work fine
3. (Slide 10) Verifying your Database
When you click on Create New Database you should see the following verifications:
  • At the top it should say say localhost >> coffee
  • a notification bar with a checkmark shoud appear saying "Database coffee has been created"
  • you should see line of code that says CREATE DATABASE 'coffee'
If you choose your own name for the database, it should appear in the same spots on the verification page. 

4. (no slide) Creating Tables
First, Make sure you are in your new database, not at the phpMyAdmin home page, and you're set to go
  • Click on the "Create table" button in your sidebar or enter the name in the main area of the screen. 
  • The example name in the slides is "product" - you can make it anything you like, but you will again need to update the next exercise to make it match your choice
  • Choose to create 5 fields
5. (Slide 11) Create Table Fields(defining the column structure for your table)
Enter the following names for each field (or your own, but you'll need to keep track of them)
  1. field name: product_ID 
    • type: INT
    • index: PRIMARY
    • check the A/I (Auto Increment) box
  2. field name: company
    • type: VARCHAR
    • length: 256
  3. field name:type
    • type: VARCHAR
    • length: 256
  4.  field name: roast
    • type: VARCHAR
    • length: 256
  5. field name: description
    • type: TEXT

Notes on content types: 

Why do you want to auto-increment product_ID? You'll be using it as the primary key for this table (so you could connect any line in the table by it's ID to a line in another table). If you have it auto-increment, you can be sure each entry gets a unique number and you'll have a way to check which order they were entered in, if that matters.

Why use VARCHAR and define the length? If you know the content of your columns will be basic text and numbers and possibly punctuation but never very long, using this type and length helps use less computer memory because it limits the size of the information that can be stored there.

Why would you use TEXT and not define the length? This can store a huge amount of text, but doing so unncessarily could slow down your programs, so it's a best practice to only use this type when you know you need it. 

6. (No slides) Verifying your table:
  • You should see all of your fields and the types from the Structure tab of your table now 
  • Check it against the slide info show on if want to be sure your remembered everything. 
  • At the top You'll see a message saying "MySQL returned an empty result set (i.e. zero rows)." because the table doesn't have any data in it yet.
7. (No slide) Entering Data 
Be sure you're in the correct table you've just created from the sidebar (you should see "breadcrumb" links that look like localhost>>coffee>>product, or whatever names you've used, at the top of the screen)
  • Go to the Insert tab
    • leave the product_ID (the primary key) field empty. It will fill in automatically if you've turned on auto-increment
    • enter any company name
    • enter a type
    • enter a roast
    • enter a description
  • Save your entries
  • Repeat until you have several entries in your table
8. (no slides) Verify Data
  • Now when you click on your table from the sidebar, it should take you to the first "Browse" tab (which was greyed out before) where you can view your entries in the table.

Exercise 6

What we learn to do: run a query on our MySQL database
source: I think he just told us what to do?

How to get started:

Go to the SQL Tab. Do stuff. 

Sorry I don't have more than that!  I was having trouble with my tables and didn't catch much except there are a lot of ways to do queries, lots of people get paid to do queries, etc. 

I assume there are sources out there explaining how to do this but I didn't see anything in the materials for the course so I'm leaving this section short for the time being until I have time to catch up.

Here's an example query Mirsa came up with next to me and I copied down & edited to match my data. 
It works, if the comments are removed and it's made to match your table data:
SELECT * 
FROM  `product` //your table name
WHERE product_id <5 //make this less than the total number of entries
AND  `brand` =  "Tazo" //make this match one of your entries

My suggestion? Watch for the messages you see when you use the tabs or edit buttons to search or edit or insert data in your tables. It will show you the queries it just ran for you, and you can learn what the keywords and syntax look like this way:

Example Search Query:
SELECT * 
FROM `product` 
WHERE `brand` LIKE 'Tazo'

Example Edit Query:
UPDATE `tea`.`product` SET `description` = 'This is standard American dust tea.' WHERE `product`.`product_id` =5;

Example Insert Query:
INSERT INTO `tea`.`product` (
`product_id` ,
`brand` ,
`name` ,
`type` ,
`description`
)
VALUES (
NULL , 'Twinnings', 'Earl Grey', 'loose leaf','A black tea with bergamot oil flavoring. '
);

Exercise 7

What we learn to do: connect to your db using php
Source: This code is found on slide #13 of PHP_Session2 of the Girls Develop IT materials linked above.

How to get started:
  1. Copy the code below into your text or code editor 
  2. Save the file into the root directory (or a folder you've created in there) for your local server
  3. Name the file db.inc.php (it's ok that there are 2 periods)
  4. Go on to the next step. This is a function you use to connect to your database, but there's nothing that will display to the browser without a value being put into the function, which the next exercise does. 
<?php
$link=mysqli_connect ('localhost', 'root', 'root');//XAMPP and WAMP will break with this code- take out the 2nd root (password) leave it blank
    if (!$link){
        $output='Unable to connect to the database';
        echo $output;
        exit();
    }

    if (!mysqli_set_charset($link,'utf8')) {
        $output = 'Unable to set database connection encoding.';
        echo $output;
        exit();
    }

    if (!mysqli_select_db($link, 'coffee')){
        $output = 'Unable to locate the database.';
        echo $output;
        exit();
    }
?>

Want an example?
You can find my file for this exercise (it is fixed for XAMPP and my database setup, which is shown above, and has even more explanatory comments) attached below, titled "XAMPP.db.inc.php"

What might you need to fix?
If you see an Access Denied error code, then you need to fix the login information in line 2 of db.inc.php:
Warning: mysqli_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: YES) in C:\xampp\htdocs\week2\db.inc.php on line 2

Not sure where to go to figure out what the login information should be?
  • accessing basic users:
    • go to phpmyadmin - do not select any tables - go to the user tab at the top. 
    • look under the list of users for username root, host localhost. Does it have a password?
      • If no, leave the field empty in your code: '' (single quotes with no space between)
      • if yes, type whatever is in the field into your code between the single quotes.
Note: leaving the default to root/root or root/blank would obviously be a terrible way to configure a live server, but no one can access this "server" but the people using your computer so it doesn't matter as much for practice

If you get the "Unable to locate the database." error, check if you customized your database name or have a typo in your code in the third if statement (the database name is hard coded into the if conditions)
  • I changed my database name to "tea," for instance, because I don't drink coffee and don't know enough to fill in a table about different types of coffee.
Debbie adds: I found that the database connection was difficult to establish unless I got the spacing in the code precisely correctly.  Part of the problem is that I couldn't always figure out where my code was failing, even with the helpful error messages.  I created a version of the db.inc.php file that returns error messages when there' s a problem, but also includes success messages when the connection works.  This version works with my MAMP installation.  

Exercise 8

What we learn to do: display the data from your database in your browser
Source: This code is found on slides #18-20 of PHP_Session2 of the Girls Develop IT materials linked above. 

How to get started:
  1. Copy the code below into your text or code editor 
  2. Save the file into the root directory (or a folder you've created in there) for your local server
  3. Name the file product_show.php
  4. Open the file in your browser.
<?php
    include 'db.inc.php';

    $sql='SELECT id, company, type, roast, description FROM product ORDER BY id DESC';
    $result = mysqli_query($link, $sql); 

    if (!$result) {
        $error = 'Error fetching data: ' . mysqli_error($link);
        echo $error;
        exit();
    

    while($recording=mysqli_fetch_array($result)){
        $id=htmlspecialchars($recording[id'], ENT_QUOTES, 'UTF-8'); 
        $company= htmlspecialchars($recording['company'], ENT_QUOTES, 'UTF-8');
        $type=htmlspecialchars($recording['type'], ENT_QUOTES, 'UTF-8');         
        $roast=htmlspecialchars($recording['roast'], ENT_QUOTES, 'UTF-8'); 
        $description=htmlspecialchars($recording['description'], ENT_QUOTES, 'UTF-8'); 

        echo $company; 
        echo $type; 
        echo $roast; 
        echo $description; 
    

?>  

Want an example?
You can find my file for this exercise (it is fixed for XAMPP and my database setup, which is shown above, has formatting mixed in and even more explanatory comments) attached below, titled "XAMPP-formatted-product_show.php"

Why isn't it working?
  • If you see errors, look at the possible fixes in the end of the last exercise first:
    • it could be a problem with the information about how to get into your database (line 2 of inc.db.php)
      • Fran Bailey reports that having a password breaks the code for WAMP users.
      • Laura Cathey reported the same problem for XAMPP users, and gave the tip on how to find where your user info is that I included above. 
    • it could be a problem with having the database name hard coded into inc.db.php (the final if statement in inc.db.php)
  • Next check if you see any hard coded data field names in the example that doesn't match your table fields
    • check through the first $sql line
    • check through the variables in the while loop
    • check through the echo statements

After the Exercises:

Want to format the output of your product-show.php file? 
Read through slides 21 and 22 or play with the example files I've included below!

Here's what my version outputs (obviously I need to learn something about how to include accent characters correctly):

Twinnings: Earl Grey
Type: loose leaf
Description:
A black tea with bergamot oil flavoring. 

Tazo: Passion
Type: bagged
Description:
This tea has hibiscus petals in it.

Tazo: Thé Verte
Type: bagged
Description:
This is green tea with a French name.

Mighty Leaf: Chocolate Mint
Type: bagged
Description:
This is a flavored herbal tea.

Lipton: Classic
Type: bagged
Description:
This is standard American dust tea.

Example Table & Relational DB

posted May 8, 2013, 5:25 PM by Emily Barney   [ updated May 8, 2013, 5:38 PM ]

Because I decided to play around, my example database is named "tea" instead of "coffee" and my "product" table has a bunch of different column names than the example (but all the same content types): 

product_idbrandnametypedescription
1TazoPassionbaggedThis tea has hibiscus petals in it.
2TazoThé VertebaggedThis is green tea with a French name.
4Mighty LeafChocolate MintbaggedThis is a flavored herbal tea.
5LiptonClassicbaggedThis is the standard American dust tea.

If I really wanted to make this example into a relational database, I could look at this and ask myself what I'd entered more times than I needed to and where I could create new tables to store those entries, then link those pieces of info into this product table using their "primary key" id numbers:

A relational database might look more like this:

Product table:
 product_IDbrand_id name type_id description
 1 1 Passion     1 This tea has hibiscus petals in it.
 2  1 Thé Verte 1 This is a green tea with a French name.
 4 2 Chocolate Mint 1 This is a flavored herbal tea.
 5 3 Classic 1 This is the standard American dust tea.

Brand table:
 brand_id brand_name
 1Tazo
 2Mighty Leaf 
 3Lipton

Type table:
 type_id    type_name
 1 bagged
 2 loose leaf

Explanation:

This is a lot of work to go to for 4 lines of data, but if I decided to go to the grocery store and list all of the teas they had available, or ask everyone I know what their favorite tea was, this would make it a lot faster to enter the data if I'm not typing in these same pieces of information over and over. 

This approach also saves on the size of my database - I could use more INT types for the IDs and store the VARCHAR types a single time instead of over and over. 

I could think about what I know about tea and prepare ahead for other types of entries - I don't have any loose leaf tea listed in my initial data, but I could quickly start to add it anytime!

For instance, I could make a website or application for people to manage the inventory of different stores by adding more tables to collect store information and quantities of each product. I could turn that into a comparison tea shopping tool for tea-lovers instead by grouping teas by store locations or sorting them by price, if I added tables to store that information.

Or I could make a survey website and store the information about each person who filled in their favorite tea into my database by adding a user table and connecting it to the product table. For the users, I could use a drop-down menu to pull all the brand and types out to my website, then just let them select entries. Then I wouldn't have to worry about typing errors. I could add another text field for reviews and pull up all the user reviews along with the product when people look at the survey results.

1-1 of 1

ċ
XAMPP-formatted-product_show.php
(2k)
Emily Barney,
May 8, 2013, 5:10 PM
ċ
XAMPP.db.inc.php
(1k)
Emily Barney,
May 8, 2013, 5:10 PM
ċ
db.inc.php
(0k)
Deborah Ginsberg,
May 9, 2013, 1:58 PM
ċ
week2-functions-multiply.php
(1k)
Emily Barney,
May 8, 2013, 3:31 AM
ċ
week2-functions-temperature.php
(1k)
Emily Barney,
May 8, 2013, 3:54 AM