Category: sqlite / MySql / psql

phpで予約システムを作くろう1 DBの設計

弁護士事務所の予約システムを作っていきたい。

まず、DBの設計からですが、
基本は「ユーザーから予約リクエストがあったら、テーブルに書き込む」となりますが、どういう設計がいいのでしょうか？

初期設計では、マスターのテーブルと、予約したい人のそれぞれのテーブルを作っていく、という作りで始めてみたいと思います。
予約時間を30分、1時間、1時間30分、2時間と選べるようにしたいので、timeのカラムもつくります。

create table reserve.master(
	id int unsigned auto_increment primary key,
	reserve date,
	name varchar(255)
)

create table reserve.matsushima(
	id int unsigned auto_increment primary key,
	reserve date,
	time varchar(255),
	name varchar(255),
	content varchar(255)
)

create table reserve.ono(
	id int unsigned auto_increment primary key,
	reserve date,
	time varchar(255),
	name varchar(255),
	content varchar(255)
)

create table reserve.kusano(
	id int unsigned auto_increment primary key,
	reserve date,
	time varchar(255),
	name varchar(255),
	content varchar(255)
)

mysql カラムを先頭に追加

Alter table add firstですね。

ALTER TABLE users ADD id int unsigned auto_increment primary key FIRST;

input formも修正します。

<input type="radio" name="gender" value="男性" checked>男性
	<input type="radio" name="gender" value="女性">女性<br>
	年齢:
	<select name="age">
	<option value="">選択してください</option>
	<option value="20歳未満">20歳未満</option>
	<option value="20-24歳">20-24歳</option>
	<option value="25-29歳">25-29歳</option>
	<option value="30-34歳">30-34歳</option>
	<option value="35-39歳">35-39歳</option>
	<option value="40-44歳">40-44歳</option>
	<option value="45-49歳">45-49歳</option>
	<option value="50-54歳">50-54歳</option>
	<option value="55-59歳">55-59歳</option>
	<option value="60-64歳">60-64歳</option>
	<option value="65-69歳">65-69歳</option>
	<option value="70-74歳">70-74歳</option>
	<option value="75-79歳">75-79歳</option>
	<option value="80歳以上">80歳以上</option>

sqlもorder by id descとします。

$sql = "select * from users order by id desc";

女性でログインすると、男性だけ表示されます。

画像を追加

switch($gender){
	case '男性':
		$img = "asset/img/female.png";
		break;
	case '女性':
		$img = "asset/img/male.png";
}

$i = 0;
foreach($gender_lists as $value){
	if($gender != $value){
		echo "<img src=\"".$img."\" id=\"icon\">";
		echo "<div id=\"profile\"><a href=\"\">".$name_lists[$i] ."</a>:".$age_lists[$i]." <span id=\"fs-s\">".$gender_lists[$i] ."</span><br>";
		echo $job_lists[$i]."(".$area_lists[$i].")</div><br><br>";
	}
	$i++;
}

問題は、年齢が近い人と、エリアが近い人をどういうロジックで計算させるかなんだが、
あれ？　これはよくわからんぞ。
autoincrementのidをbase scoreにして、それにsliceした先頭2桁の年齢が近ければ点数を加算して、in_arrayで都道府県が近隣であれば更に点数を加算でOK？

新規登録画面の項目をmysqlに追加していく

ここら辺は適当につくってきます。
性別、生年月日、エリア、職業あたりでしょうか。

重複登録を避けるため、ipアドレスを登録して、同じipアドレスから重複して登録できないようにしたいと思います。

ALTER TABLE users ADD gender int;
ALTER TABLE users ADD birth int;
ALTER TABLE users ADD area varchar(41);
ALTER TABLE users ADD job varchar(255);
ALTER TABLE users ADD ip int(41);

mysql> desc users;
+———-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————–+——+—–+———+——-+
| username | varchar(41) | YES | UNI | NULL | |
| password | varchar(255) | YES | | NULL | |
| gender | int(11) | YES | | NULL | |
| birth | int(11) | YES | | NULL | |
| area | varchar(41) | YES | | NULL | |
| job | varchar(255) | YES | | NULL | |
| ip | int(41) | YES | | NULL | |
+———-+————–+——+—–+———+——-+
7 rows in set (0.00 sec)

現在の登録画面

