20200929のMySQLに関する記事は6件です。

MySQL PHP ログインなし掲示板の内容に変更削除返信機能を付けていく TOP画面

テーブルに投稿日時順に出力し最初の投稿を先に出力する
返信内容があったら間に出力するこれも日付け順

top.php
<!DOCTYPE html>
<html lang="ja">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>投稿フォーム</title>
</head>

<body>
  <form action="comment_insert_done.php" method="post">
    <input type="text" name="comment">
    <input type="submit" value="送信">
  </form>
</body>

</html>
<?php

require_once("func/header.php");

define('DB_HOST', 'localhost');
define('DB_NAME', 'name_db');
define('DB_USER', 'user');
define('DB_PASSWORD', 'pass');
// 文字化け対策
// $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET 'utf8'");
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
$pdo = new PDO('mysql:host=localhost;dbname=name_db;', 'user', 'pass',  array(
  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
));
echo "<table class='table'>
<thead class='thead-light'>
  <tr>
  <th>コメント</th>
  <th>投稿日時</th>
  <th></th>
  <th></th>
  <th></th>
  </tr>
</thead>
";
$result = $pdo->query('SELECT * FROM t_text WHERE reply_parent_id = 0 AND deleteflg = 0 AND comment IS NOT NULL  ORDER BY datetime DESC');
// print_r($result);
var_dump($pdo->errorCode());
var_dump($pdo->errorInfo());

foreach ($result as $row) {
  // print_r($row);
  echo "<tr>";
  echo "<td>" . $row['comment'] . "</td>";
  echo "<td>" . $row['datetime'] . "</td>";
  echo '<td>';
  echo '<form action="comment_change.php" method="post">';
  echo '  <input type="hidden" value="' . $row['text_id'] . '" name= "text_id">';
  echo '  <input type="hidden" value="' . $row['comment'] . '" name= "comment">';
  echo '  <input type="submit" class="btn btn-success" value="変更" >';
  echo "</form>";
  echo "</td>";
  echo '<td>';
  echo '<form action="comment_delete_done.php" method="post" onClick="return confirm(\'削除しますか?\');">';
  echo '  <input type="hidden" value="' . $row['text_id'] . '" name= "text_id">';
  echo '<input type="submit" class="btn btn-danger" value="削除" >';
  echo "</form>";
  echo "</td>";
  echo '<td>';
  // print_r($row['text_id']);
  echo '<form action="reply.php" method="post" onClick="return confirm(\'返信しますか?\');">';
  echo '  <input type="hidden" value="' . $row['text_id'] . '" name= "text_id">';
  echo '  <input type="hidden" value="' . $row['reply_parent_id'] . '" name= "reply_parent_id">';
  echo '  <input type="hidden" value="' . $row['user_id'] . '" name= "user_id">';
  echo '<input type="hidden" value = "' . $row['comment'] . '" name= "comment">';
  echo '<input type="submit" class="btn  btn-secondary" value="返信" >';
  echo "</form>";
  echo "</td>";
  echo "</tr>";
  $reply_id = $row['text_id'];
  // $reply_id = $row['reply_parent_id'];
  $result2 = $pdo->query('SELECT * FROM t_text WHERE reply_parent_id = "' . $reply_id . '" AND deleteflg = 0 AND comment IS NOT NULL ORDER BY datetime DESC');

  foreach ($result2 as $row2) {
    // print_r($row2['text_id']);
    echo "<tr>";
    echo "<td>" . $row2['comment'] . "</td>";
    echo "<td>" . $row2['datetime'] . "</td>";
    echo '<td>';
    echo '<form action="comment_change.php" method="post">';
    echo '  <input type="hidden" value="' . $row2['text_id'] . '" name= "text_id">';
    echo '  <input type="hidden" value="' . $row2['comment'] . '" name= "comment">';
    echo '<input type="submit" class="btn btn-success" value="変更" >';
    echo "</form>";
    echo "</td>";
    echo '<td>';
    echo '<form action="comment_delete_done.php" method="post" onClick="return confirm(\'削除しますか?\');">';
    echo '  <input type="hidden" value="' . $row2['text_id'] . '" name= "text_id">';
    echo '<input type="submit" class="btn btn-danger" value="削除" >';
    echo "</form>";
    echo "</td>";
    echo '<td>';
    // print_r($row2['text_id']);
    echo '<form action="reply.php" method="post" onClick="return confirm(\'返信しますか?\');">';
    echo '  <input type="hidden" value="' . $row2['text_id'] . '" name= "text_id">';
    echo '  <input type="hidden" value="' . $row2['reply_parent_id'] . '" name= "reply_parent_id">';
    echo '  <input type="hidden" value="' . $row2['user_id'] . '" name= "user_id">';
    echo '<input type="hidden" value = "' . $row2['comment'] . '" name= "comment">';
    echo '<input type="submit" class="btn  btn-secondary" value="返信" >';
    echo "</form>";
    echo "</td>";
    echo "</tr>";
  }
}
echo '</table>';
?>

