Info2180 Lecture 7
Info2180 Lecture 7
SQL
SQL
OVERVIEW OF SQL
▸ is an ANSI (American National Standards Institute) standard.
▸ You can use SQL to retrieve, insert, update and delete data from a
database.
▸ You can also create new databases and tables for those databases.
▸ And you can grant permissions to users for tables, views and
procedures.
RELATIONAL
DATABASES
SQL
Column/Field/Attribute
Table
SELECT STATEMENTS
DISTINCT MODIFIER
WHERE CLAUSE
LIKE OPERATOR
fi
SQL
LIKE OPERATOR
LIKE OPERATOR
ORDER BY KEYWORD
UPDATE table_name
SET column1=value1, column2=value2,…
WHERE some_column=some_value;
SQL
TYPES OF JOINS
▸ There are different types of joins, here are a few:
▸ LEFT JOIN
▸ RIGHT JOIN
▸ NATURAL JOIN
DESIGNING A QUERY
▸ To determine what query you need, start by asking:
http://php.net/manual/en/intro.pdo.php
SQL
PDO EXAMPLE
// PDO + MySQL
$pdo = new
PDO('mysql:host=example.com;dbname=database',
'user', 'password');
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['some_field']);
SQL
PDO EXAMPLE
PDO EXAMPLE
$stmt->execute();
SQL
PREPARED STATEMENTS
▸ There are multiple bene ts to using prepared statements, both
for performance and security reasons.
?>
HTML TABLES
HTML TABLES
OVERVIEW TABLES
▸ Back in the old days, tables were used not only for tabular
data but also to layout pages.
▸ But tables were not meant for layouts and this too resulted
in a number of associated problems.
<table>
<caption>List of Books</caption>
<tr>
<th>Title</th>
<th>Stock</th>
<th>Quantity</th>
<th>Price</th>
</tr>
…
</table>
EXAMPLE HTML TABLE WITH THEAD
<table>
<thead>
<tr>
<th>Title</th>
<th>Stock</th>
<th>Quantity</th>
<th>Price</th>
</tr>
</thead>
…
</table>
EXAMPLE HTML TABLE WITH TBODY
<table>
…
<tbody>
<tr>
<td>Don’t Make Me Think by Steve Krug</td>
<td>In Stock</td>
<td>1</td>
<td>$30.02</td>
</tr>
…
</tbody>
</table>
SQL
▸ border-spacing
▸ caption-side
▸ empty-cells
<?php
$stmt = $pdo->query("SELECT * FROM STUDENTS");
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
EXAMPLE OF PRINTING DATABASE DATA USING PHP IN A TABLE
<table>
<thead>
<tr>
<th>Student ID</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<?php foreach ($students as $student): ?>
<tr>
<td><?= $student['id']; ?></td>
<td><?= $student['name']; ?></td>
<td><?= $student['email']; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
SQL
RESOURCES
▸ PDO - http://php.net/manual/en/book.pdo.php