ウォーリーの雑記帳: mysql

ラベル mysql の投稿を表示しています。すべての投稿を表示

2014年10月18日土曜日

cakephpの中間テーブルの命名規則

複数形をアンダースコアでつなぐ
順番はabc順

「ユーザー」と「趣味」を多対多の関係で表現する場合

interests_usersとなる

modelの命名規則

複数形 + アンダースコア + 単数でつなぐ
順番はabc順

modelは単数なのでモデル名の末尾は単数にする
中間テーブルの一つ目の名称は複数のままにする
interests_user.php

インスタンスの命名規則

アッパーキャメルで複数 + 単数
InterestsUser

2014年3月2日日曜日

iCLUSTAサーバーでphpmyadminを導入する

iCLUSTAでデータベース管理のために便利なphpmyadminを導入する手順です。

cakephp絡みでmySQLをiCLUSTAで使用するための方法は以前に書いていますのでそちらを参考にするとよいと思います。
iCLUSTAサーバーでcakephpを使ってMySQLに接続
今回はphpmyadminの設定部分加筆しました。

はじめにまとめ

MySQLを使うには？

標準ではDBサーバー使用できないので、
アカウントマネージャーでデータベースを申し込む（無料です）

phpMyAdminをインストールするには？

公式サイトからダウンロードしてサーバーにup
configでログインの設定をする

１、MySQLを使用可能にする

そのままでは使えません
GMOクラウドのアカウントマネージャーに行く
右上にあるログイン画面からログインする
各種手続き=>サービスに関する手続き=>オプション申込み=>契約してるプランを選択
追加項目の「無料データベース(MySQL)」を選択=>次へ
この後、案内にしたがって進むと申し込みができる。

注意点

一度に一つのデータベースしか申し込めないので、複数個作る時には上の操作を何回か行う必要がある
申し込みから開設まで1日くらい時間がかかる

２、phpMyAdminのインストール

phpMyAdminの公式サイトから最新版をダウンロードする
解凍してFileZillaなどを使ってphpmyadminフォルダをサーバーの直下に配置
config.inc.phpを作成する。（config.sample.inc.phpからコピー）

/*
 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysql if your server does not have mysqli */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/*
 * phpMyAdmin configuration storage settings.
 */

/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = '';
// $cfg['Servers'][$i]['controluser'] = 'pma';
// $cfg['Servers'][$i]['controlpass'] = 'pmapass';

こちらの値をを書き換えます。
値についてはプランマネージャー=>ユーティリティ=>データベース設定を参照してください

/* 
 * ホストを設定
 * データベースサーバーに続けてポート番号
 * 例 サーバー名：mysql500.in.shared-server.net
 *    ポート番号：5555
 */

$cfg['Servers'][$i]['host'] = 'mysql500.in.shared-server.net:5555';


/*
 * ホスト、ユーザー、パスワード設定
 */

$cfg['Servers'][$i]['controlhost'] = '上と同じ';
$cfg['Servers'][$i]['controluser'] = 'データベース名';
$cfg['Servers'][$i]['controlpass'] = '自分で設定したパスワード';

作成したconfig.inc.phpをphpmyadminフォルダにup
ブラウザからサーバーにアクセスする（www.*******.jp/phpmyadmin）
ログイン

これでphpMyAdminが使えるようになります。自分は、ローカルではmampを使っているのでこれが一番使いやすいです。
wordpressのDBも生データを取得するときはmyadminがあると便利です

2013年12月4日水曜日

iCLUSTAサーバーでcakephpを使ってMySQLに接続

iCLUSTAは安定しているし、GMOクラウドの株主優待使うととてもコストパフォーマンスがいいので使っています。
ただ、あまりマニュアルがしっかりしていないので、サーバーの開設やcakephpを使うときのMySQLの設定がわからない部分が多かったのでここに記します。

はじめにまとめ

MySQLを使うには？

アカウントマネージャーでデータベースを申し込む

データベースの管理は？

プランマネージャーからDBManagerを使う or phpMyAdminをインストールする

cakephp側の設定は？

datebase.phpを変更する

host ホスト名
port ポート番号 *新規で追加
login データベース名
password パスワード
database データベース名

１、MySQLを使用可能にする