1,text_id
2,reply_parent_id
3,reply_id
4,user_id
5,comment
6,datetime
7,deleteflg
が振られている

text.csv
1,0,0,11,ないよう1,2020-09-24 07:09:07,0
2,0,0,11,ないよう2,2020-09-24 07:09:13,0
3,0,0,11,ないよう3,2020-09-24 07:09:21,0
4,0,0,11,ないよう4,2020-09-24 07:09:23,0
5,0,0,12,ないよう5,2020-09-24 07:09:32,1
6,0,0,12,ないよう6,2020-09-24 07:09:37,0
7,0,0,12,ないよう7,2020-09-24 07:09:45,0
8,7,7,12,7番目のuser3へ返信:ないよう8,2020-09-24 08:09:23,0
9,7,8,12,8番目のuser3へ返信:ないよう9,2020-09-24 09:09:13,0
10,6,6,11,6番目のuser3へ返信:へんしんないよう,2020-09-25 01:09:58,0
11,2,2,11,2番目のuser2へ返信:へんしんないよう2,2020-09-25 01:09:02,0
12,7,9,11,9番目のuser3へ返信:へんしんないよう3,2020-09-28 01:09:06,0
13,7,12,10,12番目のuser2へ返信:へんしんないよう4,2020-09-28 01:09:10,0

参考にしたサイト
PHP 5.3.6より前のバージョンの PDO MySQL で charset を指定する
https://qiita.com/ngyuki/items/d88a4df860abb51eb714

PDOを使ったPHPでのデータベース基本操作
https://qiita.com/mitsuru793/items/45b2452284e321c7a5a9

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQLでミリ秒のUNIX時間を日時に変更してタイムゾーンも変更

目的

MySQLに保存しているミリ秒のUNIX時間のデータを日時の書式に変更したい。
ついでにタイムゾーンも変更したい。

AppStore 領収書の Latest_receipt_info 内の expires_date_ms のようなデータなど。
※UNIXエポック(1970年1月1日午前0時0分0秒)からの経過ミリ秒で表現されている

想定テーブル

id user_id expires_date_ms
1 1 1601371107000
4 2 1601379940000

SQL例

SELECT 
  id, user_id,
  CONVERT_TZ(FROM_UNIXTIME(expires_date_ms / 1000, '%Y-%m-%d %H:%i:%s'), '+00:00', '+09:00') AS expires_date_ms
FROM
  hogehoges;

解説

項目 説明
expires_date_ms / 1000 ミリ秒を秒に変更
FROM_UNIXTIME(unix_timestamp, format) UNIX時間を所定の書式に変更する
CONVERT_TZ(dt, from_tz , to_tz) タイムゾーンを変更

その他

Latest_receipt_info 内にはちゃんと日時で表現されている expires_date がある。

参考

MySQLでUNIXタイムスタンプと日付を相互変換するメモ
MySQLでミリ秒をDATE型に変換するSQL文
レスポンスボディ.Latest _receipt _info

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

