Q&A
>この情報がわかれば答えられるかも
分かるかどうかは別としてSQLのことになるのでテーブル定義は必須と思います。
手元で試そうにも定義がないと検証できませんから。
CREATE文で提示してください。可能ならサンプルデータもINSERTで。
前提・実現したいこと
PHPでいいね機能を作っています。データベースと連携し、ハートマークをクリックして”いいね”の付け外しまでは既にできていますが、全投稿を取得し”いいね”が付いた投稿にはTwitterのようにハートマーク横に数字を表示する段階で躓いています。
Qiitaのこちらの記事を参考に作っていますが、投稿取得のSQLの書き方でうまくいっていないようです。ソースコードの方にその比較を書いています。
members,posts,likesの３つのテーブルを使っています。
該当のソースコード
<?php session_start(); require('dbconnect.php'); require('function.php'); // 1,これでいいねが付いてる投稿だけ取得 $posts = $db->prepare('SELECT m.name, m.picture, p.*, COUNT(l.post_id) AS like_cnt FROM members m, posts p, likes l WHERE m.id=p.member_id AND p.id=l.post_id GROUP BY l.post_id ORDER BY p.created DESC LIMIT ?, 5'); // 2,これで何も1件も取得できない Qiitaにあったもの $posts = $db->prepare('SELECT m.name, m.picture, p.*, COUNT(l.post_id) AS like_cnt FROM members m, posts p LEFT JOIN likes l ON p.id=l.post_id WHERE m.id=p.member_id GROUP BY l.post_id ORDER BY p.created DESC LIMIT ?, 5'); // 8/27追記 ↑このGROUP BY をp.idにしたところ”いいね”有り無しの全投稿取得できました!! // 3,これが元のSQL 全件取得できる $posts = $db->prepare('SELECT m.name, m.picture, p.* FROM members m, posts p WHERE m.id=p.member_id ORDER BY p.created DESC LIMIT ?,5'); $posts->bindParam(1, $start, PDO::PARAM_INT); $posts->execute(); //1-2. いいねボタン if (isset($_REQUEST['like'])) { //いいねを押したメッセージの投稿者を調べる $contributor = $db->prepare('SELECT member_id FROM posts WHERE id=?'); $contributor->execute(array($_REQUEST['like'])); $pressed_message = $contributor->fetch(); //1-3. いいねを押した人とメッセージ投稿者が同一人物でないか確認 if ($_SESSION['id'] != $pressed_message['member_id']) { //1-4. 過去にいいね済みであるか確認 $pressed = $db->prepare('SELECT COUNT(*) AS cnt FROM likes WHERE post_id=? AND member_id=?'); $pressed->execute(array( $_REQUEST['like'], $_SESSION['id'] )); $my_like_cnt = $pressed->fetch(); //1-5. いいねのデータを挿入or削除 if ($my_like_cnt['cnt'] < 1) { $press = $db->prepare('INSERT INTO likes SET post_id=?, member_id=?, created=NOW()'); $press->execute(array( $_REQUEST['like'], $_SESSION['id'] )); header("Location: index.php?page={$page}"); exit(); } else { $cancel = $db->prepare('DELETE FROM likes WHERE post_id=? AND member_id=?'); $cancel->execute(array( $_REQUEST['like'], $login_user )); header("Location: index.php?page={$page}"); exit(); } } } ?>
<body> <div id="wrap"> <div id="head"> <h1><a href="index.php">PHPで簡単SNS</a></h1> </div> <div id="content"> <div class="top"> <form action="" method="post"> <dl> <dt><?php echo h($member['name']); ?>さん、メッセージをどうぞ</dt> <dd> <textarea name="message" cols="50" rows="5"><?php echo h($message); ?></textarea> <input type="hidden" name="reply_post_id" value="<?php echo h($_REQUEST['res']); ?>" /> </dd> </dl> <div> <p> <input type="submit" class="btn-border" value="投稿する" /> </p> </div> </form> <div> <a href="logout.php" class="btn-border">ログアウト</a> </div> </div> <?php foreach ($posts as $post) : ?> <div class="msg"> <div class="msg_left"> <img src="member_picture/<?php echo h($post['picture']); ?>" width="48" height="48" alt="<?php echo h($post['name']); ?>" /> </div> <div class="msg_right"> <div class="msg_right_head"> <div class="name"><a href="profile.php?id=<?php echo h($post['member_id']); ?>"><?php echo h($post['name']); ?></a></div> <div class="day"><a href="view.php?id=<?php echo h($post['id']); ?>"><?php echo h($post['created']); ?></a></div> </div> <div class="msg_body"> <?php echo mb_strimwidth(htmlspecialchars($post['message']), 0, 150, '...', "UTF-8"); ?> </div> <div class="msg_footer"> <!-- いいね機能をここに書いています --> <?php //2-1. ログインしている人がいいねしたメッセージをすべて取得 この2つ「$like = ~~」と「$my_like_cnt ~~」を上の大枠phpからこのforeach文の中に入れたらハートマークに色が付いた $like = $db->prepare('SELECT post_id FROM likes WHERE member_id=?'); $like->execute(array($_SESSION['id'])); while ($like_record = $like->fetch()) { $my_like[] = $like_record; } $my_like_cnt = 0; if (!empty($my_like)) { foreach ($my_like as $like_post) { foreach ($like_post as $like_post_id) { if ($like_post_id == $post['id']) { $my_like_cnt = 1; } } } } <?php if ($my_like_cnt < 1) : ?> <a class="heart" href="index.php?like=<?php echo h($post['id']); ?>&page=<?php echo h($page); ?>">♡</a> <?php else : ?> <a class="heart red" href="index.php?like=<?php echo h($post['id']); ?>&page=<?php echo h($page); ?>">♥</a> <?php endif; ?> <span> <?php echo h($post['like_cnt']); ?> </span> [<a href="index.php?res=<?php echo h($post['id']); ?>">コメント</a>] <?php if ($_SESSION['id'] == $post['member_id']) : ?> [<a href="delete.php?id=<?php echo h($post['id']); ?>" style="color: #F33;">削除</a>] <?php endif; ?> <?php if ($post['reply_message_id'] > 0) : ?> [<a href="view.php?id=<?php echo h($post['reply_message_id']); ?>"> 返信元のメッセージ</a>] <?php endif; ?> </div> </div> </div> <?php endforeach; ?> -- ここにページネーション </div> </div> </body> </html>
今回の質問にはおそらく影響ないPHP部分 // メッセージを登録 if (!empty($_POST)) { if ($_POST['message'] !== '') { if ($_POST['reply_post_id'] == '') { $_POST['reply_post_id'] = 0; } $message = $db->prepare('INSERT INTO posts SET member_id=?, message=?, reply_message_id=?, created=NOW()'); $message->execute(array( $member['id'], $_POST['message'], $_POST['reply_post_id'] )); // もう一度このページにジャンプすることで$_POSTを空にする header('Location: index.php'); exit(); } } // 投稿を取得 ページネーションも $page = $_REQUEST['page']; if ($page == '') { $page = 1; } $page = max($page, 1); $counts = $db->query('SELECT COUNT(*) AS cnt FROM posts'); $cnt = $counts->fetch(); $maxPage = ceil($cnt['cnt'] / 5); $page = min($page, $maxPage); $start = ($page - 1) * 5;
サンプルデータ CREATE TABLE `likes` ( `id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `member_id` int(11) NOT NULL, `created` datetime NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `likes` (`id`, `post_id`, `member_id`, `created`, `modified`) VALUES (11, 19, 1, '2021-08-24 23:51:11', '2021-08-24 14:51:11'), (15, 14, 1, '2021-08-25 09:32:31', '2021-08-25 00:32:31'), CREATE TABLE `members` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `password` varchar(100) NOT NULL, `picture` varchar(255) NOT NULL, `introduce` varchar(200) NOT NULL, `created` datetime NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `members` (`id`, `name`, `email`, `password`, `picture`, `introduce`, `created`, `modified`) VALUES (1, 'sakura', 'sakura-example@gmail.com', '5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', '20210823233156atom.jpg', '初めまして、sakuraです。プログラミング勉強中です。自己紹介文をupdate.phpから編集できるようになりました。日々なんとか成長中', '2021-08-17 13:54:57', '2021-08-23 14:31:56'), (2, 'wakaba', 'wakaba-example@yahoo.ne.jp', '5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', '20210821103031animals.jpg', 'どうも、神奈川の大学通ってる学生です。趣味は登山です。', '2021-08-17 21:41:43', '2021-08-21 01:30:31'), CREATE TABLE `posts` ( `id` int(11) NOT NULL, `message` text NOT NULL, `member_id` int(11) NOT NULL, `reply_message_id` int(11) NOT NULL DEFAULT '0', `created` datetime NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `posts` (`id`, `message`, `member_id`, `reply_message_id`, `created`, `modified`) VALUES (3, '一覧を作りました。', 1, 0, '2021-08-17 16:30:47', '2021-08-17 07:30:47'), (4, '初めまして、よろしくお願いします。', 2, 0, '2021-08-17 21:42:13', '2021-08-17 12:42:13'), (5, '返信機能のため、投稿', 2, 0, '2021-08-17 21:42:31', '2021-08-17 12:42:31'), (6, 'もう一つ増やします', 2, 0, '2021-08-17 21:42:46', '2021-08-17 12:42:46'),
試したこと
ソースコードの方にも書いていますが、Qiita記事のSQL文ではどの投稿も取得できず、いいね機能を付ける前の元のSQLでは投稿全件取得できています。
QiitaにあったSQL文を試しに「LEFT JOIN」を「RIGHT JOIN」に変更したところ、”いいね”した投稿だけ取得できハートマークの横に数字の「1」も入りましたので、外部結合は出来たようです。ただ RIGHT で likesテーブル を優先したので”いいね”が付いていない投稿は取得されなかった、という理解です。
「どのコードで何をしようとしているか」は把握しているつもりです。やはりSQL文に問題があるのでしょうか？
8/26 追記
「$post['id']はforeach内かな」と思い移動したところハートマークに色付きましたが、まだSQL文が間違っているのか”いいね”有り無しの全件取得はできていません。
8/27 追記
全件取得は解決しました(コード参照)、ハートマークに色も付いて横にいいね数も表示できました。とりあえずは解決です、ありがとうございました。
補足情報（FW/ツールのバージョンなど）
PHP7.4.1 MAMP PHPMyAdmin Chromeブラウザ VSCode を使っています。
回答ありがとうございます。「CREATE文で提示」がやったことないので合っているか不安ですが、MySQLで「SHOW CREATE TABLE テーブル名」で実行したところテーブル定義と思われるCREATE文が取得できましたので、添付します。
構造は質問文のリンクのQiita記事にあるテーブル定義と似ているものでした。
データもお見せしたかったのですが、こちらはコマンドがよくわからず申し訳ありません。
何かわかりましたら、回答よろしくお願いします。
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(100) NOT NULL,
`picture` varchar(255) NOT NULL,
`introduce` varchar(200) NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message` text NOT NULL,
`member_id` int(11) NOT NULL,
`reply_message_id` int(11) NOT NULL DEFAULT '0',
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4
CREATE TABLE `likes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`post_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4
createは合ってます。
ただ、質問本文にコードとして追記してください。
サンプルデータについては「データダンプ」で調べれば出てくると思います。
phpMyAdminなどのツールを使われているならエクスポート機能があるので活用を。
難しければコマンドでselectした結果を画面キャプチャか質問本文にマークダウンでテーブル組んでも良いです。
PHPMyAdminから「エクスポート」でコードを質問本文に張り付けようとしたのですが、「10000字超えは送れない」ようなので、SELECT文の画面を張りました。
遅くなって申し訳ありません。
あぁなるほど。こちらも言葉足らずでした。「サンプル」なので全部なくても良いですよ。
データベースそれぞれのテーブルの一部を追加しました。本文10000字に入れるため「投稿への返信機能」の記述は削りました。
回答1件
0
自己解決
$posts = $db->prepare('SELECT m.name, m.picture, p.*, COUNT(l.post_id) AS like_cnt FROM members m, posts p LEFT JOIN likes l ON p.id=l.post_id WHERE m.id=p.member_id GROUP BY l.post_id ORDER BY p.created DESC LIMIT ?, 5');
Qiitaにあった↑SQL文のGROUP BY をp.idに変えたところ解決。”いいね”有り無し全ての投稿を取得表示できました。GROUP BYは「○○ごと」に集計なので「postsテーブルのidごとに」が正しかったようです。
投稿2021/08/28 00:28
総合スコア1
あなたの回答
tips
プレビュー