そのままでは使えません
GMOクラウドのアカウントマネージャーに行く
右上にあるログイン画面からログインする
各種手続き=>サービスに関する手続き=>オプション申込み=>契約してるプランを選択
追加項目の「無料データベース(MySQL)」を選択=>次へ
この後、案内にしたがって進むと申し込みができる。

注意点

一度に一つのデータベースしか申し込めないので、複数個作る時には上の操作を何回か行う必要がある
申し込みから開設まで1日くらい時間がかかる

２、データベースを管理する

データベースができたら今度はプランマネージャーにログイン
ユーティリティ=>データベース設定=>パスワードなどを設定
ユーティリティ=>DBManager（MySQL)でデータベースの内容を書き換え可能

注意点

SQLを書いて操作できるが、使い勝手が悪い

推奨する方法-phpMyAdminのインストール

phpMyAdminの公式サイトから最新版をダウンロードする
解凍してFileZillaなどを使ってサーバーの直下にディレクトリを配置
ユーザー名として１で設置したデータベースのデータベース名を入力
パスワードとしてデータベース設定で設定したパスワードを入力
ログイン

これでphpMyAdminが使えるようになります。自分は、ローカルではmampを使っているのでこれが一番使いやすいです。

こちら少し詳しく書いたものを投稿したので参照してください
http://lost-waldo.blogspot.jp/2014/03/iclustaphpmyadmin.html

３、cakephpの設定

database.phpを変更
cakephpのディレクトリごとサーバーにアップロード

基本的にはcakephpのディレクトリごとサーバーにアップロードすればOKです。
ただ、ここでだdatabase.phpの設定でつまずいたので(なんてことないのですが)記録します。

datebase.php

データベースとの接続の設定をするファイルですがデフォルトで以下の記述があります。

class DATABASE_CONFIG {

 public $default = array(
  'datasource' => 'Database/Mysql',
  'persistent' => false,    
  'host' => 'localhost',
  'login' => 'user',
  'password' => 'password',
  'database' => 'database_name',
  'prefix' => '',
  //'encoding' => 'utf8',
 );
}

このhost、login、password、database部分を書き換えて使っていました。

class DATABASE_CONFIG {

 public $default = array(
  'datasource' => 'Database/Mysql',
  'persistent' => false,    
  'host' => 'ホスト名:ポート番号',
  'login' => 'ログイン名(iCLUSTAではデータベース名)',
  'password' => 'パスワード',
  'database' => 'データベース',
  'prefix' => '',
  'encoding' => 'utf8',
 );
}

しかしこれでは以下のエラーが...

Cake is NOT able to connect to the database.
Database connection "Mysql" is missing, or could not be created.
SQLSTATE[HY000] [2005] Unknown MySQL server host 'データベースホスト:ポート番号' (1)

のエラーが…この問題は1ヶ月くらいわからず放置してました。ふと、cakephpのリファレンスを見に行った時に、"port"という設定項目があるとこに気が付きました。
そこで、ホストで指定していたポート番号を"port"で設定しました

class DATABASE_CONFIG {

 public $default = array(
  'datasource' => 'Database/Mysql',
  'persistent' => false,    
  'host' => 'ホスト名',
  'port' => 'ポート番号',
  'login' => 'ログイン名(iCLUSTAではデータベース名)',
  'password' => 'パスワード',
  'database' => 'データベース',
  'prefix' => '',
  'encoding' => 'utf8',
 );
}

これでディレクトリ直下にアクセスすると

Cake is able to connect to the database.

ちゃんと表示されました。

設定が少しわかりづらいけどリーズナブルですので利用してみたい方は➔

2013年12月3日火曜日

[MySQL]、[php] 分類のindex管理(4)

引き続き分類を文字列からindex管理に変更する過程です。
【関連する記事】
[MySQL]、[php] 分類のindex管理(1)
[MySQL]、[php] 分類のindex管理(2)
[MySQL]、[php] 分類のindex管理(3)

前回まではデータベースに登録されてある分類項目を選択項目としてhtmlで表示するところまで紹介しました。

今回は全ユーザー共通の分類項目とユーザー独自の分類項目を分けて管理する部分を紹介します。

実は、[MySQL]、[php] 分類のindex管理(2)で作ったテーブルにuser_idを入れておきました。このuser_idを使います。
まずはマスターのIDとしてuserテーブルのID=0にmasterとでも登録しておきます。
これが全ユーザーが使う分類項目となります。同時にuser_id=0が、この後ユーザーとして登録されないように埋めておくことにもなります。