PHP(+MySQL)のランダム関数を使ってガチャを作る話

はじめに

ソーシャルアプリのガチャを作ったときの話。
無作為にデータを取り出すプログラムを書いたとき「なんだか偏るなぁ」と感じたので、
自分の中でどのやり方が課金者に平等で無作為(そして簡単に)にデータを取り出せるか調べてみたことを書きます。

使用している言語

PHP7.0.0、データベースはMySQL

そもそもMySQLでランダムにレコードを取得してはいけない?

SELECT * FROM 'gacha' ORDER BY RAND() LIMIT 1;
超簡単に取得できる方法ではありますが、SQLアンチパターンであるので却下しました。

PHPで取得したデータからランダムに取得

rand()mt_rand()

どちらも擬似乱数を整数で返してくれるもの。
mt_randの方が数倍高速だということですので使うならmt_randかなぁと思いましたが
暗号学的に安全ではないようなのです。

PHP7以降では暗号学的に安全と言われているrandom_int()が存在します。

PHP7以降で暗号化に使う場合であればこちら推奨されています。
引数を省略することはできませんが、基本的な使い方自体はmt_rand()と変わらないので置き換えも簡単にできるかと思います。

ではランダム関数使うならrandom_intを使いましょうという話なのか?

安全面を考えるとrandom_int()でいいと思います。
ただ、速度はmt_randの方が早そうなので、単純に乱数が欲しいだけならmt_randで十分だと思います。

stackoverflowに速度を検証された方がいたようです。
PHP Rand()vs. random_int()

結論

課金が絡むようなできる限り不具合なく安全で公平なシャッフルを望むのであれば多少速度が遅かろうがrandom_int()を使った方がいいと判断しましたので、PHP7以降であればrandom_int()、それ以下であればmt_rand()を使用するとします。

本題

ただ、プログラムで完璧な無作為を行うことは不可能なので関数を利用しつつ
仕組みでカバーすることにします。

つくるガチャについて(ここからは興味のある人だけお読みください)

ここで、どんなガチャがあるのか覚えている限り書きます(備忘録)

ランダム型
データベースから1件ランダムで排出するものを決定する一番作りやすいガチャです。
以前私が作っていたものもこの形で、データのレコード数を調整することで確率を収束させていました。
問題点は当たらない時はいくらお金をつぎ込んでも当たらない、という点です。

コンプリート型
例えば10種類のアイテムを全てガチャで引けたら今回の目玉のアイテムをもらえるといったものです。
欲しくもないアイテムをひたすら狙い続ける上にランダム型ベースなので確率も上がらない最も闇が深いガチャです。
現在は規制が入り、使えません

ボックス型
コンプリート型が規制されたことによってよく使われるようになったガチャです。
イメージは実物のガチャポンのように袋の中から一つずつ引いていき、いずれ空になるので欲しいものが確実に手に入るガチャです。

基本的に空になるともう課金されないため、売り上げの上限が決まってしまうという問題があったので
目玉のアイテムを複数集めるメリットを用意した上で、目玉のアイテムをユーザーが手に入れた段階でボックスをリセットできる機能をつけた派生系のものが生まれてきました。
いずれ100%欲しいものが欲しいだけ手に入る分コンプリート型よりは良心的になりました。

ステップアップ型
ガチャを引くとステップが上がるとともにオマケなどがもらえて、ステップが上がるほどにオマケやお得なアイテムが豪華になっていくガチャです。
また、頭打ちにならないように10ステップまで進むと1ステップに戻るものもあります。その場合は10ステップ目に目玉アイテムがある場合が多いです。
ボックス型よりも比較的早く目的のアイテムが確実に手に入るのでわかりやすいです。

テーブル型
ランダム性を完全に取り除いて最初からガチャで出る順番をデータに全て登録しておく、というものです。
これはかなり古いやりかたでデータをいい感じに登録する作業が面倒なのでやっているゲームはもうないかもしれません。

