PHP / MariaDB / 데이터베이스의 데이터 수정하기

이 강좌에서 사용하는 데이터베이스는 employees입니다. (employees 설치 하기)

employees.php

Edit 버튼을 추가합니다. employees_edit.php에 emp_no의 값을 전달합니다.

<!doctype html>
<html lang="ko">
  <head>
    <meta charset="utf-8">
    <title>Employees</title>
    <style>
      body {
        font-family: Consolas, monospace;
        font-family: 12px;
      }
      table {
        width: 100%;
      }
      th, td {
        padding: 10px;
        border-bottom: 1px solid #dadada;
        text-align: center;
      }
    </style>
  </head>
  <body>
    <table>
      <thead>
        <tr>
          <th>emp_no</th>
          <th>first_name</th>
          <th>last_name</th>
          <th>hire_date</th>
          <th>Edit</th>
          <th>Delete</th>
        </tr>
      </thead>
      <tbody>
        <?php
          $jb_conn = mysqli_connect( 'localhost', 'username', 'password', 'employees' );
          $delete_emp_no = $_POST[ 'delete_emp_no' ];
          if ( isset( $delete_emp_no ) ) {
            $jb_sql_delete = "DELETE FROM employees WHERE emp_no = '$delete_emp_no';";
            mysqli_query( $jb_conn, $jb_sql_delete );
            echo '<p style="color: red;">Employee ' . $delete_emp_no . ' is deleted.</p>';
          }
          $jb_sql = "SELECT * FROM employees LIMIT 5;";
          $jb_result = mysqli_query( $jb_conn, $jb_sql );
          while( $jb_row = mysqli_fetch_array( $jb_result ) ) {
            $jb_edit = '
              <form action="employees_edit.php" method="POST">
                <input type="hidden" name="edit_emp_no" value="' . $jb_row[ 'emp_no' ] . '">
                <input type="submit" value="Edit">
              </form>
            ';
            $jb_delete = '
              <form action="employees.php" method="POST">
                <input type="hidden" name="delete_emp_no" value="' . $jb_row[ 'emp_no' ] . '">
                <input type="submit" value="Delete">
              </form>
            ';
            echo '<tr><td>' . $jb_row[ 'emp_no' ] . '</td><td>'. $jb_row[ 'first_name' ] . '</td><td>' . $jb_row[ 'last_name' ] . '</td><td>' . $jb_row[ 'hire_date' ] . '</td><td>' . $jb_edit . '</td><td>' . $jb_delete . '</td></tr>';
          }
        ?>
      </tbody>
    </table>
  </body>
</html>

employees_edit.php

  • employees.php에서 Edit 버튼을 클릭했을 때 이동하는 페이지입니다.
  • emp_no 값을 받습니다.
$edit_emp_no = $_POST[ 'edit_emp_no' ];
  • 데이터베이스에 연결합니다.
$jb_conn = mysqli_connect( 'localhost', 'username', 'jb0923', 'password' );
  • emp_no에 해당하는 직원의 정보를 가져옵니다.
$jb_sql_edit = "SELECT * FROM employees WHERE emp_no = $edit_emp_no;";
  • 가져온 직원의 정보를 기본값으로 하는 폼을 만듭니다.
<form action="employees_update.php" method="POST">
  <input type="hidden" name="emp_no" value="<?php echo $jb_row[ 'emp_no' ]; ?>">
  <p>NO <?php echo $jb_row[ 'emp_no' ]; ?></p>
  <p>Birth Date <input type="date" name="birth_date" value="<?php echo $jb_row[ 'birth_date' ]; ?>" required></p>
  <p>First Name <input type="text" name="first_name" value="<?php echo $jb_row[ 'first_name' ]; ?>" required></p>
  <p>Last Name <input type="text" name="last_name" value="<?php echo $jb_row[ 'last_name' ]; ?>" required></p>
  <p>Gender <select name="gender" required>
    <option value="M" <?php if ( $jb_row[ 'gender' ] == M ) { echo 'selected'; } ?>>M</option>
    <option value="F" <?php if ( $jb_row[ 'gender' ] == F ) { echo 'selected'; } ?>>F</option>
  </select></p>
  <p>Hire Date <input type="date" name="hire_date" value="<?php echo $jb_row[ 'hire_date' ]; ?>" required></p>
  <button>Edit</button>