<?php elseif($status == "none"): ?>
	<p>ユーザ名(英数字3~32文字)、推測されにくいパスワード(英数字8~32文字)を入力してください。</p>
	<form method="POST" action="">
	ユーザ名:<br>
	<input type="text" name="username"><br>
	パスワード:<br>
	<input type="password" name="password"><br>
	性別:<br>
	<input type="radio" name="example" value="male" checked>男性
	<input type="radio" name="example" value="femail">女性<br>
	年齢:<br>
	<select name="age">
	<option value="">選択してください</option>
	<option value="20歳未満">20歳未満</option>
	<option value="20-29歳">20-29歳</option>
	<option value="30-39歳">30-39歳</option>
	<option value="40-49歳">40-49歳</option>
	<option value="50-59歳">50-59歳</option>
	<option value="60-69歳">60-69歳</option>
	<option value="70-79歳">70-79歳</option>
	<option value="80歳以上">80歳以上</option>
	</select><br>
	年齢:<br>
	<select name="job">
	<option value="">選択してください</option>
	<option value="公務員">公務員</option>
	<option value="経営者・役員">経営者・役員</option>
	<option value="会社員">会社員</option>
	<option value="自営業">自営業</option>
	<option value="自由業">自由業</option>
	<option value="専業主婦">専業主婦</option>
	<option value="パート・アルバイト">パート・アルバイト</option>
	<option value="学生">学生</option>
	<option value="その他">その他</option>
	</select><br><br>
	<input type="submit" value="登録">
	</form>
	<a href="login.php">ログイン</a>
<?php else: ?>

ALTER TABLEでデータベースのカラムを成形

alter tableでメール送信、受信用テーブルのカラム追加、削除を行っていきます。

ALTER TABLE sends ADD alreadyread int afetr junk;

mysql> desc sends;
+————-+——————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+——————+——+—–+———+—————-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(41) | YES | | NULL | |
| destination | varchar(41) | YES | | NULL | |
| subject | varchar(255) | YES | | NULL | |
| body | varchar(255) | YES | | NULL | |
| file1 | varchar(255) | YES | | NULL | |
| file2 | varchar(255) | YES | | NULL | |
| alreadyread | int(11) | YES | | NULL | |
| junk | int(11) | YES | | NULL | |
| deleted | int(11) | YES | | NULL | |
| sendtime | datetime | YES | | NULL | |
+————-+——————+——+—–+———+—————-+
11 rows in set (0.07 sec)

メールボックスをつくろう5 複数添付ファイルの保存パスをDBに格納

php側
画像保存後は、unlinkで一時フォルダの画像を削除します。

session_start();