今回は単純に確率の話なので「ランダム型」でいきます。

以下のようなデータを用意します。
実際作ったものとは少々異なりますがイメージはこんな感じです。

gacha_item テーブル

id gacha_id rarity item_name min_ratio max_ratio
1 1 5 アイテムSSR 0 10
2 1 4 アイテムSR 11 100
3 1 4 アイテムSR 101 500
4 1 4 アイテムSR 501 1000
5 2 4 アイテムSR 0 200
6 2 3 アイテムR 201 500
7 2 3 アイテムR 501 1000
8 3 2 アイテムUC 0 250
9 3 2 アイテムUC 251 500
10 3 2 アイテムUC 501 750
11 3 2 アイテムUC 751 1000

フロー

やりたいこと。

  1. ユーザーがガチャを回す
  2. ガチャIDをランダムで取得
  3. ガチャIDの中からランダムで1件取得
  4. データベースから絞り込んだアイテムを取得
  5. ユーザーがアイテムをGET
$a = random_int(1, 3); // ガチャ&演出決定

$b = random_int(0, 1000); // ratio決定

$Item = getGachaItem($a,$b); // データベースから排出アイテム取得

// sqlはこんな感じ
SELECT * FROM 'gacha_item' WHERE 'gacha_id' = $a AND 'min_ratio' <= $b AND 'max_ratio' >= $b LIMIT 1;

一発でテーブルから1件取得するのではなく、ガチャIDを先にランダムで選ぶことで無作為性を上げています。

これで、簡単な仕組みのガチャができました。
あと必要なのはゲームバランス等を加味した排出率の計算をします。
データの設定ができたら後は10万回ほど回してみて排出率を出します。

データがでたらどれくらいのユーザーが目玉のアイテムを手に入れて欲しいかを考えます。
アクティブユーザー数や平均のガチャ回数を見たり、そのアイテムがゲームプレイに与える影響の大きさ(ゲームバランス)を鑑みて排出率を決定すればOKです。

余談:ガチャ演出について

gacha_idはガチャを入れ物で分けて無作為性を上げていますが「ガチャ演出の切り替え」に使う場合もあります。

レアリティによって演出を変えてもいいんですが、上位レアリティを増やすときでも演出を増やしやすいというメリットがあります。

例:
gacha_idが1の場合はrarityが高いものが入っているあたりガチャ。演出も派手。
gacha_idが2の場合は少しrarityが低いものが入っています。演出は少し派手。
gacha_idが3の場合はrarityが低いものが入っています。演出はノーマル。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【備忘録】EC2上のMySQLにMySQL Workbenchを接続する手順

MySQL Workbenchは便利、されどわかりにくい。
EC2へのSSH接続について、次やるときには忘れてるかもしれないのでメモします。

筆者の環境

  • Amazon Linux2
  • MySQL
  • RDSは不使用

手順

MySQL Workbenchを起動、スパナマークor+マークを押して設定画面を開く。

各設定内容

  • Connection Name:設定につける名前というだけなので、適当にわかりやすい名前をつける。
  • Connection Method:Standard TCP/IP over SSHを選択
  • SSH Hostname:<ElasticIP>:22という書式で、EC2インスタンスのIPアドレスと、SSHのポート番号を入力(セキュリティグループのインバウンドルールを参照)
  • SSH Username:ec2-user
  • SSH Key File:右側のを押して、秘密鍵を選択するor秘密鍵への絶対パスを直接書く
  • MySQL Hostname:localhost
  • MySQL Server Port:3306
  • Username:設定したユーザーネーム
  • Password:設定したパスワード

これで接続できました。
未来の自分もそうであってほしい。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQL 5.7で濁点・半濁点を含む半角カタカナをあいまい検索

TL; DR

照合順序を利用したLIKE検索の際に、濁点・半濁点を含む半角カタカナを区別せずに検索する方法を簡単にまとめました。

SELECT
    title
