LoginSignup
0
0

More than 5 years have passed since last update.

@youhei_nakagawa(ようへい なかがわ)

GTB、LAMP開発基礎

Last updated at Posted at 2016-05-27

LAMP開発基礎

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

GTB、LAMP開発基礎の自習内容

GTB、LAMP開発基礎の自習内容 MySQL編

1.PHPからMySQLへの接続

<?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
/**
 * 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
/**
 * 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として出力

<?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

<?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

<!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によるカテゴリー表示の絞り込み

<?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.映画のカテゴリーとタイトルを同時に表示しよう

<?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.カテゴリーの絞り込みのリンクをつけてみる

<?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

<?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.クロスサイトスクリプティング攻撃対応

<?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>

編集画面の作成

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>
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']));
}
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0