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', 'password', 'employees' );
- 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>