<!--#include virtual="commontop.html" -->
<title>Web Programming Step by Step, Lecture 20: Databases and SQL; HTML Tables</title>
</head>
<body>
<div class="layout">
<div id="controls"><!-- DO NOT EDIT --></div>
<div id="currentSlide"><!-- DO NOT EDIT --></div>
<div id="header"></div>
<div id="footer">
<h1><em>Web Programming Step by Step</em>, Lecture 20</h1>
<h2>Databases and SQL; HTML Tables</h2>
</div>
</div>
<div class="presentation">
<div class="slide">
<h1><a href="http://www.webstepbook.com/">Web Programming Step by Step</a></h1>
<h3>Lecture 20 <br /> Databases and SQL; HTML Tables</h3>
<h4>Reading: 11.1 - 11.3; 2.2.2</h4>
<h5>
References:
<a href="http://www.w3schools.com/sql/sql_quickref.asp">SQL syntax reference</a>,
<a href="http://www.w3schools.com/sql/default.asp">w3schools tutorial</a>
</h5>
<p class="license">
Except where otherwise noted, the contents of this presentation are Copyright 2010 Marty Stepp and Jessica Miller.
</p>
<div class="w3c">
<a href="http://validator.w3.org/check/referer"><img src="images/w3c-xhtml11.png" alt="Valid XHTML 1.1" /></a>
<a href="http://jigsaw.w3.org/css-validator/check/referer"><img src="images/w3c-css.png" alt="Valid CSS!" /></a>
</div>
</div>
<div class="slide titleslide">
<h1>11.1: Database Basics</h1>
<ul>
<li>
<strong>11.1: Database Basics</strong>
</li>
<li>
11.2: SQL
</li>
<li>
11.3: Databases and PHP
</li>
<li>
2.2.2: HTML Tables
</li>
</ul>
</div>
<div class="slide">
<h1>Relational databases</h1>
<ul>
<li><a href="http://en.wikipedia.org/wiki/Relational_database"><span class="term">relational database</span></a>: A method of structuring data as tables associated to each other by shared attributes.</li>
<li>a table row corresponds to a unit of data called a <span class="term">record</span>; a column corresponds to an attribute of that record</li>
<li>relational databases typically use <span class="term">Structured Query Language</span> (SQL) to define, manage, and search data</li>
</ul>
</div>
<div class="slide">
<h1>
Why use a database?
<span class="readingsection">(11.1.1)</span>
</h1>
<ul>
<li><span class="term">powerful</span>: can search it, filter data, combine data from multiple sources</li>
<li><span class="term">fast</span>: can search/filter a database very quickly compared to a file</li>
<li><span class="term">big</span>: scale well up to very large data sizes</li>
<li><span class="term">safe</span>: built-in mechanisms for failure recovery (e.g. <span class="term">transactions</span>)</li>
<li><span class="term">multi-user</span>: concurrency features let many users view/edit data at same time</li>
<li><span class="term">abstract</span>: provides layer of abstraction between stored data and app(s)
<ul>
<li>many database programs understand the same SQL commands</li>
</ul>
</li>
</ul>
</div>
<!--
can ask lots of good high-level questions about the data ...
Who has the highest GPA?
Give me the last 10 names in ABC order.
Show me all students who are taking at least 14 credits and also have GPAs below 2.0.
...
-->
<div class="slide">
<h1>Database software</h1>
<ul>
<li><a href="http://en.wikipedia.org/wiki/Oracle_database">Oracle</a></li>
<li><a href="http://www.microsoft.com/sql/">Microsoft SQL Server</a> (powerful) and <a href="http://en.wikipedia.org/wiki/Microsoft_Access">Microsoft Access</a> (simple)</li>
<!--
<li>IBM <a href="http://en.wikipedia.org/wiki/IBM_DB2">DB2</a></li>
-->
<li><a href="http://www.postgresql.org/">PostgreSQL</a> (powerful/complex free open-source database system)</li>
<li><a href="http://www.sqlite.org/">SQLite</a> (transportable, lightweight free open-source database system)</li>
<li><img style="float: right; width: 20%" src="images/mysql.gif" alt="MySQL" />
<a href="http://www.mysql.com/">MySQL</a> (simple free open-source database system)
<ul>
<li>many servers run "<a href="http://en.wikipedia.org/wiki/LAMP_%28software_bundle%29">LAMP</a>" (Linux, Apache, MySQL, and PHP)</li>
<li>Wikipedia is run on PHP and MySQL</li>
<li>we will use MySQL in this course</li>
</ul>
</li>
</ul>
</div>
<div class="slide">
<h1>Example <code>simpsons</code> database</h1>
<table style="font-size: smaller">
<tr>
<td style="vertical-align: top">
<table class="sqltable">
<caption>students</caption>
<tr><th>id</th><th>name</th><th>email</th></tr>
<tr><td>123</td><td>Bart</td><td>bart@fox.com</td></tr>
<tr><td>456</td><td>Milhouse</td><td>milhouse@fox.com</td></tr>
<tr><td>888</td><td>Lisa</td><td>lisa@fox.com</td></tr>
<tr><td>404</td><td>Ralph</td><td>ralph@fox.com</td></tr>
</table>
</td>
<td style="vertical-align: top">
<table class="sqltable">
<caption>teachers</caption>
<tr><th>id</th><th>name</th></tr>
<tr><td>1234</td><td>Krabappel</td></tr>
<tr><td>5678</td><td>Hoover</td></tr>
<tr><td>9012</td><td>Stepp</td></tr>
</table>
</td>
<td style="vertical-align: top">
<table class="sqltable">
<caption>courses</caption>
<tr><th>id</th><th>name</th><th>teacher_id</th></tr>
<tr><td>10001</td><td>Computer Science 142</td><td>1234</td></tr>
<tr><td>10002</td><td>Computer Science 143</td><td>5678</td></tr>
<tr><td>10003</td><td>Computer Science 190M</td><td>9012</td></tr>
<tr><td>10004</td><td>Informatics 100</td><td>1234</td></tr>
</table>
</td>
<td style="vertical-align: top">
<table class="sqltable">
<caption>grades</caption>
<tr><th>student_id</th><th>course_id</th><th>grade</th></tr>
<tr><td>123</td><td>10001</td><td>B-</td></tr>
<tr><td>123</td><td>10002</td><td>C</td></tr>
<tr><td>456</td><td>10001</td><td>B+</td></tr>
<tr><td>888</td><td>10002</td><td>A+</td></tr>
<tr><td>888</td><td>10003</td><td>A+</td></tr>
<tr><td>404</td><td>10004</td><td>D+</td></tr>
</table>
</td>
</tr>
</table>
<ul style="clear: both">
<li>to test queries on this database, use username <code>homer</code>, password <code>d0ughnut</code></li>
</ul>
</div>
<div class="slide">
<h1>
Example <code>world</code> database
<span class="readingsection">(11.1.2)</span>
</h1>
<table>
<tr>
<td colspan="2" style="vertical-align: top">
<table class="sqltable">
<caption>countries</caption>
<caption style="font-size: smaller;">Other columns:
<strong>region</strong>,
<strong>surface_area</strong>,
<strong>life_expectancy</strong>,
<strong>gnp_old</strong>,
<strong>local_name</strong>,
<strong>government_form</strong>,
<strong>capital</strong>,
<strong>code2</strong>
</caption>
<tr>
<th>code</th>
<th>name</th>
<th>continent</th>
<th>independence_year</th>
<th>population</th>
<th>gnp</th>
<th>head_of_state</th>
<th>...</th>
</tr>
<tr>
<td>AFG</td>
<td>Afghanistan</td>
<td>Asia</td>
<td>1919</td>
<td>22720000</td>
<td>5976.0</td>
<td>Mohammad Omar</td>
<td>...</td>
</tr>
<tr>
<td>NLD</td>
<td>Netherlands</td>
<td>Europe</td>
<td>1581</td>
<td>15864000</td>
<td>371362.0</td>
<td>Beatrix</td>
<td>...</td>
</tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>
</table>
</td>
</tr>
<tr>
<td style="vertical-align: top; padding-top: 1em">
<table class="sqltable">
<caption>cities</caption>
<tr>
<th>id</th>
<th>name</th>
<th>country_code</th>
<th>district</th>
<th>population</th>
</tr>
<tr><td>3793</td><td>New York</td><td>USA</td><td>New York</td><td>8008278</td></tr>
<tr><td>1</td><td>Los Angeles</td><td>USA</td><td>California</td><td>3694820</td></tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>
</table>
</td>
<td style="vertical-align: top; padding-top: 1em">
<table class="sqltable">
<caption>languages</caption>
<tr><th>country_code</th><th>language</th><th>official</th><th>percentage</th></tr>
<tr>
<td>AFG</td><td>Pashto</td><td>T</td><td>52.4</td></tr>
<td>NLD</td><td>Dutch</td><td>T</td><td>95.6</td></tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td></tr>
</table>
</td>
</tr>
</table>
<ul style="clear: both">
<li>to test queries on this database, use username <code>traveler</code>, password <code>packmybags</code></li>
</ul>
</div>
<div class="slide">
<h1>
Example <code>imdb</code> database
<span class="readingsection">(11.1.2)</span>
</h1>
<div style="font-size: smaller; overflow: hidden;">
<table class="sqltable" style="float: left; margin-left: 10px">
<caption>actors</caption>
<tr><th>id</th><th>first_name</th><th>last_name</th><th>gender</th></tr>
<tr><td>433259</td><td>William</td><td>Shatner</td><td>M</td></tr>
<tr><td>797926</td><td>Britney</td><td>Spears</td><td>F</td></tr>
<tr><td>831289</td><td>Sigourney</td><td>Weaver</td><td>F</td></tr>
<tr><td colspan="4" style="text-align: center">...</td></tr>
</table>
<table class="sqltable" style="float: left; margin-left: 10px">
<caption>movies</caption>
<tr><th>id</th><th>name</th><th>year</th><th>rank</th></tr>
<tr><td>112290</td><td>Fight Club</td><td>1999</td><td>8.5</td></tr>
<tr><td>209658</td><td>Meet the Parents</td><td>2000</td><td>7</td></tr>
<tr><td>210511</td><td>Memento</td><td>2000</td><td>8.7</td></tr>
<tr><td colspan="4" style="text-align: center">...</td></tr>
</table>
<table class="sqltable" style="float: left; margin-left: 10px">
<caption>roles</caption>
<tr><th>actor_id</th><th>movie_id</th><th>role</th></tr>
<tr><td>433259</td><td>313398</td><td>Capt. James T. Kirk</td></tr>
<tr><td>433259</td><td>407323</td><td>Sgt. T.J. Hooker</td></tr>
<tr><td>797926</td><td>342189</td><td>Herself</td></tr>
<tr><td colspan="3" style="text-align: center">...</td></tr>
</table>
<table class="sqltable" style="float: left; margin-left: 10px">
<caption>movies_genres</caption>
<tr><th>movie_id</th><th>genre</th></tr>
<tr><td>209658</td><td>Comedy</td></tr>
<tr><td>313398</td><td>Action</td></tr>
<tr><td>313398</td><td>Sci-Fi</td></tr>
<tr><td colspan="2" style="text-align: center">...</td></tr>
</table>
<table class="sqltable" style="float: left; margin-left: 10px">
<caption>directors</caption>
<tr><th>id</th><th>first_name</th><th>last_name</th></tr>
<tr><td>24758</td><td>David</td><td>Fincher</td></tr>
<tr><td>66965</td><td>Jay</td><td>Roach</td></tr>
<tr><td>72723</td><td>William</td><td>Shatner</td></tr>
<tr><td colspan="3" style="text-align: center">...</td></tr>
</table>
<table class="sqltable" style="float: left; margin-left: 10px">
<caption>movies_directors</caption>
<tr><th>director_id</th><th>movie_id</th></tr>
<tr><td>24758</td><td>112290</td></tr>
<tr><td>66965</td><td>209658</td></tr>
<tr><td>72723</td><td>313398</td></tr>
<tr><td colspan="2" style="text-align: center">...</td></tr>
</table>
</div>
<ul style="clear: both; margin-top: 30px;">
<li>also available, <code>imdb_small</code> with fewer records (for testing queries)</li>
<li>to test queries on this database, use the username/password that we will email to you soon</li>
</ul>
</div>
<div class="slide titleslide">
<h1>11.2: SQL</h1>
<ul>
<li>
11.1: Database Basics
</li>
<li>
<strong>11.2: SQL</strong>
</li>
<li>
11.3: Databases and PHP
</li>
<li>
2.2.2: HTML Tables
</li>
</ul>
</div>
<div class="slide">
<h1><a href="http://en.wikipedia.org/wiki/Sql">SQL</a> basics</h1>
<pre class="examplecode sql">
SELECT name FROM cities WHERE id = 17;
</pre>
<pre class="examplecode sql">
INSERT INTO countries VALUES ('SLD', 'ENG', 'T', 100.0);
</pre>
<ul>
<li><span class="term">Structured Query Language (SQL)</span>: a language for searching and updating a database</li>
<li>a standard syntax that is used by all database software (with minor incompatiblities)
<ul>
<li>
generally case-insensitive
</li>
</ul>
</li>
<li>a <span class="term">declarative</span> language: describes what data you are seeking, not exactly how to find it</li>
</ul>
</div>
<div class="slide">
<h1>
Issuing SQL commands directly in MySQL
<span class="readingsection">(11.2.1 - 11.2.2)</span>
</h1>
<pre class="syntaxtemplate sql">
SHOW DATABASES;
USE <var>database</var>;
SHOW TABLES;
</pre>
<ul>
<li>SSH to Webster, then type:</li>
</ul>
<pre class="examplecode ssh" style="font-size: smaller">
$ <em>mysql -u <var>yourusername</var> -p</em>
Password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> <em>USE world;</em>
Database changed
mysql> <em>SHOW TABLES;</em>
+-----------+
| cities |
| countries |
| languages |
+-----------+
3 rows in set (0.00 sec)
</pre>
</div>
<div class="slide">
<h1>The SQL <a href="http://en.wikipedia.org/wiki/Select_%28SQL%29"><code>SELECT</code></a> statement</h1>
<pre class="syntaxtemplate sql">
SELECT <var>column(s)</var> FROM <var>table</var>;
</pre>
<pre class="examplecode sql">
SELECT name, code FROM countries;
</pre>
<table class="sqltable">
<tr><th>name</th><th>code</th></tr>
<tr><td>China</td><td>CHN</td></tr>
<tr><td>United States</td><td>IND</td></tr>
<tr><td>Indonesia</td><td>USA</td></tr>
<tr><td>Brazil</td><td>BRA</td></tr>
<tr><td>Pakistan</td><td>PAK</td></tr>
<tr><td>...</td><td>...</td></tr>
</table>
<ul>
<li>the <a href="http://dev.mysql.com/doc/refman/5.0/en/select.html"><code>SELECT</code></a> statement searches a database and returns a set of results
<ul>
<li>the column name(s) written after <code>SELECT</code> filter which parts of the rows are returned</li>
<li>table and column names are case-sensitive</li>
<li><code>SELECT * FROM <var>table</var>;</code> keeps all columns</li>
</ul>
</li>
</ul>
</div>
<div class="slide">
<h1>The <code>DISTINCT</code> modifier</h1>
<pre class="syntaxtemplate sql">
SELECT <em>DISTINCT</em> <var>column(s)</var> FROM <var>table</var>;
</pre>
<table>
<tr style="vertical-align: top">
<td style="width: 45%">
<pre class="examplecode sql">
SELECT language
FROM languages;
</pre>
<table class="sqltable">
<tr><th>language</th></tr>
<tr><td>Dutch</td></tr>
<tr><td>English</td></tr>
<tr><td>English</td></tr>
<tr><td>Papiamento</td></tr>
<tr><td>Spanish</td></tr>
<tr><td>Spanish</td></tr>
<tr><td>Spanish</td></tr>
<tr><td>...</td></tr>
</table>
</td>
<td style="width: 45%; padding-left: 5%">
<pre class="examplecode sql">
SELECT <em>DISTINCT</em> language
FROM languages;
</pre>
<table class="sqltable">
<tr><th>language</th></tr>
<tr><td>Dutch</td></tr>
<tr><td>English</td></tr>
<tr><td>Papiamento</td></tr>
<tr><td>Spanish</td></tr>
<tr><td>...</td></tr>
</table>
</td>
</tr>
</table>
<ul>
<li>eliminates duplicates from the result set</li>
</ul>
</div>
<div class="slide">
<h1>The <code>WHERE</code> clause</h1>
<pre class="syntaxtemplate sql">
SELECT <var>column(s)</var> FROM <var>table</var> <em>WHERE <var>condition(s)</var></em>;
</pre>
<pre class="examplecode sql">
SELECT name, population FROM cities <em>WHERE country_code = "FSM"</em>;
</pre>
<table class="sqltable">
<tr><th>name</th><th>population</th></tr>
<tr><td>Weno</td><td>22000</td></tr>
<tr><td>Palikir</td><td>8600</td></tr>
</table>
<ul>
<li><code>WHERE</code> clause filters out rows based on their columns' data values</li>
<li>in large databases, it's critical to use a <code>WHERE</code> clause to reduce the result set size</li>
<li>suggestion: when trying to write a query, think of the <code>FROM</code> part first, then the <code>WHERE</code> part, and lastly the <code>SELECT</code> part</li>
</ul>
</div>
<div class="slide">
<h1>More about the <code>WHERE</code> clause</h1>
<pre class="syntaxtemplate sql">
WHERE <var>column</var> <var>operator</var> <var>value(s)</var>
</pre>
<pre class="examplecode sql">
SELECT name, gnp FROM countries <em>WHERE gnp > 2000000</em>;
</pre>
<table class="sqltable">
<tr><th>code</th><th>name</th><th>gnp</th></tr>
<tr><td>JPN</td><td>Japan</td><td>3787042.00</td></tr>
<tr><td>DEU</td><td>Germany</td><td>2133367.00</td></tr>
<tr><td>USA</td><td>United States</td><td>8510700.00</td></tr>
<tr><td>...</td><td>...</td><td>...</td></tr>
</table>
<ul>
<li>the <code>WHERE</code> portion of a SELECT statement can use the following operators:
<ul>
<li><code>=</code>, <code>></code>, <code>>=</code>, <code><</code>, <code><=</code></li>
<li><code><></code>, <code>!=</code>, <code>IS NOT</code> : not equal (Java-like <code>!=</code> is a common but non-standard addition)</li>
<li><code>BETWEEN</code> <var>min</var> <code>AND</code> <var>max</var></li>
<li><code>LIKE</code> <a href="http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html"><var>pattern</var></a></li>
<li><code>IN</code> (<var>value</var>, <var>value</var>, ..., <var>value</var>)</li>
</ul>
</li>
</ul>
</div>
<div class="slide">
<h1>Multiple <code>WHERE</code> clauses: AND, OR</h1>
<pre class="examplecode sql">
SELECT * FROM cities WHERE code = 'USA' <em>AND population >= 2000000</em>;
</pre>
<table class="sqltable">
<tr><th>id</th><th>name</th><th>country_code</th><th>district</th><th>population</th></tr>
<tr><td>3793</td><td>New York</td><td>USA</td><td>New York</td><td>8008278</td></tr>
<tr><td>3794</td><td>Los Angeles</td><td>USA</td><td>California</td><td>3694820</td></tr>
<tr><td>3795</td><td>Chicago</td><td>USA</td><td>Illinois</td><td>2896016</td></tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>
</table>
<ul>
<li>multiple <code>WHERE</code> conditions can be combined using <code>AND</code> and <code>OR</code></li>
<li><code>AND</code> has precedence; good idea to use parentheses when combining <code>AND</code> and <code>OR</code></li>
</ul>
</div>
<div class="slide">
<h1>Wildcard patterns: <code>LIKE</code></h1>
<pre class="syntaxtemplate sql">
WHERE <var>column</var> LIKE <var>pattern</var>
</pre>
<pre class="examplecode sql">
SELECT code, name, population FROM countries WHERE name <em>LIKE 'United%'</em>;
</pre>
<table class="sqltable">
<tr><th>code</th><th>name</th><th>population</th></tr>
<tr><td>ARE</td><td>United Arab Emirates</td><td>2441000</td></tr>
<tr><td>GBR</td><td>United Kingdom</td><td>59623400</td></tr>
<tr><td>USA</td><td>United States</td><td>278357000</td></tr>
<tr><td>UMI</td><td>United States Minor Outlying Islands</td><td>0</td></tr>
</table>
<ul>
<li><code>%</code> wildcard means 0 or more of any character</li>
<li><code>_</code> wildcard means 1 of any character</li>
<li>escape either with a backslash to use literal <code>%</code> or <code>_</code> inside a <code>LIKE</code> term</li>
<li>negate using <code>NOT LIKE</code></li>
</ul>
</div>
<div class="slide">
<h1>Uses and pitfalls of <code>LIKE</code></h1>
<ul>
<li><code>LIKE '<var>text</var><em>%</em>'</code> searches for text that <em>starts</em> with a given prefix</li>
<li><code>LIKE '<em>%</em><var>text</var>'</code> searches for text that <em>ends</em> with a given suffix</li>
<li><code>LIKE '<em>%</em><var>text</var><em>%</em>'</code> searches for text that <em>contains</em> a given substring</li>
<li>
common error:<br/>
<code>LIKE '<var>text</var>'</code> is no different than <code>= '<var>text</var>'</code>
<ul>
<li>without wildcards, searches for text that <em>is exactly equal to</em> the given string</li>
<li>you probably meant <code>LIKE '<em>%</em><var>text</var><em>%</em>'</code></li>
</ul>
</li>
</ul>
</div>
<div class="slide">
<h1>Sorting by a column: <code>ORDER BY</code></h1>
<pre class="syntaxtemplate sql">
ORDER BY <var>column(s)</var>
</pre>
<pre class="examplecode sql">
SELECT code, name, population FROM countries
WHERE name LIKE 'United%' <em>ORDER BY population</em>;
</pre>
<table class="sqltable">
<tr><th>code</th><th>name</th><th>population</th></tr>
<tr><td>UMI</td><td>United States Minor Outlying Islands</td><td>0</td></tr>
<tr><td>ARE</td><td>United Arab Emirates</td><td>2441000</td></tr>
<tr><td>GBR</td><td>United Kingdom</td><td>59623400</td></tr>
<tr><td>USA</td><td>United States</td><td>278357000</td></tr>
</table>
<ul>
<li>can write <code>ASC</code> or <code>DESC</code> to sort in ascending (default) or descending order:
<pre class="examplecode sql">
SELECT * FROM countries <em>ORDER BY population DESC</em>;
</pre>
</li>
<li>can specify multiple orderings in decreasing order of significance:
<pre class="examplecode sql">
SELECT * FROM countries <em>ORDER BY population DESC, gnp</em>;
</pre>
</li>
<li>
see also: <a href="http://www.w3schools.com/sql/sql_groupby.asp"><code>GROUP BY</code></a>
</li>
</ul>
</div>
<div class="slide">
<h1>Limiting the result set: <code>LIMIT</code></h1>
<pre class="syntaxtemplate sql">
LIMIT <var>number</var>
</pre>
<pre class="examplecode sql">
SELECT name FROM cities WHERE name LIKE 'K%' <em>LIMIT 5</em>;
</pre>
<table class="sqltable">
<tr><th>name</th></tr>
<tr><td>Kabul</td></tr>
<tr><td>Khulna</td></tr>
<tr><td>Kingston upon Hull</td></tr>
<tr><td>Koudougou</td></tr>
<tr><td>Kafr al-Dawwar</td></tr>
</table>
<ul>
<li>
can be used to get the top-N of a given category (<code>ORDER BY</code> and <code>LIMIT</code>)
</li>
<li>
useful to get a glimpse of your query's output without seeing the entire thing (e.g., when constructing a query)
</li>
</ul>
</div>
<div class="slide titleslide">
<h1>11.3: Databases and PHP</h1>
<ul>
<li>
11.1: Database Basics
</li>
<li>
11.2: SQL
</li>
<li>
<strong>11.3: Databases and PHP</strong>
</li>
<li>
2.2.2: HTML Tables
</li>
</ul>
</div>
<div class="slide">
<h1>PHP MySQL functions</h1>
<table class="standard">
<tr>
<th>name</th>
<th>description</th>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_connect"><code>mysql_connect</code></a>
</td>
<td>
connects to a database server
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_select_db"><code>mysql_select_db</code></a>
</td>
<td>
chooses which database on server to use (similar to SQL <code>USE <var>database</var>;</code> command)
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_query"><code>mysql_query</code></a>
</td>
<td>
performs a SQL query on the database
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_real_escape_string"><code>mysql_real_escape_string</code></a>
</td>
<td>
encodes a value to make it safe for use in a query
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_fetch_array"><code>mysql_fetch_array</code></a>, ...
</td>
<td>
returns the query's next result row as an associative array
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_close"><code>mysql_close</code></a>
</td>
<td>
closes a connection to a database
</td>
</tr>
</table>
</div>
<div class="slide">
<h1>Complete PHP MySQL example</h1>
<pre class="examplecode php">
<span class="comment"># connect to world database on local computer</span>
$db = <em>mysql_connect</em>("localhost", "traveler", "packmybags");
<em>mysql_select_db</em>("world");
<span class="comment"># execute a SQL query on the database</span>
$results = <em>mysql_query</em>("SELECT * FROM countries WHERE population > 100000000;");
<span class="comment"># loop through each country</span>
while ($row = <em>mysql_fetch_array</em>($results)) {
?>
<li> <?= <em>$row["name"]</em> ?>, ruled by <?= <em>$row["head_of_state"]</em> ?> </li>
<?php
}
?>
</pre>
</div>
<div class="slide">
<h1>
Connecting to MySQL: <code>mysql_connect</code>
<span class="readingsection">(11.3.1)</span>
</h1>
<pre class="syntaxtemplate php">
<em>mysql_connect</em>("<var>host</var>", "<var>username</var>", "<var>password</var>");
<em>mysql_select_db</em>("<var>database name</var>");
</pre>
<pre class="examplecode php">
<span class="comment"># connect to world database on local computer</span>
mysql_connect("localhost", "traveler", "packmybags");
mysql_select_db("world");
</pre>
<ul>
<li><a href="http://www.php.net/mysql_connect"><code>mysql_connect</code></a> opens connection to database on its server
<ul>
<li>any/all of the 3 parameters can be omitted (default: <code>localhost</code>, anonymous)</li>
</ul>
</li>
<li><a href="http://www.php.net/mysql_select_db"><code>mysql_select_db</code></a> sets which database to examine</li>
</ul>
</div>
<div class="slide">
<h1>
Performing queries: <code>mysql_query</code>
<span class="readingsection">(11.3.2)</span>
</h1>
<pre class="syntaxtemplate php">
<span class="deemphasizedcode">mysql_connect("<var>host</var>", "<var>username</var>", "<var>password</var>");
mysql_select_db("<var>database name</var>");</span>
$results = <em>mysql_query</em>("<var>SQL query</var>");
...
</pre>
<pre class="examplecode php">
$results = mysql_query("SELECT * FROM cities WHERE code = 'USA'
AND population >= 2000000;");
</pre>
<ul>
<li><a href="http://www.php.net/mysql_query"><code>mysql_query</code></a> sends a SQL query to the database</li>
<li>returns a special result-set object that you don't interact with directly, but instead pass to later functions</li>
<li>SQL queries are in <code>"</code> <code>"</code>, end with <code>;</code>, and nested quotes can be <code>'</code> or <code>\"</code></li>
</ul>
</div>
<div class="slide">
<h1>Result rows: <code>mysql_fetch_array</code></h1>
<pre class="syntaxtemplate php">
<span class="deemphasizedcode">mysql_connect("<var>host</var>", "<var>username</var>", "<var>password</var>");
mysql_select_db("<var>database name</var>");
$results = mysql_query("<var>SQL query</var>");</span>
while ($row = <em>mysql_fetch_array</em>($results)) {
<var>do something with $row;</var>
}
</pre>
<ul>
<li><a href="http://www.php.net/mysql_fetch_array"><code>mysql_fetch_array</code></a> returns one result row as an associative array
<ul>
<li>the column names are its keys, and each column's values are its values</li>
<li>example: <code>$row["population"]</code> gives the population from that row of the results</li>
</ul>
</li>
</ul>
</div>
<div class="slide">
<h1>
Error-checking: <code>mysql_error</code>
<span class="readingsection">(11.3.3)</span>
</h1>
<pre class="examplecode php">
if (!mysql_connect("localhost", "traveler", "packmybags")) {
<em>die("SQL error occurred on connect: " . mysql_error());</em>
}
if (!mysql_select_db("world")) {
<em>die("SQL error occurred selecting DB: " . mysql_error());</em>
}
$query = "SELECT * FROM countries WHERE population > 100000000;";
$results = mysql_query($query);
if (!$results) {
<em>die("SQL query failed:\n$query\n" . mysql_error());</em>
}
</pre>
<ul>
<li>SQL commands can fail: database down, bad password, bad query, ...</li>
<li>for debugging, always test the results of PHP's <code>mysql</code> functions
<ul>
<li>if they fail, stop script with <code>die</code> function, and print <code>mysql_error</code> result to see what failed</li>
<li>give a descriptive error message and also print the query, if any</li>
</ul>
</li>
</ul>
</div>
<div class="slide">
<h1>Complete example w/ error checking</h1>
<pre class="examplecode php" style="font-size: smaller">
<span class="comment"># connect to world database on local computer</span>
<em>check(</em>mysql_connect("localhost", "traveler", "packmybags")<em>, "connect");</em>
<em>check(</em>mysql_select_db("world")<em>, "selecting db");</em>
<span class="comment"># execute a SQL query on the database</span>
$query = "SELECT * FROM countries WHERE population > 100000000;";
$results = mysql_query($query);
<em>check(</em>$results<em>, "query of $query");</em>
<span class="comment"># loop through each country</span>
while ($row = mysql_fetch_array($results)) {
?>
<li> <?= $row["name"] ?>, ruled by <?= $row["head_of_state"] ?> </li>
<?php
}
<span class="comment"># makes sure result is not false/null; else prints error</span>
<em>function check($result, $message) {
if (!$result) {
die("SQL error during $message: " . mysql_error());
}
}</em>
?>
</pre>
</div>
<div class="slide">
<h1>Other MySQL PHP functions</h1>
<table class="standard">
<tr>
<th>name</th>
<th>description</th>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_num_rows"><code>mysql_num_rows</code></a>
</td>
<td>
returns number of rows matched by the query
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_num_fields"><code>mysql_num_fields</code></a>
</td>
<td>
returns number of columns per result in the query
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_list_dbs"><code>mysql_list_dbs</code></a>
</td>
<td>
returns a list of databases on this server
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_list_tables"><code>mysql_list_tables</code></a>
</td>
<td>
returns a list of tables in current database
</td>
</tr>
<tr>
<td>
<a href="http://www.php.net/mysql_list_fields"><code>mysql_list_fields</code></a>
</td>
<td>
returns a list of fields in the current data
</td>
</tr>
<tr>
<td colspan="2" class="completelist">
<a href="http://www.php.net/manual/en/ref.mysql.php">complete list</a>
</td>
</tr>
</table>
</div>
<div class="slide titleslide">
<h1>2.2.2: HTML Tables</h1>
<ul>
<li>
11.1: Database Basics
</li>
<li>
11.2: SQL
</li>
<li>
11.3: Databases and PHP
</li>
<li>
<strong>2.2.2: HTML Tables</strong>
</li>
</ul>
</div>
<div class="slide">
<h1>HTML tables:
<a href="http://www.w3schools.com/tags/tag_table.asp"><code><table></code></a>,
<a href="http://www.w3schools.com/tags/tag_tr.asp"><code><tr></code></a>,
<a href="http://www.w3schools.com/tags/tag_td.asp"><code><td></code></a>
</h1>
<p class="description">
A 2D table of rows and columns of data (block element)
</p>
<div class="example">
<pre class="examplecode html">
<table>
<tr><td>1,1</td><td>1,2 okay</td></tr>
<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
</pre>
<div class="exampleoutput">
<table>
<tr><td>1,1</td><td>1,2 okay</td></tr>
<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
</div>
</div>
<ul>
<li><code>table</code> defines the overall table, <code>tr</code> each row, and <code>td</code> each cell's data</li>
<li>tables are useful for displaying large row/column data sets</li>
<li>
NOTE: tables are sometimes used by novices for web page layout, but this is not proper semantic HTML and should be avoided
</li>
</ul>
</div>
<div class="slide">
<h1>Table headers, captions:
<a href="http://www.w3schools.com/tags/tag_th.asp"><code><th></code></a>,
<a href="http://www.w3schools.com/tags/tag_caption.asp"><code><caption></code></a>
</h1>
<div class="example">
<pre class="examplecode html">
<table>
<em><caption></em>My important data<em></caption></em>
<tr><em><th></em>Column 1<em></th></em><em><th></em>Column 2<em></th></em></tr>
<tr><td>1,1</td><td>1,2 okay</td></tr>
<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
</pre>
<div class="exampleoutput">
<table>
<caption>My important data</caption>
<tr><th>Column 1</th><th>Column 2</th></tr>
<tr><td>1,1</td><td>1,2 okay</td></tr>
<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
</div>
</div>
<ul>
<li><code>th</code> cells in a row are considered headers; by default, they appear bold</li>
<li>a <code>caption</code> at the start of the table labels its meaning</li>
</ul>
</div>
<div class="slide">
<h1>
Styling tables
<span class="readingsection">(3.2.6)</span>
</h1>
<div class="example">
<pre class="examplecode css">
table { border: 2px solid black; caption-side: bottom; }
tr { font-style: italic; }
td { background-color: yellow; text-align: center; width: 30%; }
</pre>
<div class="exampleoutput insertoutput">
<table>
<caption>My important data</caption>
<tr><th>Column 1</th><th>Column 2</th></tr>
<tr><td>1,1</td><td>1,2 okay</td></tr>
<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
</div>
</div>
<ul>
<li>all standard CSS styles can be applied to a table, row, or cell</li>
<li>table specific CSS properties:
<ul>
<li><a href="http://www.w3schools.com/css/pr_tab_border-collapse.asp"><code>border-collapse</code></a>, <a href="http://www.w3schools.com/css/pr_tab_border-spacing.asp"><code>border-spacing</code></a>, <a href="http://www.w3schools.com/css/pr_tab_caption-side.asp"><code>caption-side</code></a>, <a href="http://www.w3schools.com/css/pr_tab_empty-cells.asp"><code>empty-cells</code></a>, <a href="http://www.w3schools.com/css/pr_tab_table-layout.asp"><code>table-layout</code></a></li>
</ul>
</li>
</ul>
</div>
<div class="slide">
<h1>The <a href="http://www.w3schools.com/css/pr_tab_border-collapse.asp"><code>border-collapse</code></a> property</h1>
<pre class="examplecode css">
table, td, th { border: 2px solid black; }
table { <em>border-collapse: collapse;</em> }
</pre>
<div class="bordered" style="float: left">
<table>
<caption>Without <code>border-collapse</code></caption>
<tr><th>Column 1</th><th>Column 2</th></tr>
<tr><td>1,1</td><td>1,2</td></tr>
<tr><td>2,1</td><td>2,2</td></tr>
</table>
</div>
<div class="bordered" style="float: right">
<table style="border-collapse: collapse">
<caption>With <code>border-collapse</code></caption>
<tr><th>Column 1</th><th>Column 2</th></tr>
<tr><td>1,1</td><td>1,2</td></tr>
<tr><td>2,1</td><td>2,2</td></tr>
</table>
</div>
<ul style="clear: both; padding-top: 20px;">
<li>by default, the overall table has a separate border from each cell inside</li>
<li>the <code>border-collapse</code> property merges these borders into one</li>
</ul>
</div>
<div class="slide">
<h1>The <code>rowspan</code> and <code>colspan</code> attributes</h1>
<div class="example">
<pre class="examplecode html">
<table>
<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
<tr><td <em>colspan="2"</em>>1,1-1,2</td>
<td <em>rowspan="3"</em>>1,3-3,3</td></tr>
<tr><td>2,1</td><td>2,2</td></tr>
<tr><td>3,1</td><td>3,2</td></tr>
</table>
</pre>
<div class="exampleoutput insertoutput bordered"></div>
</div>
<ul>
<li><code>colspan</code> makes a cell occupy multiple columns; <code>rowspan</code> multiple rows</li>
<li><code>text-align</code> and <code>vertical-align</code> control where the text appears within a cell</li>
</ul>
</div>
<div class="slide">
<h1>Column styles:
<a href="http://www.w3schools.com/tags/tag_col.asp"><code><col></code></a>,
<a href="http://www.w3schools.com/tags/tag_colgroup.asp"><code><colgroup></code></a>
</h1>
<div class="example">
<pre class="examplecode html">
<table>
<em><col class="urgent" />
<colgroup class="highlight" span="2"></colgroup></em>
<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
<tr><td>1,1</td><td>1,2</td><td>1,3</td></tr>
<tr><td>2,1</td><td>2,2</td><td>2,3</td></tr>
</table>
</pre>
<style type="text/css">
.urgent {
background-color: pink;
}
.highlight {
background-color: yellow;
}
</style>
<div class="exampleoutput insertoutput bordered"></div>
</div>
<ul>
<li><code>col</code> tag can be used to define styles that apply to an entire column (self-closing)</li>
<li><code>colgroup</code> tag applies a style to a group of columns (NOT self-closing)</li>
</ul>
</div>
<div class="slide">
<h1>Don't use tables for layout!</h1>
<ul>
<li>(borderless) tables appear to be an easy way to achieve grid-like page layouts
<ul>
<li>many "newbie" web pages do this (including many UW CSE web pages...)</li>
</ul>
</li>
<li>but, a <code>table</code> has semantics; it should be used only to represent an actual table of data</li>
<li>instead of tables, use <code>div</code>s, widths/margins, floats, etc. to perform layout</li>
</ul>
<hr />
<ul style="margin-top: 2em">
<li>tables should not be used for layout!</li>
<li class="incremental" style="font-size: 120%">Tables should not be used for layout!!</li>
<li class="incremental" style="font-size: 140%">TABLES SHOULD NOT BE USED FOR LAYOUT!!!</li>
<li class="incremental" style="font-size: 180%; text-decoration: blink;"><strong>TABLES</strong> SHOULD <strong>NOT</strong> BE USED FOR <strong>LAYOUT</strong>!!!!</li>
</ul>
</div>
<!--#include virtual="commonbottom.html" -->