ユーザー独自分類登録

では、ユーザーが分類を登録するページを作ります。前提としてユーザーIDが$_SESSION['user_id']によって受け取れる状態としてます。

[specification_choice.php]

<h2>ユーザー独自の分類を登録</h2>
<form action=“insert_action.php” method="post" >  
 <label>名称</label>
 <input type="text" name="specification_name" />
 <input type="submit" value="追加"/>
</form>

[insert_action.php]

$sql = sprintf('INSERT INTO pay_specifications SET name="%s", user_id=%d, created=NOW()',
  mysql_real_escape_string($_POST["specification_name"]),
  mysql_real_escape_string($_SESSION['user_id'])
  );
mysql_query($sql, $link) or die(mysql_error());

ユーザー独自分類取り出し

それでは、SQL文を書き換えます。phpのコードは前回のページを参照してください。

【やりたいこと】

pay_specificationsを読み出す時にuser_id=0(マスターのID)と使用するユーザー自身のidだけを読み込む

【コード before】

$sql = sprintf('SELECT * FROM pay_specifications
 ORDER BY ID ASC',
 mysql_real_escape_string($_SESSION['user_id']

【コード after】

$sql = sprintf('SELECT * FROM pay_specifications
 WHERE user_id=0 OR user_id=%d
 ORDER BY ID ASC',
 mysql_real_escape_string($_SESSION['user_id']

2行目にWHERE句を追加しました。
これで、他のユーザーが独自に使用する分類項目は表示されないはずです。

ユーザー独自分類の非表示

最後に、ユーザー独自の分類項目を入力ではもう使わないが、一覧表示にはそのまま残したい。そんな時のためにユーザー独自の分類項目は削除ではなく使用フラグでかんりするようにしました。これもテーブルには用意してあって、usesカラムを使って

使用
不使用

と定義して、

[insert_action.php内書き込みのSQL]

$sql = sprintf('INSERT INTO pay_specifications SET name="%s", user_id=%d, created=NOW(), uses=1',
  mysql_real_escape_string($_POST["specification_name"]),
  mysql_real_escape_string($_SESSION['user_id'])
  );
mysql_query($sql, $link) or die(mysql_error());

[読み込みのSQL]

SELECT * FROM pay_specifications
 $sql = sprintf('WHERE user_id=0 OR user_id=%d AND uses=1
 ORDER BY ID ASC',
 mysql_real_escape_string($_SESSION['user_id']

[新たに、削除用のページを作り以下のSQLを発行]

UPDATE pay_specifications SET uses=2 
 $sql = sprintf('WHERE id=%d AND user_id=%d',
 mysql_real_escape_string($_POST['not_use_id']),
 mysql_real_escape_string($_SESSION['user_id'])

deleteするのではなくuses=2とすることで選択肢として表示されず、一覧表示にはそのまま表示される仕組みになっています。

2013年12月2日月曜日

[MySQL]、[php] 分類のindex管理(3)

引き続き分類を文字列からindex管理に変更する過程です。
【関連する記事】
[MySQL]、[php] 分類のindex管理(1)
[MySQL]、[php] 分類のindex管理(2)

前回は、DBの追加と変更について書きました。

今回は実際にデータを取得してhtmlに表示する部分です

データベースの素晴らしい機能であるリレーションを使います。

初めて使った時は感動でした。form部分にセレクトタグを使って表示します。

<?php 
$sql=sprintf('SELECT * FROM pay_specifications
 ORDER BY ID ASC',
 mysql_real_escape_string($_SESSION['user_id'])
);
$result = mysql_query($sql) or die(mysql_error());

$record=NULL;
while ($row = mysql_fetch_assoc($result)) {
 $record[] = $row;
}
?>

<select  name="pay_specification_id">

<?php 
for ($i=0,$count_record=count($record); $i<$count_record; $i++){
 print('<option value="'.h($record[$i]['id']).'">'.h($record[$i]['name']).'</option>');
}
?>

</select>

書いてあるコードの内容

2~11行目

支払分類テーブルからデータを取り出して$recordに格納する。
SQL文生成ークエリ発行ーフェッチして各行を格納

18~19行目

selectタグで挟まれた部分のoptionに先ほどデータを格納した$recordを使って値を入れる。
postする値にpay_specifications.idを、htmlで表示される値にpay_specifications.nameを指定

<option value=1>食費</option>');
<option value=2>交際費</option>');
<option value=3>その他</option>');

のようにデータベースに登録されている値が出力されます。

次回

共通して表示される分類項目とユーザー独自で設定する分類項目をどうやって管理し、表示するようにしたかを紹介していきます。

2013年12月1日日曜日

[MySQL]、[php] 分類のindex管理(2)

前回の記事の続きです。
【関連する記事】
[MySQL]、[php] 分類のindex管理(1)

前回は改造前の状態とやることをザクっと把握したところまで書きました。

今回はデータベースの変更点

を主に紹介します。支払情報を例にとって見てみます

まずは支払情報のデータベースに分類IDという項目を追加します。

ALTER TABLE `pay` ADD `specification_id` INT NOT NULL AFTER `count`

次に分類テーブルを作ります。

カラムにはID、名称、ユーザーID、使用フラグ、作成日、更新日を作っておきます。

CREATE TABLE `pay_specifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `user_id` int(11) NOT NULL,
  `uses` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modefied` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  AUTO_INCREMENT=1 ;

これで支払DBのspecification_idを使って支払分類DBの情報を持ってくる準備ができるようになりました。

次回

実際にデータを取得してhtml上に選択画面として表示させるところまで書きたいと思います。

2013年11月29日金曜日

[MySQL]、[php] 分類のindex管理(1)

[MySQL]、[php]　支払と収入の分類項目をデータベースで管理する

2013/11/25の更新によって追加した機能についてどのように実装していったか書き残しておこうと思います。

今回の変更点

・収入と支払の分類項目をindex管理する。(php、DB設計)
・全ユーザー共通の分類項目を用意。(DB設計)
・ユーザーが独自に設定した項目は他のユーザーには表示されない(DB設計)
・ユーザーが独自に設定した項目を設定するページ作成。(php)
・ユーザー独自の分類項目を「使わない」設定にした後も支出・支払一覧には表示させる。(DB設計)

項目としてはDB設計の変更がメインですね。新しい設定ページが必要だったので時間がかかったのは結局phpのコーディングですが。

改造の動機・問題点

これまでのページでは分類を下のように書いていました。

<select  name="type" id="type" >
<?php 
$bunrui_array = array("交通費","食費","消耗品","交際費","サークル","研究室","その他");
for ($i=0; $i<count($bunrui_array); $i++){
 print('<option value="'.$bunrui_array[$i].'">'.$bunrui_array[$i].'</option>');
}
?>
</select>

この問題点は、2つ

文字列で格納しているので拡張させづらい
全ユーザーが作成者の指定した分類項目でしか分類できない

index管理は口座の設定をできるようにした部分で一度実装しているので、それを応用して行います。んで作る途中で気づいたことは、

ユーザーが独自の分類項目を削除したときどんな挙動をさせたら良いのか？
データベースから削除してしまっては今までのデータを見るときに表示されないので困る。

となるとデータベース上には保存しておく必要があるなと、フラグで管理するのが適切ということになりました。そんなこんなで最初に書いたリストにあるように開発していくのですが、内容は次の記事に

[MySQL]、[php] 分類のindex管理(2 )

2013年11月19日火曜日

【注意点】[php] sprintf関数でSQL文を生成する時

今回の学習

sprintf関数を使ってLIKEやDATEFORMATなど%を使うSQL文を生成する時、%は%%としましょう。

参考にしたサイト
http://d.hatena.ne.jp/niconico_licoco+wu/20130117/1358398457

受け取った変数をエスケープするためにsprintf関数を用いてSQL文を作ってクエリに投げるといったやり方をしばしば行います。(私は基本的にこの方法で統一してます)

例えばこんな感じで使ってます(ユーザーの口座情報を取得するクエリ)

$sql = sprintf('SELECT a.name, u.id FROM user_accounts u 
                  JOIN accounts a ON u.account_id=a.id 
                  WHERE u.user_id=%d ORDER BY ID ASC',
    (mysql_real_escape_string($user_id))
);
$result = mysql_query($sql) or die(mysql_error());

ここからが困った時の様子

この時に、SQL文で%を使う場合(LIKEとか、DATEFORMATとか)があります。
例えば自分のアプリケーションではユーザーの今月の収入データを取得します

$sql = sprintf("SELECT SUM(income.amount) AS sum_income
   FROM income 
   WHERE user_id=%d
    AND date >= DATE_FORMAT(NOW(), '%Y-%m-01')
    AND date < ADDDATE(DATE_FORMAT(NOW(), '%Y-%m-01'), interval 2 month)
                     GROUP BY income.user_id ", 
   mysql_real_escape_string($user_id)
);
$result = mysql_query($sql, $link) or die(mysql_error());

です。この時、上のようにsprintfを使って実行すると......

"Query was empty"

クエリがありませんと?! いやいや、んなまさか？
試しにエスケープしてた部分に適当に値を入れてデータベースに直接SQL文を実行すると値が返ってくる。

ん？ん？ん？普段ならこの直接SQLを打って間違ってる箇所洗い出して見つかるのだが、今回は分からない。

SQLをコーテーションで囲うだけにして単なる文字列として生成してみると...

$sql = "SELECT SUM(income.amount) AS sum_income
   FROM income 
   WHERE user_id=0
    AND date >= DATE_FORMAT(NOW(), '%Y-%m-01')
    AND date < ADDDATE(DATE_FORMAT(NOW(), '%Y-%m-01'), interval 2 month)
                     GROUP BY income.user_id ";
$result = mysql_query($sql, $link) or die(mysql_error());

これは動く！

sprintf関数が原因とわかりまして、初めてsprintf＋MySQLで検索。
判明しました。sprintfの中で%は%%としなくてはいけないみたいです。%sとか%dで変換指定をするので、そのままじゃダメなのね。納得

ということで

sprintf()内で%を使いたいときは%%とする。

正解は

$sql = sprintf("SELECT SUM(income.amount) AS sum_income
   FROM income 
   WHERE user_id=%d
    AND date >= DATE_FORMAT(NOW(), '%%Y-%%m-01')
    AND date < ADDDATE(DATE_FORMAT(NOW(), '%%Y-%%m-01'), interval 2 month)
                     GROUP BY income.user_id ", 
   mysql_real_escape_string($user_id)
);
$result = mysql_query($sql, $link) or die(mysql_error());

登録: 投稿 (Atom)

2014年10月18日土曜日

cakephpの中間テーブルの命名規則

modelの命名規則

インスタンスの命名規則

2014年3月2日日曜日

iCLUSTAサーバーでphpmyadminを導入する

はじめにまとめ

MySQLを使うには？

phpMyAdminをインストールするには？

１、MySQLを使用可能にする

注意点

２、phpMyAdminのインストール

2013年12月4日水曜日

iCLUSTAサーバーでcakephpを使ってMySQLに接続

はじめにまとめ

MySQLを使うには？

データベースの管理は？

cakephp側の設定は？

１、MySQLを使用可能にする

注意点

２、データベースを管理する

注意点

推奨する方法-phpMyAdminのインストール

３、cakephpの設定

datebase.php

2013年12月3日火曜日

今回は全ユーザー共通の分類項目とユーザー独自の分類項目を分けて管理する部分を紹介します。

ユーザー独自分類登録

[specification_choice.php]

[insert_action.php]

ユーザー独自分類取り出し

【やりたいこと】

【コード before】

【コード after】

ユーザー独自分類の非表示

[insert_action.php内書き込みのSQL]

[読み込みのSQL]

[新たに、削除用のページを作り以下のSQLを発行]

2013年12月2日月曜日

今回は実際にデータを取得してhtmlに表示する部分です

データベースの素晴らしい機能であるリレーションを使います。

書いてあるコードの内容

2~11行目

18~19行目

次回

2013年12月1日日曜日

今回はデータベースの変更点

まずは支払情報のデータベースに分類IDという項目を追加します。

次に分類テーブルを作ります。

次回

2013年11月29日金曜日

[MySQL]、[php] 支払と収入の分類項目をデータベースで管理する

今回の変更点

改造の動機・問題点

これまでのページでは分類を下のように書いていました。

この問題点は、2つ

次の記事

2013年11月19日火曜日

今回の学習

ここからが困った時の様子

"Query was empty"

これは動く！

sprintf()内で%を使いたいときは%%とする。

[MySQL]、[php]　支払と収入の分類項目をデータベースで管理する