

[何度か](http://blog.sus-happy.net/server-move-2/ "サーバを移転しました。") [移管を](http://blog.sus-happy.net/sakura-vps/ "さくらのVPSに移管完了") [繰り返してきている](http://blog.sus-happy.net/aws-ec2-nginx/ "サーバをAWS EC2+nginx+PHP+MySQLに乗り換えてみた")当サイトですが、今回は[Linode](https://www.linode.com/)に移管をしてみました。

t1.microからt2.microという手もあったのですが、AWSを利用する理由も特にないかなぁということでまたVPSに舞い戻っています。

ついでにCentOSも7にしてみたので、色々変更点をメモメモ。


## Linodeを契約

メンバー登録して、必要情報を入力、プランは「Linode 1024」を選択、デプロイ等ゴニョゴニョしていたら立ち上がりました。

開設あたりまでは、[こちらのページに詳しく記載されていました](http://blog.uklab.jp/web/sign-up-linode/)ので参考にさせていただきました。


## 必要なモノをインストール

AWSとほぼ同じ環境を用意していきますので、PHP（php-fpm）、MySQL、nginxをインストールしていきます…ついでに標準パッケージも気になっていたMariaDBになっていたのでコチラを利用。お初。

という事で、自分はyumしか使えないので、epel、remiのお世話になります。

```shell
rpm -Uvh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-2.noarch.rpm
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm
```


自動的にレポジトリ見に行くのは微妙なのでenabledを0にしてから、PHP、MySQL、nginxをインストールします。
 と、言いたいところでしたが、PHP5.5をインストールする時にlibgd.soが無いよって言われてしまったので、gd-lastもインストールします。

```shell
yum install --enablerepo=remi gd-last
yum install --enablerepo=remi-php55 php-cli php-mysql php-mbstring php-gd php-fpm
yum install --enablerepo=remi mariadb-server
yum install --enablerepo=epel nginx
```


## MariaDBの初期設定

とは言っても事前調査なしなので、MySQLの初期設定と同様です。[前回の記事と変わりません](http://blog.sus-happy.net/aws-ec2-nginx/ "サーバをAWS EC2+nginx+PHP+MySQLに乗り換えてみた")。

MariaDBを実行するコマンドだけ異なります。CentOS7からは「systemctl」が採用されたそうなので、「/etc/rc.d/init.d/***」では無くなりました。
[systemctlについてはコチラのページで色々詳しく記載がされていました](http://qiita.com/tukiyo3/items/092fc32318bd3d1a0846)ので参考にさせて頂いています。

```shell
systemctl start mariadb
systemctl enable mariadb
```

ちなみにMySQLのダンプデータをそのまま実行しても移すことが出来ました。今のところ不具合は起きていません。


## PHPとnginxの初期設定

コチラは前回の設定と<del datetime="2014-09-28T09:34:23+00:00">変えなくても動きました</del>変えていません。

MariaDBと同様に、systemctlから実行、自動起動設定を行います。


## その他色々

1. 一旦ローカルのhostsをLinodeに向くように書き換えてチェック
2. 問題なさそうだったのでAレコードを書き換え
3. 新規ファイルをアップしてDNSが書き換えられてるかチェック
4. EC2側を（まだちょっと怖いので）stop

EC2を停止してもEBS分が課金されるみたいなので、様子をみてterminateする予定です。

そういえばポート設定してないのでそちらも気が向いたらしておこう。iptablesじゃなくなってるらしいので、試してみたらまたメモします。


## 参考

- [Linodeのアカウント開設からプロモーションコードの適応までを書いてみる – UKLab開発者ブログ](http://blog.uklab.jp/web/sign-up-linode/)
- [systemd – CentOS7で使われているsystemctlについて少しまとめた – Qiita](http://qiita.com/tukiyo3/items/092fc32318bd3d1a0846)


サーバをLinodeに移管してみた


FuelPHP等のフレームワークを使う時はついつい便利なのでクエリビルダを利用してるのですが、どうしても特殊な操作を行う時はクエリビルダを利用するほうが分かりづらくなってしまったり、自動的にエスケープされて利用できなかったりして、結局クエリ文を直書きしてしまっていました。

その一つとして、MySQLのorder by field機能がクエリビルダにメソッドが存在しないのですが、何とかクエリビルダを使いながら実現出来ないか調べてみました。


## order by fieldって？

そもそもorder by fieldとは、指定した順番に検索結果を並べかえる、というMySQL独特な記法です。

```sql
SELECT * FROM `table` ORDER BY FIELD( `column`, 3, 1, 2 );
```

のように記述すると、columnが3→1→2の値の順に取得出来ます。

order by fieldで一つかと思ってましたが、fieldという文字列関数を利用しているみたいです。  
 参考：[MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 7.4 文字列関数](http://dev.mysql.com/doc/refman/5.1-olh/ja/string-functions.html#function_field)


## FuelPHPのクエリビルダ

FuelPHPのクエリビルダはDBクラスの静的メソッドと、そこからチェーンメソッドで条件を繋げていきます。

```sql
SELECT * FROM `table` WHERE `column` = 1 ORDER BY `id` DESC;
```

例えば、上記の様なクエリ文を構築する場合は、下記のように記述します。

```php
DB::select()->from('table')->where( 'column', 1 )->order_by( 'id', 'desc' );
```

系統の違うチェーンメソッドの順番は入れ替えても、構築される結果は変わりません。  
 つまり、上記と下記の記述は同じクエリ分が生成されます。

```php
DB::select()->order_by( 'id', 'desc' )->from('table')->where( 'column', 1 );
```

こういった順番を気にしなくても良いところや、変数型によって自動的にエスケープしてくれる所が個人的に好みなので、フレームワークを利用する際は頻繁に活用しています。

FuelPHPのクエリビルダをまとめてくれているページがありましたので、ご紹介しておきます。  
 参考：[FuelPHPのクエリビルダを表にまとめた – BTT's blog](http://btt.hatenablog.com/entry/2012/08/20/190737)


## order by fieldを実現するときの問題と解決

### 問題

上記例でちょろっと記述しましたが、order byのクエリを構築するには、「`order_by()`」メソッドを利用します。 
`order_by()`の第一引数が並び替えの基準キー、第二引数は昇順(asc)か降順(desc)のどちらかの指定となります。

なので、下記のように記述すると何となく動きそうな気がします。

```php
DB::select()->from('table')->order_by( 'FIELD( `column`, 3, 1, 2 )' )
```

しかし、実際に組み立てられるSQL文は下記の様になります。

```sql
SELECT * FROM ORDER BY `FIELD( `column`, 3, 1, 2 )`
```

「FIELD( \`column\`, 3, 1, 2 )」を文字列として認識してエスケープしてくれますので、狙った通りの動きをしてくれません。

### 解決

そこで「DB::expr()」を利用すると、エスケープせずにそのまま組み込んでくれますので、下記のように記述することが出来ます。

```php
DB::select()->from('table')->order_by( DB::expr( 'FIELD( `column`, 3, 1, 2 )' ) )
```

組み立てた結果は下記の通りです。

```sql
SELECT * FROM `table` ORDER BY FIELD( `column`, 3, 1, 2 );
```

これでクエリビルダを使ってorder by fieldを利用することが出来ます。


## 注意点

DB::expr()を利用する場合自動的にエスケープしない仕様なので、内容が不明確な変数を差し込んだり、変数型を固めずに利用するとセキュリティホールになりうる可能性がありますので、注意して使う必要があります。


## 参考

- [MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 7.4 文字列関数](http://dev.mysql.com/doc/refman/5.1-olh/ja/string-functions.html#function_field)
- [FuelPHPのクエリビルダを表にまとめた – BTT's blog](http://btt.hatenablog.com/entry/2012/08/20/190737)
- [DB::expr()万能すぎワロタ – Dazing days](http://d.hatena.ne.jp/ts_asano/20121114/1352899282)

FuelPHPのクエリビルダでMySQLのorder by fieldを利用するためにDB::expr()を使ってみた


これまでは、[さくらのVPS](http://vps.sakura.ad.jp/)を利用させていただいていたのですが、最近の流行りに乗っかろうかと思いまして[AWSのEC2](http://aws.amazon.com/jp/ec2/)でサーバを立ちあげて移管してみました。

単純にnginxに慣れていない事もあって色々と躓いてしまいましたが、なんとか表示できています。
 不具合がまだチョコチョコ出ちゃってますが、メインコンテンツが見れてるのでとりあえず保留中です。

また何かの時に新しく作るかもしれないので、参考サイトとメモを残しておきます。


## AWS EC2の設定

まずAWSのアカウントを作ってEC2のインスタンスを作ります。

使い慣れているので、CentOSを選びましたが全部同じのはなぁ…と思ってCentOS6 64bitを選択しました。

また、nginxやPHP、MySQL、WordPressまでプリインストールされているモノもあるのですが、設定方法を勉強するためにも今回はOSのみのイメージを選択しています。

WordPressを利用するだけなのであれば、[こちらの記事](http://8bitodyssey.com/archives/3344)が参考になるかと思います。

### 参考サイト

- [civic site » EC2のMicroインスタンス + nginx + fastcgi + wordpressで複数サイトの運用！しかも新規は1年無料！](http://civic.xrea.jp/2012/01/08/ec2-micro-nginx-wordpress/)
- [AWS + 網元で、超高速 WordPress を手に入れよう、そうしよう | 8bitodyssey.com](http://8bitodyssey.com/archives/3344)


## CentOSの初期設定

ほとんど何も入っていないので、色々とインストールします。

まずは、CentOSのパッケージだとPHPとMySQLのバージョンが微妙なのと、nginxが含まれていないので、yumレポジトリを追加します。

```shell
rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-7.noarch.rpm
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
rpm -Uvh http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm
```

epelとremiは最初から「enabled」が「1」、「rpmforge」は「0」になっています。
 どちらにするかは自由かと思いますが、個人的には選べるようにしたかったので、全て「0」にしています。

ちなみに設定情報は、「/etc/yum.repos.d/****.repo」に書かれています。

最後にPHPとMySQL、nginxをインストールします。

```shell
yum install --enablerepo=remi php-cli php-mysql php-mbstring php-gd php-fpm
yum install --enablerepo=remi mysql-server
yum install --enablerepo=epel nginx
```

PHPはnginx上でfastcgiとして動かすので、「php-fpm」も一緒にインストールしました。

### 参考サイト

- [majin.jp » 録画サーバ移設完了](http://www.majin.jp/archives/287)


## MySQLの初期設定

基本的に初期設定のままですが、文字コードをUTF-8で利用できるようにちょこっとだけ編集しました。

```shell
[mysqld]
character-set-server = utf8
skip-character-set-client-handshake

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8
```

あとは、起動して「mysql_secure_installation」で初期設定をしておきます。

```shell
/etc/rc.d/init.d/mysqld start
chkconfig mysqld on
mysql_secure_installation
```

### 参考サイト

- [さくらの VPS に nginx を入れてリバースプロキシ設定するまでの作業メモ（検証用） | ウェブル](http://weble.org/2012/05/22/nginx-work-log)
- [データベースサーバー構築(MySQL) – CentOSで自宅サーバー構築](http://centossrv.com/mysql.shtml)


## PHPの初期設定

「php.ini」を色々といじって設定を行います。
 いまいち何が最適なのかわからないので正直適当に触っていますが、参考サイトを参照いただければと思います。

```shell
vi /etc/php.ini
```

あとは、fastcgiのためにphp-fpmの設定を行います。

```shell
vi /etc/php-fpm.d/www.conf
; 内部ポートを利用する例が多いですが、ソケットにしました
listen = /path/to/php-fpm.socket
; userとgroupをnginxで設定する名前にします
user = nginx
group = nginx
; pm.**** は勉強不足です。
; 予め設定した領域内で動かすか、変動させるか？って感じでしょうか
```

最後にphp-fpmを起動させます。

```shell
/etc/init.d/php-fpm start
chkconfig php-fpm on
```

### 参考サイト

- [PHP/tips/推奨php.ini設定 – yohgaki's wiki](http://wiki.ohgaki.net/index.php?PHP%2Ftips%2F%E6%8E%A8%E5%A5%A8php.ini%E8%A8%AD%E5%AE%9A)
- [PHP5.3 php.iniの必須設定サンプル – ほめお日記](http://d.hatena.ne.jp/home0/20111112/1321062630)
- [日本語利用の為の設定(mbstring) – php.iniの設定 – PHPインストールと初期設定](http://www.phpbook.jp/install/phpini/index5.html)
- [さくらの VPS に nginx を入れてリバースプロキシ設定するまでの作業メモ（検証用） | ウェブル](http://weble.org/2012/05/22/nginx-work-log)


## nginxの初期設定

最後にnginxを設定します。かなり手探り状態なので合ってるかどうか怪しいですが…。

ほぼ[参考サイト](http://weble.org/2012/05/22/nginx-work-log)のコピペですが、ソケットでfastcgiを動作させ、リバースプロキシを行なっているつもりの設定です。

```shell
vi /etc/nginx/nginx.conf
http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  /var/log/nginx/access.log  main;
    error_log   /var/log/nginx/error.log;

    sendfile        on;
    #tcp_nopush     on;

    #keepalive_timeout  0;
    keepalive_timeout  65;

    # gzip圧縮
    gzip  on;
    gzip_http_version 1.0;
    gzip_vary         on;
    gzip_comp_level   6;
    gzip_types        text/xml text/css application/xhtml+xml application/xml application/rss+xml application/atom_xml application/x-javascript application/x-httpd-php;
    gzip_disable      "MSIE [1-6]\.";

    # リバースプロキシの設定
    proxy_cache_path  /var/cache/nginx levels=1:2 keys_zone=czone:4m max_size=50m inactive=120m;
    proxy_temp_path   /var/tmp/nginx;
    proxy_cache_key   "$scheme://$host$request_uri";
    proxy_set_header  Host               $host;
    proxy_set_header  X-Real-IP          $remote_addr;
    proxy_set_header  X-Forwarded-Host   $host;
    proxy_set_header  X-Forwarded-Server $host;
    proxy_set_header  X-Forwarded-For    $proxy_add_x_forwarded_for;

    upstream backend {
        ip_hash;
        server 127.0.0.1:8080;
    }
    upstream php-backend {
        server unix:/path/to/php-fpm.socket;
    }

    # Load config files from the /etc/nginx/conf.d directory
    # The default server is in conf.d/default.conf
    include /etc/nginx/conf.d/*.conf;
}
```

基本的には「virtual.conf」を編集するそうなのですが、サブドメインを色々と使っているので、サブドメインごとに「example.com.conf」の様に用意しています。

こちらもほぼコピペですが、WordPress向け＋（要らないと思いますが）PATH_INFOも取得できるようにしています。

```shell
vi /etc/nginx/conf.d/example.com.conf
server {

    listen      80;
    server_name example.com;
    root        /path/to/document/root;
    access_log  /var/log/nginx/example.com/access.log   main;
    error_log   /var/log/nginx/example.com/error.log;
    client_max_body_size 36M;

    location /wp-admin { proxy_pass http://backend; }
    location ~ .*\.php { proxy_pass http://backend; }
    location / {
        set $mobile "";
        if ($http_user_agent ~* '(DoCoMo|J-PHONE|Vodafone|MOT-|UP\.Browser|DDIPOCKET|ASTEL|PDXGW|Palmscape|Xiino|sharp pda browser|Windows CE|L-mode|WILLCOM|SoftBank|Semulator|Vemulator|J-EMULATOR|emobile|mixi-mobile-converter)') {
            set $mobile "@ktai";
        }
        if ($http_user_agent ~* '(iPhone|iPod|Opera Mini|Android.*Mobile|NetFront|PSP|BlackBerry)') {
            set $mobile "@mobile";
        }
        if ($http_cookie ~* "comment_author_|wordpress_(?!test_cookie)|wp-postpass_" ) {
            set $do_not_cache 1;
        }

        proxy_no_cache     $do_not_cache;
        proxy_cache_bypass $do_not_cache;
        proxy_cache czone;
        proxy_cache_key "$scheme://$host$request_uri$is_args$args$mobile";
        proxy_cache_valid  200 301 302 60m;
        proxy_cache_valid  404 5m;
        proxy_cache_use_stale  error timeout invalid_header updating
                               http_500 http_502 http_503 http_504;
        proxy_pass http://backend;
        proxy_redirect http://example.com:8080/ /;
    }
}

server {

    listen      8080;
    server_name example.com;
    root        /path/to/document/root;
    access_log  /var/log/nginx/example.com/access.log   main;
    error_log   /var/log/nginx/example.com/error.log;
    client_max_body_size 36M;

    location / {
        index  index.php index.html index.htm;
        # static files
        if (-f $request_filename) {
            expires 14d;
            break;
        }
        # request to index.php
        if (!-e $request_filename) {
            rewrite ^(.+)$  /index.php?q=$1 last;
        }
    }

    # pass the PHP scripts to FastCGI server listening on socket
    location ~ \.php$ {
        set $script $uri;
        set $path_info "";
        if ($uri ~ "^(.+\.php)(/.+)") {
            set $script $1;
            set $path_info $2;
        }
        # fastcgi_pass   127.0.0.1:9000;
        fastcgi_pass   php-backend;
        include        fastcgi_params;
        fastcgi_param  SCRIPT_FILENAME  /path/to/document/root$fastcgi_script_name;
        fastcgi_param  DOCUMENT_ROOT    /path/to/document/root;
        fastcgi_param  PATH_INFO        $path_info;
        fastcgi_param  SCRIPT_NAME      $script;
        fastcgi_intercept_errors on;
    }
}
```

最後に起動して、エラーが出なければ動く…はずです。

```shell
/etc/init.d/nginx start
chkconfig nginx on
```

### a-blog cms向けの設定

ちなみに[a-blog cms](http://www.a-blogcms.jp/)向けの設定は下記の通り、のはずです。

```shell
vi /etc/nginx/conf.d/acms.example.com.conf
server {
    listen      80;
    server_name acms.example.com;
    root        /path/to/document/vhosts/acms;
    access_log  /var/log/nginx/acms.example.com/access.log   main;
    error_log   /var/log/nginx/acms.example.com/error.log;
    client_max_body_size 36M;

    location /wp-admin { proxy_pass http://backend; }
    location ~ .*\.php { proxy_pass http://backend; }
    location / {
        set $mobile "";
        if ($http_user_agent ~* '(DoCoMo|J-PHONE|Vodafone|MOT-|UP\.Browser|DDIPOCKET|ASTEL|PDXGW|Palmscape|Xiino|sharp pda browser|Windows CE|L-mode|WILLCOM|SoftBank|Semulator|Vemulator|J-EMULATOR|emobile|mixi-mobile-converter)') {
            set $mobile "@ktai";
        }
        if ($http_user_agent ~* '(iPhone|iPod|Opera Mini|Android.*Mobile|NetFront|PSP|BlackBerry)') {
            set $mobile "@mobile";
        }
        if ($http_cookie ~* "comment_author_|wordpress_(?!test_cookie)|wp-postpass_" ) {
            set $do_not_cache 1;
        }

        proxy_no_cache     $do_not_cache;
        proxy_cache_bypass $do_not_cache;
        proxy_cache czone;
        proxy_cache_key "$scheme://$host$request_uri$is_args$args$mobile";
        proxy_cache_valid  200 301 302 60m;
        proxy_cache_valid  404 5m;
        proxy_cache_use_stale  error timeout invalid_header updating
                               http_500 http_502 http_503 http_504;
        proxy_pass http://backend;
        proxy_redirect http://acms.example.com:8080/ /;
    }
}

server {

    listen      8080;
    server_name acms.example.com;
    root        /path/to/document/vhosts/acms;
    access_log  /var/log/nginx/acms.example.com/access.log   main;
    error_log   /var/log/nginx/acms.example.com/error.log;
    client_max_body_size 36M;

    location / {
        index  index.php index.html index.htm;
        if (-e $request_filename) { break; }
        rewrite (.*(^|/)[^\./]+)$ $1/ permanent;
        rewrite ((\.(html|htm|php|xml|txt|js|json|css|yaml|csv))|/)$ /index.php last;
    }

    # pass the PHP scripts to FastCGI server listening on socket
    location ~ \.php$ {
        set $script $uri;
        set $path_info "";
        if ($uri ~ "^(.+\.php)(/.+)") {
            set $script $1;
            set $path_info $2;
        }
        # fastcgi_pass   127.0.0.1:9000;
        fastcgi_pass   php-backend;
        include        fastcgi_params;
        fastcgi_param  SCRIPT_FILENAME  /path/to/document/vhosts/acms$fastcgi_script_name;
        fastcgi_param  DOCUMENT_ROOT    /path/to/document/vhosts/acms;
        fastcgi_param  PATH_INFO        $path_info;
        fastcgi_param  SCRIPT_NAME      $script;
        fastcgi_intercept_errors on;
    }
}
```

### 参考サイト

- [さくらの VPS に nginx を入れてリバースプロキシ設定するまでの作業メモ（検証用） | ウェブル](http://weble.org/2012/05/22/nginx-work-log)
- [Oneライセンスと合わせて完全無料! a-blog cmsをdotCloudで動かしてみた – エンジニアブログ – スカイアーク](http://www.skyarc.co.jp/engineerblog/entry/ablogcms_dotcloud.html)


## 発生中の不具合

現状発生中の不具合は下記の通り、時間見つけて随時修正していきます。

### Perl CGIが動かない

Perlの設定を全くしていないので、現状では動きません。

恐らく[ここの内容](http://abeerforyou.com/?p=429)を参考にすれば良いかと思うのですが、試した時はうまく動作しませんでした。

たぶんちょっとしたミスだと思うので、ちょこちょこ試してみます。

### スニペット集でエラーが発生中

以前、[カスタム投稿タイプを使ってスニペット集を作った](http://blog.sus-happy.net/jquery-pjax-wordpress/)のですが、エラーが発生するようになってしまいました。

リダイレクトの設定なのか、移管した時にミスしているのか、ちょっと原因は不明ですが修正予定です。


## その他AWS利用中の注意点

また、今回の設定の中で、AWSを利用する上で気をつけないといけないなぁ、と感じたことを挙げておきます。

### ローカル時間の設定について

インスタンスを用意した直後では、東京リビジョンで作ったとしても世界標準時になっています。

こちらはAWSに限ったことではないのですが、仮想サーバの特性上ntpをインストールしても時刻設定が出来ないので、AWS側で用意してあるファイルをコピーします。

```shell
cp /usr/share/zoneinfo/Asia/Tokyo /etc/localtime
```

### ポートの開閉について

基本的には「iptables」を利用してポートの開閉を行うかと思うのですが、AWSではサーバ上ではポートの操作を行わず、セキュリティグループと呼ばれる機能で管理します。

利用方法については[こちらのページでわかりやすくまとめられています](http://www.ipentec.com/document/document.aspx?page=amazon-ec2-add-rule-to-security-group&culture=ja-jp)ので、ご参考にしてださい。

自分の場合、iptablesをインストールしてしまい、設定変更しても変わらないなーと思ってSSHポートを何を思ったのかOFFに設定変更した後リブートしてしまう、という行動を取ったことによって、AWSのセキュリティグループは通ってもOS上で弾かれる、というどうしようもない状態になってしまいました。


## まとめ

AWS EC2を初めて利用させてもらいましたが、結構快適です。

好きな時に好きな分だけ、というのはかなり実感出来ました。
 （特に一個インスタンスをどうしようもない状態にしてしまった時に、新しくもう一つインスタンスを立ち上げた時）

AMIとか言うイメージデータを作ったりすると、もっと便利になりそうですが、もうちょっと調査が必要なので、またボチボチ触っていこうかと思います。


## 追記 12/11/30

スニペット集のエラーについては、pjaxの判定に「getallheaders」を利用していたことが原因でした。

PHP5.4からはCGI版でも利用できるのですが、それ以前ではモジュール版のみの機能だったので、そこでエラーが発生していました。

スニペット集では「[jquery-pjax](https://github.com/defunkt/jquery-pjax)」ライブラリを利用しているのですが、こちらではGET値「_pjax=true」も付与されているので、そちらで判定するように変更しています。


サーバをAWS EC2+nginx+PHP+MySQLに乗り換えてみた


[PHPのPDO](http://php.net/manual/ja/book.pdo.php)を使ってMySQLにアクセスしている時に、LIMIT句でエラーが発生してしまったので、その解決方法を残しておきます。


## PDOとは

PHPでデータベースを扱う際に、mysql_queryでクエリ文を受け渡していると、セキュリティの関係上行わないといけないエスケープ等が面倒です。

そこで、PHP5より実装されたPDOを使うことで、プリペアドステートメントによってデータベースに安全且つ簡単にアクセスすることが出来ます。

その他、PEARで提供されている[PEAR MDB2](http://pear.php.net/manual/ja/package.database.mdb2.php)でもプレペアドステートメントを用いることが出来ます。


## PDOによるプリペアドステートメント

PDOを用いたプレペアドステートメントについては下記の通り記述します。

```php
// $dbh = new PDO();

// 疑問符を用いた例
$query = 'SELECT * FROM `table` WHERE `id` < ? AND `title` = ?';
$sth = $dbh->prepare( $query );
$sth->bindParam( 1, $id );
$sth->bindParam( 2, $title );
$sth->execute();

// 名前付きパラメータを用いた例
$query = 'SELECT * FROM `table` WHERE `id` < :id AND `title` = :title';
$sth = $dbh->prepare($query);
$sth->bindParam( ':id', $id );
$sth->bindParam( ':title', $title );
$sth->execute();
```

それぞれ最終的に構築されるSQL分は同じモノが出来上がります。


## LIMIT句でのエラーとその対処

上記の例の通り記述すると、LIMIT句でエラーが発生してしまいます。  
 例えば下記の例では正常に動作しません。

```php
// エラーが発生する例
// $dbh = new PDO();
$limit = 10;
$query = 'SELECT * FROM `table` LIMIT ?';
$sth = $dbh->prepare( $query );
$sth->bindParam( 1, $limit );
$sth->execute();
```

原因としては、LIMIT句に渡す値が文字列として扱われ、最終的に下記のクエリ文となってしまうためです。

```sql
SELECT * FROM `table` LIMIT '10'
```

LIMIT句には数値を渡す必要があるため、bindParamでPDO::PARAM_***という定義済み変数を用いて形式を指定することで解消することが出来ます。

```php
// 正常に動作する例
// $dbh = new PDO();
$limit = 10;
$query = 'SELECT * FROM `table` LIMIT ?';
$sth = $dbh->prepare( $query );
$sth->bindParam( 1, $limit, PDO::PARAM_INT );
$sth->execute();
```

今回の様な例に限らず、上手く実行できないからここだけはプリペアドステートメントを使わない、という様な状態は避けていきたいですね。


## 参考

- [PDO MYSQLの組み合わせでLIMITを使う場合の注意点 » とりあえず9JP](http://9jp.info/archives/89)

PHPのPDOでLIMIT句でエラーが発生した時の対処


MySQLのクエリを組み立てている時に、やりたかった事を実現するために、ユーザ定義変数を利用したので、また使うときに忘れていても良いようにメモしておきます。


## ユーザ定義変数の記述方法

ユーザ定義変数の記述方法は、こんな感じ。

```sql
SET @foo:="bar";
# SETの時は @foo="bar"; でもOK
```

一度定義しておくと、その接続の間利用することが出来ます。

```sql
SELECT * FROM `hoge` WHERE `fuga` = @foo;
```


## ユーザ定義変数を使って行番号を追加する

簡単な例として、変数を使って行番号のカラムを追加してみます。

```sql
SET @row:=0;
SELECT
  *,
  ( @row:=@row+1 ) AS `row`
FROM
  `hoge`
WHERE
  `foo` = 'bar';
```

ただ、この書き方だと、クエリが2つになってしまうのが少し気になりますね。


## SELECT文内でユーザ定義変数を定義する

やはりクエリを1つにしたかったので、調べてみたところ[こんな方法](http://m97087yh.seesaa.net/article/109049904.html)がありました。

上記の例で参考サイトの方法を利用すると、こんな感じ。

```sql
SELECT
  *,
  ( @row:=@row+1 ) AS `row`
FROM
  ( SELECT @row:=0 ) AS `dummy`,
  `hoge`
WHERE
  `foo` = 'bar';
```

これなら一回の呼び出しで行けるのでスッキリしますね。  
 ただし、参考サイトにも書かれているように、ユーザ定義変数をorder byする場合に意図した結果にならない可能性があるので注意してください。


## やりたかったこと

最後に応用例として、自分がやりたかった事をメモしておきます。

<table><thead><tr><th>カラム名</th><th>データ型</th><th>備考</th></tr></thead><tbody><tr><td>entry_id</td><td>int</td><td>auto_increment</td></tr><tr><td>date</td><td>datetime</td><td></td></tr><tr><td>flag</td><td>tinyint</td><td>0 or 1</td></tr><tr><td colspan="3">その他色々</td></tr></tbody></table>こんな感じのテーブルがあった時に、下記条件で引っ張り出そうとしました。

- 日付降順
- 5件取得
- flagが0
- flagが1の場合、最新のモノから3件分は除外

上3つは問題ありませんが、最後の一つを実現するためにユーザ定義変数を使っています。  
 実装したクエリ文はこんな感じ。

```sql
SELECT
  *
FROM
  `entry` AS `a`
    INNER JOIN (
      SELECT
        `entry_id`,
        CASE `flag`
          WHEN 1 THEN @flag_num:=@flag_num+1
          ELSE -1
        END `flag_num`
      FROM
        ( SELECT @flag_num:=0 ) AS `dummy`,
        `entry`
      ORDER BY
        `date` DESC
    ) AS `unite` USING( `entry_id` )
WHERE
  `flag_num` > 3 AND `flag_num` < 0
ORDER BY
  `date` DESC
LIMIT
  0,5
```

サブクエリ上でflagが0の時は-1、flagが1の時は該当した行数のカラムを追加し、Where句で判別しています。

このくらいだったら単純に8件取得して、PHP側で取捨選択してもよかったかもしれませんね…。


## 参考サイト

- [MySQLで行番号: Ma note](http://m97087yh.seesaa.net/article/109049904.html)

MySQL

サーバをLinodeに移管してみた

FuelPHPのクエリビルダでMySQLのorder by fieldを利用するためにDB::expr()を使ってみた

サーバをAWS EC2+nginx+PHP+MySQLに乗り換えてみた

PHPのPDOでLIMIT句でエラーが発生した時の対処

MySQLのユーザ定義変数を使って色々やってみた