if(!empty($_FILES['files']['name'])){
      $rand = mt_rand(1,9);
      $date = date("ymdHis");
      $path = "upload/img/".$date.$rand;
      mkdir($path, 0777);
    }

    for ($i=0; $i<count($_FILES&#91;'files'&#93;&#91;'name'&#93;) and $i < 2; $i++) {
    $file_ext = pathinfo($_FILES&#91;"files"&#93;&#91;"name"&#93;&#91;$i&#93;, PATHINFO_EXTENSION);
    if (FileExtensionGetAllowUpload($file_ext) &&  is_uploaded_file($_FILES&#91;"files"&#93;&#91;"tmp_name"&#93;&#91;$i&#93;)) {
      if(move_uploaded_file($_FILES&#91;"files"&#93;&#91;"tmp_name"&#93;&#91;$i&#93;, $path."/".$_FILES&#91;"files"&#93;&#91;"name"&#93;&#91;$i&#93;)) {
          $message .= $_FILES&#91;"files"&#93;&#91;"name"&#93;&#91;$i&#93; . "を送信しました。<br>";
          if($i == 0){
            $file1 = $path."/".$_FILES["files"]["name"][$i];
            $message .= "file1のパスは　".$file1." です。<br>";
          } else {
            $file2 = $path."/".$_FILES["files"]["name"][$i];
            $message .= "file2のパスは　".$file2." です。<br>";
          }
      } else {
        $message = "ファイルをアップロードできません。<br>";
      }
    } else {
      $message = "ファイルが選択されていません。<br>";
    }
  }
  function FileExtensionGetAllowUpload($ext){
    $allow_ext = array("gif","jpg","jpeg","png");
    foreach($allow_ext as $v){
      if ($v === $ext){
        return 1;
      }
    }
    return 0;
  }
unlink($_FILES['files']['name']);

if(isset($_POST["to"])){
	$username = $_SESSION["username"];
	$destination = htmlspecialchars($_POST["to"]);
	$subject = htmlspecialchars($_POST["subject"]);
	$body = htmlspecialchars($_POST["body"]);
	$date =	date("Y-m-d H:i");

	$dsn = "mysql:dbname=mail;host=localhost";
	$user = "hoge";
	$password = "hogehogehoge";
	 
	try {
	    $dbh = new PDO($dsn, $user, $password);
	} catch (PDOException $e){
	    print('connection failed:'.$e->getMessage());
	}

	$stmt = $dbh -> prepare("INSERT INTO sends (username, destination, subject, body, file1, file2, sendtime) VALUES(:username, :destination, :subject, :body, :file1, :file2, :sendtime)");
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':destination', $destination, PDO::PARAM_STR);
    $stmt->bindParam(':subject', $subject, PDO::PARAM_STR);
    $stmt->bindParam(':body', $body, PDO::PARAM_STR);
    $stmt->bindParam(':file1', $file1, PDO::PARAM_STR);
    $stmt->bindParam(':file2', $file2, PDO::PARAM_STR);
    $stmt->bindParam(':sendtime', $date, PDO::PARAM_STR);
    $stmt->execute();
}

ブラウザ(画像添付時)

画像フォルダ

mysql sendsテーブル
おおお、入ってる！　いいね♪

新規送信機能はほぼ出来たので、次はテーブル情報の呼び出しですね。
二重にDBに登録する理由はないので、メール受信者もsendsテーブルの呼び出しでつくってみたいと思います。

パラメータがsentitemsの時に表示

mysql・PHPで疑似的なメールボックスを作りたい

まずは簡易的な設計にしたいので、
Mysql側のテーブルは、(1)account、(2)send、(3)receive の3つぐらいでしょうか。
sendのテーブルとtoで指定したaddressのreceiveテーブルに、メール文を保存して、
送信者・受信者がお互いに見れるようにする。受信者は、メールを見たら、alreadyreadのフラグを立てる。

create table mail.account(
	id int unsigned auto_increment primary key,
	address varchar(255),
	passwords varchar(255),
        name varchar(255)
);

create table mail.send(
	id int unsigned auto_increment primary key,
	accountid varchar(255),
	to varchar(255),
	subject varchar(255),
	body varchar(255),
	file1 varchar(255),
	file2 varchar(255),
	alreadyread int,
	sendtime datetime default null
);

create table mail.receive(
	id int unsigned auto_increment primary key,
	accountid varchar(255),
	subject varchar(255),
	body varchar(255),
	file1 varchar(255),
	file2 varchar(255),
	to varchar(255),
	from varchar(255),
	alreadyread int,
	junk int,
	delete int,
	receivetime datetime default null
);

ああああ、singin・loginフォーム作らないと駄目だ。。

mysqlで複数カラムを検索

以下で可能。

SELECT * FROM tablename WHERE CONCAT(col1,col2) like '%query%';

例:
SELECT * FROM lists WHERE CONCAT(code,name) like ‘%運送%’;

例2:
SELECT * FROM lists WHERE CONCAT(code,name) like ‘%95%’;

例えば、検索クエリが複数の場合
以下だと、emptyで返ってきます。

SELECT * FROM lists WHERE CONCAT(code,name) like '%三菱　化学%';

ワードを二つに分けて、ANDで繋げます。

SELECT * FROM lists WHERE CONCAT(code,name) like '%三菱%' AND CONCAT(code,name) LIKE '%化学%';

以下のように返ってきます。

PDO create tableで4000個テーブルを作る時間

銘柄リストから、それぞれのテーブルを作っていきます。sqlにはif not existsを入れます。

$time_start = microtime(true);
$dsn = "mysql:dbname=equity;host=localhost";
$user = "hoge";
$password = "hogehoge";
 
try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e){
    print('connection failed:'.$e->getMessage());
} 

$sql = "select code from lists";
$stmt = $dbh->query($sql);
while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
		$code[] = $result["code"];
}


foreach($code as $value){
	$sql = "create table if not exists equity.code".$value."(
		id int unsigned auto_increment primary key,
		date int,
		close int
	)ENGINE = MYISAM;";
	$stmt = $dbh->query($sql);
}

$time = microtime(true) - $time_start;
echo "{$time}秒";

mysql側　入ってます。

経過時間:35秒

問題は次ですが、
アップデートがなかった場合：11.73秒

localで走らせるので、そこまで大きな問題ではなさそうですね。
一からもう一度やってみましたが、大体同じ時間です。

試しにgoogle financeから1個データを入れてみます。

これは。。。かなりやる気なくなってきた。