[PHP & MySQL] 테이블 간의 연결

새로운 테이블(author)을 생성하고 기존 테이블(topic)과 연결하기

1. author 테이블 생성하기

MariaDB [opentutorials]> CREATE TABLE author(
    -> id INT(11) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(30) NOT NULL,
    -> profile VARCHAR(200) NULL,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.046 sec)

MariaDB [opentutorials]> DESC author;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30)  | NO   |     | NULL    |                |
| profile | varchar(200) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.026 sec)

MariaDB [opentutorials]> INSERT INTO author(name, profile) VALUES('Regulus', 'developer');
Query OK, 1 row affected (0.005 sec)

MariaDB [opentutorials]> INSERT INTO author(name, profile) VALUES('Hermione', 'DBA');
Query OK, 1 row affected (0.005 sec)

MariaDB [opentutorials]> INSERT INTO author(name, profile) VALUES('Luna', 'data scientist');
Query OK, 1 row affected (0.005 sec)

MariaDB [opentutorials]> SELECT * FROM author;
+----+----------+----------------+
| id | name     | profile        |
+----+----------+----------------+
|  1 | Regulus  | developer      |
|  2 | Hermione | DBA            |
|  3 | Luna     | data scientist |
+----+----------+----------------+
3 rows in set (0.000 sec)

 

2. topic 테이블에 author_id 열 생성하기

테이블에 새로운 열 생성하기: ALTER TABLE [table명] ADD COLUMN [column명] [기타설정];

MariaDB [opentutorials]> SELECT * FROM topic;
+----+------------+-------------------+---------------------+
| id | title      | description       | created             |
+----+------------+-------------------+---------------------+
|  4 | MySQL      | MySQL is ...      | 2022-08-01 23:23:34 |
|  5 | Oracle     | Oracle is ...     | 2022-08-01 23:24:00 |
|  6 | SQL Server | SQL Server is ... | 2022-08-01 23:24:17 |
|  7 | MongoDB    | MongoDB is ...    | 2022-08-01 23:24:31 |
| 14 | MariaDB    | MariaDB is ...    | 2022-08-07 23:57:35 |
| 15 | OrientDB   | OrientDB is ...   | 2022-08-07 23:57:54 |
+----+------------+-------------------+---------------------+
6 rows in set (0.000 sec)

MariaDB [opentutorials]> ALTER TABLE topic ADD COLUMN author_id INT(11);
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [opentutorials]> SELECT * FROM topic;
+----+------------+-------------------+---------------------+-----------+
| id | title      | description       | created             | author_id |
+----+------------+-------------------+---------------------+-----------+
|  4 | MySQL      | MySQL is ...      | 2022-08-01 23:23:34 |      NULL |
|  5 | Oracle     | Oracle is ...     | 2022-08-01 23:24:00 |      NULL |
|  6 | SQL Server | SQL Server is ... | 2022-08-01 23:24:17 |      NULL |
|  7 | MongoDB    | MongoDB is ...    | 2022-08-01 23:24:31 |      NULL |
| 14 | MariaDB    | MariaDB is ...    | 2022-08-07 23:57:35 |      NULL |
| 15 | OrientDB   | OrientDB is ...   | 2022-08-07 23:57:54 |      NULL |
+----+------------+-------------------+---------------------+-----------+
6 rows in set (0.001 sec)

