GTB、LAMP開発基礎

# LAMP開発基礎




以下の内容を実践済みの方から進めてください。

[GTB、LAMP開発基礎の自習内容](http://qiita.com/youhei_nakagawa/items/860645c1b3d937a57d18)

[GTB、LAMP開発基礎の自習内容 MySQL編](http://qiita.com/youhei_nakagawa/items/91fd071817400ec34f14)


## 1.PHPからMySQLへの接続

```php
<?php
/**
 * データベース接続確認
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}
echo "ok";
```

## 2.PHPからMySQLのデータを出力

```php
<?php
/**
 * 2.PHPからMySQLのデータを出力
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}
$sql = 'select * from films_title limit 10';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$resultdata = array();

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
print_r($resultdata);
```

## 3.PHPからMySQLのデータを条件を絞って出力

```php
<?php
/**
 * 3.PHPからMySQLのデータを条件を絞って出力
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}
$sql = 'select * from films_title where category_id = ? or category_id = ?';
$params = array(2, 5);
$stmt = $dbh->prepare($sql);
$stmt->execute($params);
$resultdata = array();

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
print_r($resultdata);
```

## 4.HTMLとして出力

```html+php
<?php
/**
 * 4.HTMLとして出力
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}
$sql = 'select * from films_title';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$resultdata = array();

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<h1>映画タイトル一覧</h1>
<?php foreach ($resultdata as $data) { ?>
    <div><?=$data['title']?></div>
<?php } ?>
</body>
</html>
```

## 5.HTMLとして出力 short_tag


```html+php
<?php
/**
 * 5.HTMLとして出力 short_tag
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}
$sql = 'select * from films_title';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$resultdata = array();

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<h1>映画タイトル一覧</h1>
<?php foreach ($resultdata as $data) : ?>
    <div><?=$data['title']?></div>
<?php endforeach; ?>
</body>
</html>
```

## GETとPOST

```html+php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<?php
echo "GET:";
print_r($_GET);
echo "POST:";
print_r($_POST);
?>
<form method="get" action="?">
    <input type="text" name="id" value="">
    <input type="submit" value="GETで送信">
</form>
<form method="post" action="?">
    <input type="text" name="id" value="">
    <input type="submit" value="POSTで送信">
</form>
</body>
</html>
```

## 6.GETによるカテゴリー表示の絞り込み

```html+php
<?php
/**
 * 6.GETによるカテゴリー表示の絞り込み
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}

$params = array();
if ($_GET['category']) {
    $sql = 'select * from films_title where category_id = ?';
    $params[] = $_GET['category'];
} else {
    $sql = 'select * from films_title';
}
$stmt = $dbh->prepare($sql);
$stmt->execute($params);
$resultdata = array();

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<h1>映画タイトル一覧</h1>
<?php foreach ($resultdata as $data) : ?>
<div><?=$data['title']?></div>
<?php endforeach; ?>
</body>
</html>
```

## 7.映画のカテゴリーとタイトルを同時に表示しよう

```html+php
<?php
/**
 * 7.GETによるカテゴリー表示の絞り込み
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}
$sql = 'select * from films_category a join films_title b on a.id = b.category_id';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$resultdata = array();

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
//print_r($resultdata);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<h1>映画タイトル一覧</h1>
<?php foreach ($resultdata as $data) : ?>
    <div><?=$data['category']?>:<?=$data['title']?></div>
<?php endforeach; ?>
</body>
</html>
```

## 8.カテゴリーの絞り込みのリンクをつけてみる

```html+php
<?php
/**
 * 8.カテゴリーの絞り込みのリンクをつけてみる
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}

$params = array();
if ($_GET['category']) {
    $sql = 'select * from films_category a join films_title b on a.id = b.category_id where b.category_id = ?';
    $params[] = $_GET['category'];
} else {
    $sql = 'select * from films_category a join films_title b on a.id = b.category_id';
}
$stmt = $dbh->prepare($sql);
$stmt->execute($params);

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<h1>映画タイトル一覧</h1>
<?php foreach ($resultdata as $data) : ?>
    <div><a href="?category=<?=$data['category_id']?>"><?=$data['category']?></a>:<?=$data['title']?></div>
<?php endforeach; ?>
</body>
</html>
```

## 9.作品を追加してみる

出来上がりのイメージ

![image](https://qiita-image-store.s3.amazonaws.com/0/111110/e64ff78e-9dcc-7c37-148e-db7e6906e428.png)

```html+php
<?php
/**
 * 9.作品を追加してみる
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}

/**
 * POSTされたデータにfunc=addがあった時作品の追加を行う
 */
if (isset($_POST['func']) && $_POST['func'] == 'add' && $_POST['category_id'] != '' && $_POST['title'] != '') {
    $sql = 'insert into films_title (category_id, title) value (?, ?)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($_POST['category_id'], $_POST['title']));
}

/**
 * カテゴリーデータのみのリストを作成
 */
$sql = 'select id, category from films_category';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$category = array();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $category[] = $result;
}

$params = array();
if ($_GET['category']) {
    $sql = 'select * from films_category a join films_title b on a.id = b.category_id where b.category_id = ?';
    $params[] = $_GET['category'];
} else {
    $sql = 'select * from films_category a join films_title b on a.id = b.category_id';
}
$stmt = $dbh->prepare($sql);
$stmt->execute($params);

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<h1>映画タイトル一覧</h1>
<?php foreach ($resultdata as $data) : ?>
<div><a href="?category=<?=$data['category_id']?>"><?=$data['category']?></a>:<?=$data['title']?></div>
<?php endforeach; ?>

<h2>映画の追加</h2>
<form method="post" action="?">
    <select name="category_id">
    <?php foreach ($category as $cat) : ?>
        <option value="<?=$cat['id']?>"><?=$cat['id']?> : <?=$cat['category']?></option>
    <?php endforeach; ?>
    </select>
    <input type="text" name="title" value="">
    <input type="hidden" name="func" value="add">
    <input type="submit" value="追加">
</form>

</body>
</html>
```

## 10.クロスサイトスクリプティング攻撃対応

```html+php
<?php
/**
 * 10.クロスサイトスクリプティング攻撃対応
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = '';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}

function hx($str) {
    return htmlspecialchars($str);
}

/**
 * POSTされたデータにfunc=addがあった時作品の追加を行う
 */
if (isset($_POST['func']) && $_POST['func'] == 'add' && $_POST['category_id'] != '' && $_POST['title'] != '') {
    $sql = 'insert into films_title (category_id, title) value (?, ?)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($_POST['category_id'], $_POST['title']));
}

/**
 * カテゴリーデータのみのリストを作成
 */
$sql = 'select id, category from films_category';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$category = array();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $category[] = $result;
}

$params = array();
if ($_GET['category']) {
    $sql = 'select * from films_category a join films_title b on a.id = b.category_id where b.category_id = ?';
    $params[] = $_GET['category'];
} else {
    $sql = 'select * from films_category a join films_title b on a.id = b.category_id';
}
$stmt = $dbh->prepare($sql);
$stmt->execute($params);

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $resultdata[] = $result;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル一覧</title>
</head>
<body>
<h1>映画タイトル一覧</h1>
<?php foreach ($resultdata as $data) : ?>
<div><a href="?category=<?=hx($data['category_id'])?>"><?=hx($data['category'])?></a>:<?=hx($data['title'])?></div>
<?php endforeach; ?>

<h2>映画の追加</h2>
<form method="post" action="?">
    <select name="category_id">
    <?php foreach ($category as $cat) : ?>
        <option value="<?=hx($cat['id'])?>"><?=hx($cat['id'])?> : <?=hx($cat['category'])?></option>
    <?php endforeach; ?>
    </select>
    <input type="text" name="title" value="">
    <input type="hidden" name="func" value="add">
    <input type="submit" value="追加">
</form>

</body>
</html>
```

## 編集画面の作成

```html+php:edit.php
<?php
/**
 * 11.編集画面付き
 */
$dsn = 'mysql:dbname=filmsdb;host=localhost';
$user = 'root';
$password = 'nx4uaPv8S';
try{
    $dbh = new PDO($dsn, $user, $password);
}catch (PDOException $e){
    echo('Connection failed:'.$e->getMessage());
    die();
}

function hx($str) {
    return htmlspecialchars($str);
}

/**
 * カテゴリーデータのみのリストを作成
 */
$sql = 'select id, category from films_category';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$category = array();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $category[] = $result;
}

$params = array();
if ($_GET['title_id']) {
    $sql = 'select * from films_title where title_id = ?';
    $params[] = $_GET['title_id'];
} else {
    $sql = 'select * from films_title';
}
$stmt = $dbh->prepare($sql);
$stmt->execute($params);

$result = $stmt->fetch(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>映画タイトル変更</title>
</head>
<body>
<h1>映画タイトル変更</h1>
<form method="post" action="011.php">
    <select name="category_id">
    <?php foreach ($category as $cat) : ?>
        <option value="<?=hx($cat['id'])?>" <?php if ($result['category_id'] == $cat['id']) : echo "selected"; endif; ?>><?=hx($cat['id'])?> : <?=hx($cat['category'])?></option>
    <?php endforeach; ?>
    </select>
    <input type="text" name="title" value="<?=hx($result['title'])?>">
    <input type="hidden" name="title_id" value="<?=hx($result['title_id'])?>">
    <input type="hidden" name="func" value="edit">
    <input type="submit" value="変更">
</form>

</body>
</html>
```

```html+php:011.php
<?php
if (isset($_POST['func']) && $_POST['func'] == 'edit' && $_POST['title_id'] != '' && $_POST['title_id'] != '')
{
    $sql = 'update films_title set title = ? where title_id = ?';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($_POST['title'], $_POST['title_id']));
}
```