</form>
  • 전체 코드는 다음과 같습니다.
<?php
  $edit_emp_no = $_POST[ 'edit_emp_no' ];
  $jb_conn = mysqli_connect( 'localhost', 'username', 'password', 'employees' );
  $jb_sql_edit = "SELECT * FROM employees WHERE emp_no = $edit_emp_no;";
  $jb_result = mysqli_query( $jb_conn, $jb_sql_edit );
  $jb_row = mysqli_fetch_array( $jb_result );
?>

<!doctype html>
<html lang="ko">
  <head>
    <meta charset="utf-8">
    <title>Edit Employee</title>
    <style>
      body {
        font-family: Consolas, monospace;
        font-family: 12px;
      }
    </style>
  </head>
  <body>
    <h1>Edit Employee</h1>
    <form action="employees_update.php" method="POST">
      <input type="hidden" name="emp_no" value="<?php echo $jb_row[ 'emp_no' ]; ?>">
      <p>NO <?php echo $jb_row[ 'emp_no' ]; ?></p>
      <p>Birth Date <input type="date" name="birth_date" value="<?php echo $jb_row[ 'birth_date' ]; ?>" required></p>
      <p>First Name <input type="text" name="first_name" value="<?php echo $jb_row[ 'first_name' ]; ?>" required></p>
      <p>Last Name <input type="text" name="last_name" value="<?php echo $jb_row[ 'last_name' ]; ?>" required></p>
      <p>Gender <select name="gender" required>
        <option value="M" <?php if ( $jb_row[ 'gender' ] == M ) { echo 'selected'; } ?>>M</option>
        <option value="F" <?php if ( $jb_row[ 'gender' ] == F ) { echo 'selected'; } ?>>F</option>
      </select></p>
      <p>Hire Date <input type="date" name="hire_date" value="<?php echo $jb_row[ 'hire_date' ]; ?>" required></p>
      <button>Edit</button>
    </form>
  </body>
</html>

employees_update.php

  • employees_edit.php에서 값을 수정하고 Edit 버튼을 클릭했을 때 이동하는 페이지입니다.
  • UPDATE 문으로 값을 수정합니다.
<!doctype html>
<html lang="ko">
  <head>
    <meta charset="utf-8">
    <title>Update Employee</title>
    <style>
      body {
        font-family: Consolas, monospace;
        font-family: 12px;
      }
    </style>
  </head>
  <body>
    <?php
      $emp_no = $_POST[ 'emp_no' ];
      $birth_date = $_POST[ 'birth_date' ];
      $first_name = $_POST[ 'first_name' ];
      $last_name = $_POST[ 'last_name' ];
      $gender = $_POST[ 'gender' ];
      $hire_date = $_POST[ 'hire_date' ];
      if ( is_null( $emp_no ) ) {
        echo '<h1>Fail!</h1>';
      } else {
        $jb_conn = mysqli_connect( 'localhost', 'username', 'password', 'employees' );
        $jb_sql = "UPDATE employees SET birth_date = '$birth_date', first_name = '$first_name', last_name = '$last_name', gender = '$gender', hire_date = '$hire_date' WHERE emp_no = $emp_no;";
        mysqli_query( $jb_conn, $jb_sql );
        echo '<h1>Success!</h1>';
      }
    ?>
    <p>
      <a href="employees.php">Employees Lists</a>
      <a href="employees_add.php">Add Employee</a>
     </p>
  </body>
</html>

PHP 강좌