FROM
    articles
WHERE
    REPLACE(REPLACE(title, '゙', ''), '゚', '') COLLATE utf8mb4_unicode_ci LIKE CONCAT(
        '%',
        REPLACE(REPLACE(@keyword, '゙', ''), '゚', ''),
        '%'
    )
;

環境

  • MySQL 5.7

前提

簡単なあいまい検索を実装する際に、照合順序(COLLATION)を変更してLIKE検索をかけるという方法があります。
詳細は割愛しますが、MySQLでは照合順序に utf8_unicode_ci ( utf8mb4_unicode_ci )を指定することで、大文字・小文字、全角・半角、ひらがな・カタカナ、濁音・半濁音をすべて区別しないようになります。

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%かんたん%';
+--------------+
| title        |
+--------------+
| かんたん      |
| カンタン      |
| がんたん      |
| カンタン         |
+--------------+
4 rows in set (0.00 sec)

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%a%';
+-----------+
| title     |
+-----------+
| abc       |
| ABC       |
| abc     |
| ABC     |
+-----------+
4 rows in set (0.01 sec)

問題

上記の方法だけでは濁点・半濁点を含む半角カタカナに関してはうまく検索できないです。
半角カタカナで濁音・半濁音を入力すると濁点と半濁点で1文字使用するため、単純に照合順序を変更するだけではLIKE検索の際にマッチングしません。1

mysql> SELECT title FROM articles;
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんデプロイ                  |
| Fabricでかんたんデプロイ                    |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%デプロイ%';
+-----------------------------------+
| title                             |
+-----------------------------------+
| Fabricでかんたんデプロイ            |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%デプロイ%';
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんデプロイ                    |
+-----------------------------------------+
1 row in set (0.00 sec)

解決法

やや強引ですが、検索対象のカラムの値から半角の濁点・半濁点を削除すればマッチするようになります。

mysql> SELECT title FROM articles WHERE REPLACE(REPLACE(title, '゙', ''), '゚', '') COLLATE utf8mb4_unicode_ci LIKE '%デプロイ%';
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんデプロイ                  |
| Fabricでかんたんデプロイ                    |
+-----------------------------------------+
2 rows in set (0.01 sec)

濁点・半濁点を含む半角カタカナでLIKE検索したい場合は、検索キーワード側でも同様の処理をすれば良いです。

mysql> SET @keyword='デプロイ';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT title FROM articles WHERE REPLACE(REPLACE(title, '゙', ''), '゚', '') COLLATE utf8mb4_unicode_ci LIKE CONCAT('%', REPLACE(REPLACE(@keyword, '゙', ''), '゚', ''), '%');
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんデプロイ                  |
| Fabricでかんたんデプロイ                    |
+-----------------------------------------+
2 rows in set (0.00 sec)

まとめ

どうしても運用の都合上半角カタカナのデータを登録する必要があるとのことだったので今回のような実装をしましたが、データの登録時点で制限を設けるなどすればこのような実装は不要です。
パフォーマンスについては計測していないので、気が向いたら確認したいです。


  1. LIKE検索では1文字ずつマッチングするため。=で比較した場合はちゃんとマッチングします。 

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【PHP初心者向け】MySQLからSQL文で絞り込み、結果をサイトに表示する

0.概要

デザイナーをやっていると、静的なサイトは作れるものの、
動的なサイトを作る時にはエンジニアに頼むことが多く、
自身で完結できないことが歯痒い時があります(実体験)。

そこで今回は、
データベース(今回はMySQL)に保存したデータを、
条件によって動的に出し分け(PHPで、SQLのクエリを書く)、
サイトに表示する方法を記載いたします。

1.はじめに(データベースの準備)

まず最初に、データベース自体にデータを入れます。
借りてるレンタルサーバーのコントロールパネルから、
phpMyAdminにログインするか、
データベースをデータベース管理ツールと連携させましょう。

オススメの無料データベース管理ツールは下記の2つです。