MariaDB [opentutorials]> UPDATE topic SET author_id = 1 WHERE id = 4;
Query OK, 1 row affected (0.006 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(생략)

MariaDB [opentutorials]> UPDATE topic SET author_id = 1 WHERE id = 15;
Query OK, 1 row affected (0.005 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [opentutorials]> SELECT * FROM topic;
+----+------------+-------------------+---------------------+-----------+
| id | title      | description       | created             | author_id |
+----+------------+-------------------+---------------------+-----------+
|  4 | MySQL      | MySQL is ...      | 2022-08-01 23:23:34 |         1 |
|  5 | Oracle     | Oracle is ...     | 2022-08-01 23:24:00 |         1 |
|  6 | SQL Server | SQL Server is ... | 2022-08-01 23:24:17 |         2 |
|  7 | MongoDB    | MongoDB is ...    | 2022-08-01 23:24:31 |         2 |
| 14 | MariaDB    | MariaDB is ...    | 2022-08-07 23:57:35 |         3 |
| 15 | OrientDB   | OrientDB is ...   | 2022-08-07 23:57:54 |         1 |
+----+------------+-------------------+---------------------+-----------+
6 rows in set (0.000 sec)

 

3. topic 테이블의 author_id 값과 author 테이블의 id 값이 같은 것을 기준으로 두 테이블을 SELECT하기

MariaDB [opentutorials]> SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+-----------+------+----------+----------------+
| id | title      | description       | created             | author_id | id   | name     | profile        |
+----+------------+-------------------+---------------------+-----------+------+----------+----------------+
|  4 | MySQL      | MySQL is ...      | 2022-08-01 23:23:34 |         1 |    1 | Regulus  | developer      |
|  5 | Oracle     | Oracle is ...     | 2022-08-01 23:24:00 |         1 |    1 | Regulus  | developer      |
|  6 | SQL Server | SQL Server is ... | 2022-08-01 23:24:17 |         2 |    2 | Hermione | DBA            |
|  7 | MongoDB    | MongoDB is ...    | 2022-08-01 23:24:31 |         2 |    2 | Hermione | DBA            |
| 14 | MariaDB    | MariaDB is ...    | 2022-08-07 23:57:35 |         3 |    3 | Luna     | data scientist |
| 15 | OrientDB   | OrientDB is ...   | 2022-08-07 23:57:54 |         1 |    1 | Regulus  | developer      |
+----+------------+-------------------+---------------------+-----------+------+----------+----------------+
6 rows in set (0.009 sec)

 

---

 

웹페이지에서 목차(title)에 해당하는 author.name 출력하기

1. index.php에 $author 변수 만들기

2. id값이 있을 때, sql문을 아래와 같이 바꾸기

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id WHERE topic.id={$filtered_id}

3. $article['name']에 $row['name'] 저장하기

4. $author 변수가 "by [이름]"을 출력하게 만들기

<!--htdocs\index.php-->
<?php
//title을 목차로 만들기
$conn = mysqli_connect('localhost', 'root', '021103', 'opentutorials');
$sql = "SELECT * FROM topic";
$result = mysqli_query($conn, $sql);
$list = '';
while($row = mysqli_fetch_array($result)){
  $escaped_title = htmlspecialchars($row['title']);
  $list = $list."<li><a href=\"index.php?id={$row['id']}\">{$escaped_title}</a></li>";
}
//기본적인 상태
$article = array(
  'title'=>'Welcome',
  'description'=>'Hello, WEB'
);

$update_link = '';
$delete_link = '';
$author = '';

//id값이 있을 때, 즉 목차를 클릭했을 때
if(isset($_GET['id'])){
  $filtered_id = mysqli_real_escape_string($conn, $_GET['id']);
  $sql = "SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id WHERE topic.id={$filtered_id}";

  $result = mysqli_query($conn, $sql);
  $row = mysqli_fetch_array($result);
  $article['title'] = htmlspecialchars($row['title']);
  $article['description'] = htmlspecialchars($row['description']);
  $article['name'] = htmlspecialchars($row['name']);

  $update_link = '<a href="update.php?id='.$_GET['id'].'">update</a>';
  $delete_link = '
    <form action="process_delete.php" method="post">
      <input type="hidden" name="id" value="'.$_GET['id'].'">
      <input type="submit" value="delete">
    </form>
  ';
  $author = '<p>by '.$article['name'].'</p>';
}
?>

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>WEB</title>
  </head>
  <body>
    <h1><a href="index.php">WEB</a></h1>
    <ol>
      <?=$list?>
    </ol>
    <a href="create.php">create</a> <!--글쓰기 기능 링크-->
    <?=$update_link?> <!--수정하기 기능 링크-->
    <?=$delete_link?> <!--삭제하기 기능 링크-->
    <h2><?=$article['title']?></h2>
    <?=$article['description']?>
    <?=$author?> <!--author-->
  </body>
</html>

 

 

---

 

Create할 때, author name을 선택하게 만들고, DB에 반영하기

1. create.php에 <select> 태그로 author name을 선택하는 항목 만들기

2. 선택한 author name은 author_id라는 이름으로 process_create.php에 전송하기

<!--htdocs\create.php-->
<?php
//title을 목차로 만들기
$conn = mysqli_connect('localhost', 'root', '021103', 'opentutorials');
$sql = "SELECT * FROM topic";
$result = mysqli_query($conn, $sql);
$list = '';
while($row = mysqli_fetch_array($result)){
  $list = $list."<li><a href=\"index.php?id={$row['id']}\">{$row['title']}</a></li>";
}

//author를 선택하는 항목
$sql = "SELECT * FROM author";
$result = mysqli_query($conn, $sql);
$select_form = '<select name="author_id">'; //process_create.php에 author_id라는 이름으로 데이터 전송
while($row = mysqli_fetch_array($result)){
  $select_form = $select_form.'<option value="'.$row['id'].'">'.$row['name'].'</option>';
}
$select_form = $select_form.'</select>';
?>

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>WEB</title>
  </head>
  <body>
    <h1><a href="index.php">WEB</a></h1>
    <ol>
      <?=$list?>
    </ol>
    <h2>Create</h2>
    <form action="process_create.php" method="post">
      <p><input type="text" name="title" placeholder="title"></p>
      <p><textarea name="description" placeholder="description"></textarea></p>
      <?=$select_form?> <!--author 선택-->
      <p><input type="submit"></p>
    </form>
  </body>
</html>

 

3. process_create.php에서 author_id 값을 받고, DB에 sql문으로 전달하기

<!--htdoc\process_create.php-->
<?php
$conn = mysqli_connect('localhost', 'root', '021103', 'opentutorials');
$filtered = array(
  'title'=>mysqli_real_escape_string($conn, $_POST['title']),
  'description'=>mysqli_real_escape_string($conn, $_POST['description']),
  'author_id'=>mysqli_real_escape_string($conn, $_POST['author_id'])
);

$sql="
  INSERT INTO topic(title, description, created, author_id)
  VALUES('{$filtered['title']}', '{$filtered['description']}', NOW(), '{$filtered['author_id']}')
";
$result = mysqli_query($conn, $sql);

if($result === false){
  echo "ERROR";
  error_log(mysqli_query($conn));
}
else {
  echo "성공했습니다.\n<a href=\"index.php\">BACK TO PAGE</a>";
}
?>

 

4. 실습

MariaDB [opentutorials]> SELECT * FROM topic;
+----+---------------------+-------------------------+---------------------+-----------+
| id | title               | description             | created             | author_id |
+----+---------------------+-------------------------+---------------------+-----------+
|  4 | MySQL               | MySQL is ...            | 2022-08-01 23:23:34 |         1 |
|  5 | Oracle              | Oracle is ...           | 2022-08-01 23:24:00 |         1 |
|  6 | SQL Server          | SQL Server is ...       | 2022-08-01 23:24:17 |         2 |
|  7 | MongoDB             | MongoDB is ...          | 2022-08-01 23:24:31 |         2 |
| 14 | MariaDB             | MariaDB is ...          | 2022-08-07 23:57:35 |         3 |
| 15 | OrientDB            | OrientDB is ...         | 2022-08-07 23:57:54 |         1 |
| 17 | Fantastic Creatures | and you cannot see them | 2022-08-08 17:09:54 |         3 |
+----+---------------------+-------------------------+---------------------+-----------+
7 rows in set (0.001 sec)