Skip to content

Lecture Notes - Week 3

Readings

  • Chapters:
    • 6 - Arrays
    • 9 - Looping
    • 10 - Functions
    • 13 - Returning Data from a MySQL Database

Screencast - Week 3

Outline of Topics

  • MySQL Data Types
  • Removing database tables
  • Deleting rows from a table
  • More specific selections
  • Looping
  • Arrays
  • Displaying multiple rows from a table using looping with mysqli_fetch_array()
  • Using and Creating Functions

Lecture

MySQL Data Types

  • Numeric Data Types
INT A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from –2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
FLOAT(M,D) A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.
DOUBLE(M,D) A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.
DECIMAL(M,D) An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

NOTE: Why you might prefer DECIMAL over FLOAT or Double
  • String Types
CHAR(M) A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.
VARCHAR(M) A variable-length string between 1 and 255 characters in length; for example VARCHAR(25). You must define a length when creating a VARCHAR field.
  • Date and Time Types
DATE A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30.
DATETIME A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00.
TIMESTAMP A timestamp between midnight, January 1, 1970 and 03:14:07 UTC on January 19, 2038. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).
TIME Stores the time in HH:MM:SS format.
YEAR(M) Stores a year in 2-digit or 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be 1970 to 2069 (70 to 69). If the length is specified as 4, YEAR can be 1901 to 2155. The default length is 4.
  • Viewing the data types of a table
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
kmarks@php-mysql-course-test-1:~/workspace $ mysql -u student -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.5.43-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use northwind;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DESCRIBE Products;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| ProductID       | int(11)       | NO   | PRI | NULL    | auto_increment |
| ProductName     | varchar(40)   | NO   | MUL | NULL    |                |
| SupplierID      | int(11)       | YES  | MUL | NULL    |                |
| CategoryID      | int(11)       | YES  | MUL | NULL    |                |
| QuantityPerUnit | varchar(20)   | YES  |     | NULL    |                |
| UnitPrice       | decimal(10,4) | YES  |     | 0.0000  |                |
| UnitsInStock    | smallint(2)   | YES  |     | 0       |                |
| UnitsOnOrder    | smallint(2)   | YES  |     | 0       |                |
| ReorderLevel    | smallint(2)   | YES  |     | 0       |                |
| Discontinued    | bit(1)        | NO   |     | b'0'    |                |
+-----------------+---------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql>

Removing database tables

Once a table is created it cannot be overwritten by a new CREATE TABLE query. Here are a couple of ways you can remove tables:

  • DROP TABLE
1
2
mysql> use kemarks1;  
mysql> DROP TABLE fullname;
  • DELETE FROM table_name. Without using a qualifier for FROM, this will delete all the rows in the table:
1
mysql> DELETE FROM fullname;

Deleting rows from a table

  • DELETE FROM table_name WHERE column_name = ''. To delete specific rows from a table, you need to specify which row(s) using the WHERE qualifier:
1
mysql> DELETE FROM fullname WHERE first_name = 'Ken';

More specific selections

  • SELECT column_name[,…] FROM table_name WHERE column_name = ''. Query for just the rows you want:
1
mysql> SELECT first_name, last_name FROM fullname WHERE last_name = 'Marks';
  • SELECT * FROM table_name WHERE column_name = '':
1
mysql> SELECT * FROM fullname WHERE last_name = 'Marks';

1
mysql> SELECT * FROM fullname WHERE first_name = 'Ken' AND last_name = 'Marks';

Looping

  • while
  • do/while
  • for
  • foreach

while

  • Count up
1
2
3
4
5
6
$counter = 0;
while ($counter < 10)
{
    echo $counter;
    $counter++;
}
  • Count down
1
2
3
4
5
6
$counter = 10;
while ($counter > 0)
{
    echo $counter;
    $counter--;
}
  • do/while
1
2
3
4
5
6
7
8
$die_1 = 1;
$die_2 = 2;
do
{
    $die_1 = rand(1,6);
    $die_2 = rand(1,6);
    echo "You rolled a $die_1 and a $die_2 <br/>";
}while( $die_1 != $die_2 );
  • for
1
2
3
4
for( $counter = 0; $counter < 10; $counter++)
{
    echo $counter . ',';
}
  • foreach
1
2
3
4
5
6
7
8
<?php
    $error_messages = array("Something's wrong!", "What happened?", "Oops.");

    foreach($error_messages as $error)
    {
        echo "$error <br/>";
    }
