Dynamic Pages
with PHP/MySQL
Overview
- HTTP life cycle
- PHP / MySQL review
- mysql improved
- PHP Data Objects
- classes, objects, & methods
- SQL joins
- Git/Github
HTTP Lifecycle
http request
http response
mysql
$con = mysql_connect("itp460.usc.edu", "student", "ttrojan");
mysql_select_db("dvd", $con);
$sql = "SELECT * FROM genres ORDER BY genre";
$results = mysql_query($sql);
while ($row = mysql_fetch_array($results)) {
echo $row["genre"] . "<br />";
}
mysql improved
$con = mysqli_connect('itp460.usc.edu', 'student', 'ttrojan', 'dvd');
$results = mysqli_query($con, 'SELECT * FROM genres');
while ($row = mysqli_fetch_array($results)) {
echo $row['genre'];
echo '<br />';
}
Wouldn't it be nice if there was a uniform way of accessing ANY type of SQL based database?
PHP Data Objects (PDO)
$connection_string = "mysql:host=$host;dbname=$dbname";
$pdo = new PDO($connection_string, $user, $pass);
Classes and Objects
$macbookPro = new Macbook('Macbook Pro');
$macbookAir = new Macbook('Macbook Air');
$macbookPro->model; // Macbook Pro
$macbookAir->model; // Macbook Air
$macbookPro->playMusic();
$macbookAir->playMusic();
Classes are the foundation of object oriented programming
Classes are like blueprints
Objects are isolated instances of classes
Objects contain variables (called properties)
Objects contain behaviors/functions (called methods)
We will learn how to create custom classes next week!
Let's learn how to use PDO
PDO Example
$host = 'itp460.usc.edu';
$dbname = 'music';
$user = 'student';
$pass = 'ttrojan';
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$sql = "SELECT title, price, play_count FROM songs";
$statement = $pdo->prepare($sql);
$statement->execute();
$songs = $statement->fetchAll();
var_dump($songs);
Records as arrays
Displaying the data
<?php foreach ($songs as $song) {
echo '<div class="song">';
echo '<h3>' . $song['title'] . '</h3>';
echo '</div>';
?>
PHP Alternate Syntax
<?php foreach ($songs as $song) : ?>
<div class="song">
<h3><?php echo $song['title']; ?></h3>
<p>Play Count: <?php echo $song['play_count']; ?></p>
<p>Price: $<?php echo $song['price']; ?></p>
</div>
<?php endforeach; ?>
PDO Example 2
$host = 'itp460.usc.edu';
$dbname = 'music';
$user = 'student';
$pass = 'ttrojan';
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$sql = "SELECT title, price, play_count FROM songs";
$statement = $pdo->prepare($sql);
$statement->execute();
$songs = $statement->fetchAll(PDO::FETCH_OBJ);
var_dump($songs);
Records as objects
PHP Alternate Syntax
<?php foreach ($songs as $song) : ?>
<div class="song">
<h3><?php echo $song->title; ?></h3>
<p>Play Count: <?php echo $song->play_count; ?></p>
<p>Price: $<?php echo $song->price; ?></p>
</div>
<?php endforeach; ?>
SQL Joins Review
SELECT *
FROM songs, artists
WHERE songs.artist_id = artists.id
SELECT *
FROM songs
INNER JOIN artists
ON songs.artist_id = artists.id
SQL Results
Column Conflicts
Select column names individually to avoid conflicts with columns of the same name in join operations
SELECT songs.id AS id, title, price, artist_name
FROM songs
INNER JOIN artists
ON songs.artist_id = artists.id
Git
- Git is a version control / code management tool
- Allows devs to keep versions / history of their code
- Can revert back to your code at any point in time
- Can create experimental 'branches'
- Git tracks changes
git init
git add --all
git commit -m 'first commit'
// make some changes
git add --all
git commit -m 'adds an about page to project'
Github
- Social network for coding
- Central repository for development teams
git remote add origin git@github.com:skaterdav85/php-pdo.git
git push -u origin master
You only have to do those once. Any time you push to Github you can simply do:
git push
Git/GitHub Workflow
git init
git add --all
git commit -m 'first commit'
git remote add origin git@github.com:skaterdav85/php-pdo.git
git push -u origin master
// make some changes like adding a new feature or bug fixes
git add --all
git commit -m 'adds an about page to project'
git push
dynamic-pages
By David Tang
