Updating data - article

article lists 

$sql = "SELECT a.id, a.title, a.summary, a.created, a.category_id, a.member_id, a.published,
               c.name     AS category,
               CONCAT(m.forename, ' ', m.surname) AS author,
               i.file     AS image_file,
               i.alt      AS image_alt 
          FROM article    AS a
          JOIN category   AS c   ON a.category_id = c.id
          JOIN member     AS m   ON a.member_id   = m.id
          LEFT JOIN image AS i   ON a.image_id    = i.id
         ORDER BY a.id DESC;";
$articles = pdo($pdo, $sql)->fetchAll();

<section>
    <h1>Articles</h1>
    <?php if ($success) { ?><div><?= $success ?></div><?php } ?>
    <?php if ($failure) { ?><div><?= $failure ?></div><?php } ?>
    <p><a href="article.php" > + NEW </a></p>
</section>
<table>
  <tr>
    <th>Image</th><th>Title</th>
    <th>Created</th>
    <th class="pub">Published</th>
    <th class="edit">Edit</th>
    <th class="del">Delete</th>
  </tr>
  <?php foreach ($articles as $article) { ?>
  <tr>
    <td><img src="../uploads/<?= html_escape($article['image_file'] ?? 'blank.png') ?>"
                alt="<?= html_escape($article['image_alt']) ?>"></td>
    <td><strong><?= html_escape($article['title']) ?></strong></td>
    <td><?= format_date($article['created']) ?></td>
    <td><?= ($article['published']) ? 'Yes' : 'No' ?></td>
    <td><a href="article.php?id=<?= $article['id'] ?>" >Edit</a></td>
    <td><a href="article-delete.php?id=<?= $article['id'] ?>" >Delete</a></td>
  </tr>
  <?php } ?>
</table>

---

article new, update

1. 세팅 

include '../includes/database-connection.php';
include '../includes/functions.php'; 
include '../includes/validate.php';    
//파일업로드
$uploads = dirname(__DIR__, 1) . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR; 
$file_types      = ['image/jpeg', 'image/png', 'image/gif',]; 
$file_extensions = ['jpg', 'jpeg', 'png', 'gif',];   
$max_size        = '5242880';//5메가

//아디디가져오고+유효성검사
$id          = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT); 
$temp        = $_FILES['image']['tmp_name'] ?? '';  //임시이미지
$destination = ''; //파일경로 

//변수 초기화
$article = [
    'id'          => $id,
    'title'       => '',
    'summary'     => '',
    'content'     => '',
    'member_id'   => 0,
    'category_id' => 0,
    'image_id'    => null,
    'published'   => false,
    'image_file'  => '',
    'image_alt'   => '',
];  
$errors  = [
    'warning'     => '',
    'title'       => '',
    'summary'     => '',
    'content'     => '',
    'author'      => '',
    'category'    => '',
    'image_file'  => '',
    'image_alt'   => '',
]; 

if ($id) {    //id 있다면 편집 모드 
    $sql     = "SELECT a.id, a.title, a.summary, a.content,  
                       a.category_id, a.member_id, a.image_id, a.published,
                       i.file      AS image_file,
                       i.alt       AS image_alt 
                  FROM article     AS a
                  LEFT JOIN image  AS i ON a.image_id = i.id
                 WHERE a.id = :id;"; 
    $article = pdo($pdo, $sql, [$id])->fetch();  

    if (!$article) {  
        redirect('articles.php', ['failure' => 'Article not found']); 
    }
}

$saved_image = $article['image_file'] ? true : false;  

$sql     = "SELECT id, forename, surname FROM member;"; 
$authors = pdo($pdo, $sql)->fetchAll(); 

$sql        = "SELECT id, name FROM category;"; 
$categories = pdo($pdo, $sql)->fetchAll();

 

2. 데이터 가져오고 검사

