テーブルAにデータを格納した時に自動付与されるIDをテーブルBのカラムにしたい。

Question

前提

MysqlをPHPからいじりたいのですが、外部に公開されていない事とアナリティクスでアクセス状態やPDFのダウンロード状況を把握するためセッションにアカウント名を入れてGETで動かしています。

データベースには、お知らせというテーブルがあり、その中にはIDとタイトルとお知らせ内容があります。
IDはAUTO_INCREMENTで自動で振っています。

|ID|タイトル|内容|
|:3|:---○--:|---:|
|:2|:---○--:|---:|
|:1|:-------:|---:|

実現したいこと

お知らせが作成された時点で別テーブルのカラムをお知らせIDとして増やし、ユーザーがお知らせを読んでお知らせを読みましたと言うボタンをクリックしたら、カラム読んだというサインとして下記の様に○が入るようにしたいと思います。

|ユーザー|ID1|ID2|ID3|ID4|
|:hoge1 |:○:|:○:|:○:|--:|
|:hoge2 |:○:|:--:|:○:|--:|
|:hoge3 |:--:|:○:|:○:|--:|

該当のソースコード

ニュース.php
$stmt = $mysqli->prepare("INSERT INTO topics (news, info) VALUES (?, ?, ?)");
$stmt = $mysqli->prepare("ALTER TABLE 別テーブル ADD '.$id.' INT(4) NULL AFTER 'ユーザー'");
$stmt->bind_param('ss', $_POST["news"], $_POST["info"]);
if($stmt->execute())
if($_SERVER['REQUEST_METHOD'] === 'POST') {
$url = $_SERVER["REQUEST_URI"];
header("Location: topicall.php");

<form method="POST" action="別テーブル.php"> <input type="hidden" name="account" value="<?php echo htmlspecialchars($_SESSION["account"], ENT_QUOTES); ?>"> <input type="hidden" name="<?php echo $nid; ?>" value="○"> <input type="submit" class="primary" value="お知らせを確認しました！"> </form>

で、お知らせテーブルは出来ているのですが、カラムを増やす事ができません。

当方PHPとMysqlをまっさらから始めて一週間ほどで、根本的な間違いやセキュリティーの問題があるだろうと言う事は分かっています。

良い手があればと思うのですが、どなたかご教授頂けると幸いです。

Answer 1

テーブルのレイアウトをオンラインで変更するようなことはやっちゃ駄目です。
行として追加するようにしましょう。

見た目で横に展開したいような場合は、GROUP_CONCAT()が使用できます。

MySQLのGROUP_CONCATがアツい
他にも参考になる記事はありますので、検索してみてください。

Comments

[sazi]

幾つか回答が寄せられたので、混乱しないようにコメントしておきます。
私の回答で、
>行として追加するようにしましょう。
部分の具体的なものが、luckerさんの回答
>GROUP_CONCAT()
を使用しない方法が yambejpさんの2番目のSQLです。
※カラムを増やさないというのは全員同じです。

[yas_usa]

何故オンラインで使ってはいけないのか分かりませんが、
こちらもGROUP_CONCAT()も参考にさせていただきます。

ありがとうございました！

[sazi]

オンラインで使っちゃいけないのは、照会や更新などとのタイミングが取りずらく、場合によってはデータ破損となる可能性があるからです。
敢えてそんな危険は冒さない方が良いという事です。

[yas_usa]

そのようなリスクがあるためという事なんですね。
無知の為、理由も含めてご教授頂けるのは非常にありがたいです！

ありがとうございます。

Answer 2

とにかくデータの持ち方が悪いです。
まずはuserとdocを分けて、user_docという中間テーブルを持ちます

create table user(uid int unique,uname varchar(30));
insert into user values(1,'hoge1'),(2,'hoge2'),(3,'hoge3');
create table doc(did int unique,dname varchar(30));
insert into doc values(1,'fuga1'),(2,'fuga2'),(3,'fuga3');
create table user_doc(uid int,did int,unique(uid,did));
insert into user_doc values(1,1),(1,2),(1,3),(2,1),(2,3),(3,2),(3,3);

その上で、userテーブルとuser_docテーブルを利用して表をつくります

select t1.uid
,case sum(t1.did=1) when 0 then '' when 1 then '○' end as ID1
,case sum(t1.did=2) when 0 then '' when 1 then '○' end as ID2
,case sum(t1.did=3) when 0 then '' when 1 then '○' end as ID3
,case sum(t1.did=4) when 0 then '' when 1 then '○' end as ID4
from user_doc as t1
left join user as t2 on t1.uid=t2.uid
group by uid;

横方向へIDを自動的に増やしていくのにはプロシージャというもので処理します
今回は煩雑にナリそうなので、上記まで理解することをおすすめします

プロシージャまで

docにfuga4を追記した上でリストを自動でつくります

• 元データ

create table user(uid int unique,uname varchar(30));
insert into user values(1,'hoge1'),(2,'hoge2'),(3,'hoge3');
create table doc(did int unique,dname varchar(30));
insert into doc values(1,'fuga1'),(2,'fuga2'),(3,'fuga3'),(4,'fuga4');
create table user_doc(uid int,did int,unique(uid,did));
insert into user_doc values(1,1),(1,2),(1,3),(2,1),(2,3),(3,2),(3,3);

• プロシージャ作成（1回だけ実効する）

drop procedure if exists myproc;
delimiter //
create procedure myproc()
begin
declare a int;
declare b varchar(100);
declare done int default 0;
declare cur cursor for
select did,dname from doc order by did;
declare continue handler for sqlstate '02000' set done=1;
set @sql='select t3.uname,t2.* from (select t1.uid ';
open cur;
repeat
fetch cur into a,b;
if not done then
set @sql=concat (@sql,',case sum(t1.did=',a,') when 0 then "" when 1 then "○" end as ',b,' ');
end if;
until done end repeat;
close cur;
set @sql=concat (@sql,'from user_doc as t1 ');
set @sql=concat (@sql,'group by uid ) as t2 ');
set @sql=concat (@sql,'inner join user as t3 on t2.uid=t3.uid ');
prepare stmt from @sql;
execute stmt;
end
//
delimiter ;

• 実行

call myproc;

Comments

[yambejp]

せっかくなのでプロシージャのsampleも付けておきます

[sazi]

えー、group_concat()推し、じゃなかったんですか。

[yambejp]

group_concat()推しですよ。
でもさきにsaziさんが書いてたのでプロシージャにしてみました

[sazi]

えー、group_concat()の結果をカラム分割してくれるストアドだったら良かったのに。

[yas_usa]

具体的なコードを出していただけたのでBAにさせていただきます。

色々テストしながら試させていただきます。
ありがとうございました！

Answer 3

カラムは alter table で増やせるんですが、
https://www.dbonline.jp/mysql/table/index20.html

そういう事は、是非、止めましょう。

###既読テーブル

ユーザー*	ID*
hoge1	1
hoge1	3
hoge2	2
hoge4	1
hoge4	4

（* プライマリキー）

こんなテーブルを作って、既読の情報だけ格納します。
読みだすときはユーザーをキーとして、お知らせテーブルと既読テーブルをJOINします。

ポイントは、ユーザーとIDに対して複合プライマリキーを指定する事です。

Comments

[yas_usa]

JOINまで分かっていないのですが、さらに精進します。

概念のヒントになりました、ありがとうございました！