名前 特徴 入手先URL
DBeaver クラシカルなUIだが多機能。ER図というDBの繋がりを表示してくれる機能がある。※今回はこれを実例に進めて参ります。 https://dbeaver.io/
Metabase ビジュアライズが充実している。クエリを知らなくともGUI上で色々できるのでデザイナーにも扱いやすい。 https://www.metabase.com/start/oss/jar

※導入に関しては他の方が書かれた下記の記事を参考にして下さい。

・ データベースツール DBeaver のインストール(Windows 上)
https://www.kkaneko.jp/tools/win/dbeaver.html
・MetabaseをWindowsにインストールする
https://qiita.com/n-i-e/items/b721687903055659ee2d

上記いずれかのデータベースにて入れたら、
まずはDB上にテーブル・カラム(項目名)を設定するために、
UI上にてテーブルの設定、カラムの設定、レコードの追加を行って下さい。

ここではDBeaverの画面を参考に進めて参ります。

・テーブルの追加
ツリーでDBをクリックし、ウインドウで開いたら[テーブル]を右クリックし、
[新しく作る 表]をクリックすると設定画面に入り、作成されます。
image.png
・カラムの追加
先程作成されたテーブルを開き、[列]にて、右クリックし、
[新しく作る カラム]をクリックすると設定画面に入り、作成されます。
image.png
・レコードの追加
最後に、今設定されたテーブル・カラムにレコードを追加するため、
SQL文のエディタを開きます。
DBeaverであれば、メニューの[SQLエディタ]から開けます。
image.png

開けたらDB上に新規レコードを追加するクエリを記述しましょう。

INSERT INTO [DB].[テーブル名]
([カラム名1], [カラム名2])
VALUES('[カラム名1の値]', '[カラム名2の値]');

※[]は削除し、中身をご自身のDBに合わせた文字に変えて下さい。

終了したらテーブルの[データ]にてちゃんと追加されているか確認しましょう。
私の場合は2つのレコードを下記のように入れ、問題ないことを確認しました。
2020-09-25_023529.png

2.PHP側でのMySQLとの連携

データベースの準備が出来たら、
PHPの読み込むファイル(例えばindex.phpなど)に、
MySQLへ接続するための、情報を記載いたします。

<?php 

// MySQLの接続情報をセット
$db = 'mysql:host=[データベースのホスト名];dbname=[データベース名]';
$user = '[ユーザー名]';
$pass = '[パスワード]';

//接続のテスト
try {
    $pdo = new PDO($db, $user, $password);
    echo "接続に成功しました";
} catch (PDOException $e) {
    echo "接続に失敗しました";
    exit();
}

  //次のステップの時にここにSQLを書く

?>

こちらを読み込んでみて「接続に成功しました」と出れば成功です。
接続に失敗しましたと表示された場合は、接続設定を見直してみて下さい。

豆知識・・・PDOはPHP Data Objectが正式名称です。

3.実際にクエリを書いて反映してみる

先程のphpファイルの、

//次のステップの時にここにSQLを書く

を書き換えます。

が、SQLを書く際はprepare文とexecute文で前処理をする必要があり、
その記述も合わせて書きましょう。

// SQLを書く(''の中は任意のクエリを記述)
$sql = 'SELECT [表示項目] FROM [データベース名].[テーブル名] WHERE [条件]';

// prepare文で上記のSQLを定義し、execute文で定義したSQLを実行
$prepare = $pdo->prepare($sql);
$prepare->execute();

// 結果を出力
foreach($prepare->fetchAll() as $result){
  // echo $result['カラム名'];にて出力できる
  }

私の場合、クエリにてshop_idがtest1のものに絞り、
2つのカラム(shop_nameとshop_id)をh2要素として表示させたため、
image.png
と表示されました。

4.最後に

今回はクエリをこちら側で書きましたが、
サイト閲覧者に入力してもらうことによって、
ユーザー毎にリクエストした結果を表示することも可能です。

今後、その方法も記事にして行きます。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む