if ($_SERVER['REQUEST_METHOD'] == 'POST') {  
// php.ini or .htaccess 
$errors['image_file'] = ($temp === '' and $_FILES['image']['error'] === 1) ? 'File too big ' : '';

// 이미지 업로드 되었다면 검사 
if ($temp and $_FILES['image']['error'] === 0) { 
    $article['image_alt'] = $_POST['image_alt']; 
    $errors['image_file'] = in_array(mime_content_type($temp), $file_types)
        ? '' : 'Wrong file type. '; 
    $extension = strtolower(pathinfo($_FILES['image']['name'], PATHINFO_EXTENSION)); 
    $errors['image_file'] .= in_array($extension, $file_extensions)
        ? '' : 'Wrong file extension. ';   
    $errors['image_file'] .= ($_FILES['image']['size'] <= $max_size)
        ? '' : 'File too big. ';  
    $errors['image_alt']  = (is_text($article['image_alt'], 1, 254))
        ? '' : 'Alt text must be 1-254 characters.';  

    // 이미지 검사 통과시 경로 지정 
    if ($errors['image_file'] === '' and $errors['image_alt'] === '') { 
        $article['image_file'] = create_filename($_FILES['image']['name'], $uploads);
        $destination = $uploads . $article['image_file']; 
    }
}

// daticle 가져오기 
$article['title']       = $_POST['title']; 
$article['summary']     = $_POST['summary'];
$article['content']     = $_POST['content'];  
$article['member_id']   = $_POST['member_id'];  
$article['category_id'] = $_POST['category_id']; 
$article['published']   = (isset($_POST['published']) and ($_POST['published'] == 1)) ? 1 : 0;  

// article 검사하기 
$errors['title']    = is_text($article['title'], 1, 80)
    ? '' : 'Title must be 1-80 characters';
$errors['summary']  = is_text($article['summary'], 1, 254)
    ? '' : 'Summary must be 1-254 characters';
$errors['content']  = is_text($article['content'], 1, 100000)
    ? '' : 'Article must be 1-100,000 characters';
$errors['member']   = is_member_id($article['member_id'], $authors)
    ? '' : 'Please select an author';
$errors['category'] = is_category_id($article['category_id'], $categories)
    ? '' : 'Please select a category';

$invalid = implode($errors);

 

3. 변경사항 업데이트 

 

 

4. 폼

<form action="article.php?id=<?= $id ?>" method="POST" enctype="multipart/form-data">
<main class="container admin" id="content">

<div class="admin-article">
<section class="image">
    <?php if (!$article['image_file']) { ?>
    <label for="image">Upload image:</label>
    <div class="form-group">
        <input type="file" name="image" id="image"><br>
        <span class="errors"><?= $errors['image_file'] ?></span>
        <label for="image_alt">Alt text: </label>
        <input type="text" name="image_alt" id="image_alt" value="" >
        <span class="errors"><?= $errors['image_alt'] ?></span>
    </div>
    <?php } else { ?>
    <label>Image:</label>
    <img src="../uploads/<?= html_escape($article['image_file']) ?>"
            alt="<?= html_escape($article['image_alt']) ?>">
    <p>Alt text: <?= html_escape($article['image_alt']) ?></p>
    <a href="alt-text-edit.php?id=<?= $article['id'] ?>" > Edit alt text </a>
    <a href="image-delete.php?id=<?= $id ?>"> Delete image </a>
    <?php } ?>
</section>

<section class="text">
    <div class="form-group">
    <label for="title">Title: </label>
    <input type="text" name="title" id="title" 
    value="<?= html_escape($article['title']) ?>" >
    <span class="errors"><?= $errors['title'] ?></span>
    </div>
    <div class="form-group">
    <label for="summary">Summary: </label>
    <textarea name="summary" id="summary">
    <?= html_escape($article['summary']) ?></textarea>
    <span class="errors"><?= $errors['summary'] ?></span>
    </div>
    <div class="form-group">
    <label for="content">Content: </label>
    <textarea name="content" id="content"
                class="form-control"><?= html_escape($article['content']) ?></textarea>
    <span class="errors"><?= $errors['content'] ?></span>
    </div>

    <div class="form-group">
    <label for="member_id">Author: </label>
    <select name="member_id" id="member_id">
        <?php foreach ($authors as $author) { ?>
        <option value="<?= $author['id'] ?>"
            <?= ($article['member_id'] == $author['id']) ? 'selected' : ''; ?>>
            <?= html_escape($author['forename'] . ' ' . $author['surname']) ?></option>
        <?php } ?>
    </select>
    </div>
    <div class="form-group">
    <label for="category">Category: </label>
    <select name="category_id" id="category">
        <?php foreach ($categories as $category) { ?>
        <option value="<?= $category['id'] ?>"
            <?= ($article['category_id'] == $category['id']) ? 'selected' : ''; ?>>
            <?= html_escape($category['name']) ?></option>
        <?php } ?>
    </select>
    </div>
    
    <div class="form-check">
    <input type="checkbox" name="published" value="1" id="published"
        <?= ($article['published'] == 1) ? 'checked' : ''; ?>>
    <label for="published" >Published</label>
    </div>
    <input type="submit" name="update" value="Save">
</section>
</div>
</main>
</form>

---

arcle delete 

// id검증 
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT); 
if (!$id) {  
    redirect('articles.php', ['failure' => 'Article not found']); 
}

$article = false;  //초기화
$sql = "SELECT a.title, a.image_id, 
               i.file      AS image_file 
          FROM article     AS a
          LEFT JOIN image  AS i  ON a.image_id    = i.id
         WHERE a.id = :id;";  
$article = pdo($pdo, $sql, [$id])->fetch();
if (!$article) { 
    redirect('articles.php', ['failure' => 'Article not found']); 
}

if ($_SERVER['REQUEST_METHOD'] == 'POST') { 
    try {
        $pdo->beginTransaction();   //트랜젝션 시작 

        if ($article['image_id']) {     //이미지 있으면 삭제 
            $sql = "UPDATE article SET image_id = null WHERE id = :article_id;"; 
            pdo($pdo, $sql, [$id]);  
            $sql = "DELETE FROM image WHERE id = :id;";
            pdo($pdo, $sql, [$article['image_id']]);  
            $path = '../uploads/' . $article['image_file']; 
            if (file_exists($path)) {   //이미지파일 존재한다면 
                $unlink = unlink($path);   //삭제 
            }
        }

        $sql = "DELETE FROM article WHERE id = :id;";
        pdo($pdo, $sql, [$id]);   
        $pdo->commit();  
        redirect('articles.php', ['success' => 'Article deleted']); 
    } catch (PDOException $e) {  //예외발생시
        $pdo->rollBack();   
        throw $e; 
    }
}

<form action="article-delete.php?id=<?= $id ?>" method="POST">
    <h1>Delete Article</h1>
    <p>Click confirm to delete the article: 
    <?= html_escape($article['title']) ?></p>
    <input type="submit" name="delete" value="Confirm">
    <a href="articles.php" >Cancel</a>
</form>