?>

Arrays

What is an array?

  • a variable that can store a collection of scalars (or other composite types).
  • an ordered map, simply a collection of key-value pairs (the key is associated with the value).

Two types of arrays

  • Numeric
1
2
3
$products = array('bacon','hamburgers','tomatoes','buns');
echo $products[0]; //bacon
echo $products[3]; //buns
  • Associative
1
2
3
4
$ages = array('bob' => 34, 'kim' => 31, 'todd' => 62);
echo $ages['bob']; //34
echo $ages['kim']; //31
echo $ages['todd'];//62

Interesting functions to create an array

array_combine()

  • Turn 2 different numeric arrays into one associative array
1
2
3
4
$colors = array('green', 'red', 'yellow');
$fruit = array('avocado', 'apple', 'banana');
$fruit_by_color = array_combine($colors, $fruit);
print_r($fruit_by_color); //Array ( [green] => avocado [red] => apple [yellow] => banana )
  • Must be equal sizes

compact()

Takes a variable number of parameters

  • Each parameter can be either a string containing the name of the variable, or an array of variable names
  • The array can contain other arrays of variable names inside it
1
2
3
4
5
6
7
8
$city  = "San Francisco";
$state = "CA";
$event = "SIGGRAPH";

$location_vars = array("city", "state");

$result = compact("event", "nothing_here", $location_vars);
print_r($result); //Array ( [event] => SIGGRAPH [city] => San Francisco [state] => CA )

Adding items to an array

array_push()

1
2
3
4
5
6
7
$cars = array();
$cars[] = 'windstar';
array_push($cars, "cavalier");
$cars[] = "sorento";
array_push($cars, "cabrio");

print_r($cars);

Accessing items from an array

1
2
3
4
$ages = array('bob' => 34, 'kim' => 31, 'todd' => 62);
echo $ages['bob']; //34
echo $ages['kim']; //31
echo $ages['todd'];//62
  • $variable['key/index']

Looping with arrays

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$cars = array("windstar","cavalier","sorento","cabrio");

for($i = 0; $i < count($cars); $i++)
{
  echo $cars[$i];
}

foreach($cars as $car)
{
  echo $car;
}

while($i < count($cars))
{
  echo $cars[$i];
  $i++;
}

Displaying mulitple rows from a table using looping with mysqli_fetch_array()

  • mysqli_fetch_array() allows you to process each row from a query from beginning to end. If there is a next row, the row is returned, otherwise NULL is returned.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<html>
    <head>
        <title>Fetching Multiple Rows</title>
    </head>
    <body>
        <?php
            // Get all the products from the Product database
            // (just the name, price, and number in stock)
            $dbc = mysqli_connect('localhost', 'student', 'student', 'northwind')
                    or trigger_error("Error connecting to MySQL server: "
                    . mysqli_error($dbc), E_USER_ERROR);

            $query = "SELECT ProductName, UnitPrice, UnitsInStock FROM Products";

            $result = mysqli_query($dbc, $query)
                    or trigger_error("Query Error description: "
                    . mysqli_error($dbc), E_USER_WARNING);

            mysqli_close($dbc);
        ?> 
        <table border='1px solid;'>
            <tr><th>Product Name
            </th><th>Unit Price
            </th><th>Units in Stock</th></tr>
        <?php
            // Display every product row in a formatted table
            while($row = mysqli_fetch_array($result))
            {
                echo '<tr><td>' . $row['ProductName'] . '</td><td>'
                        . $row['UnitPrice'] . '</td><td>'
                        . $row['UnitsInStock'] . '</td></tr>';
            }            
        ?>     
        </table>
    </body>
</html>

Using and Creating Functions

  • Functions allow you to organize code into modules that can be easily reused
  • Functions can have zero or more parameters, and may return a value
  • Creating a simple function
1
2
3
4
5
6
function fullName($first_name, $last_name)
{
    $full_name = $first_name . ' ' . $last_name;

    return $full_name;
}
  • Here is a function that takes two parameters and returns a value:
1
2
3
4
5
function sum($num1, $num2)
{
    $sum = $num1 + $num2;
    return $sum;
}
  • And here is how we can use it:
1
2
3
4
5
6
$first_num = 5;
$second_num = 7;

$sum_of_two_numbers = sum($first_num, $second_num);

echo "The result of adding $first_num and $second_num is $sum_of_two_numbers<br/>";

which results in:

1
The result of adding 5 and 7 is 12

Week 3 Lab