【php/pdoクラス】SQLite/phpLiteAdminでデータベース作成＆アクセス制限・PDOクラスで記事データ取得してみる

今回は、最近管理人が取り組んでいる『PHP/SQLiteでCMS作ってみるシリーズ』の続きです。実際にCMSを想定したテーブルを作って、PDOクラスで記事データを取ってみました。また、SQLiteの問題点ともいえるセキュリティ面の対策として、アクセス制限もつけてみます。早速内容を確認していきましょう。 (*前回の記事はこちら)

下準備～phpLiteAdminでCMSを想定したテーブル作成＆データ格納

まず、記事を取る前に、CMSを想定したテーブル作成＆データ格納を行います。新しく『c_posts』という名前でテーブルを作成。idはプライマリ＆オートインクリメントにします。作ったフィールドは以下の通りです。

そして、記事４つぶんくらい、こういう感じで作って、データベースに格納していきます。

データベース格納完了。なんか本格的っすね（笑）

SQLiteデータベースの問題点～直接ファイルにアクセスされるので、アクセス制限必須

SQLiteデータベースの問題点というと、これです。 パスワードとかもないので、無対策だと、場所がバレると直接アクセス&ダウンロードされます。『表示ディレクトリの外に置け』とは、こういう理由です。サーバーによっては『表示ディレクトリ以外の階層もある』んですけど(hetemlでいうとwebフォルダの外)、非公開ディレクトリが無い場合もあります。

そういう場合は.htaccessでアクセス制限をかけます。 全部denyにしてしまうとphpLiteAdminからもデータベースにアクセスできなくなるので、『設置ドメインのリファラは許可』という設定にしました。

で、作った.htaccessを、データベースファイルが入っているフォルダに入れます。

.htaccessアクセス制限のおかげで、万が一データベースファイルのurlがバレても、直接アクセスしてダウンロードできなくなりました。

phpLiteAdminで作成した、SQLiteデータベースから情報を取得してみる

記事後半では、上で作ったデータベースから、実際にデータを取ってみます。ＣＭＳで一般的に使われる形を想定して行ってみますか。

まずはデータベース接続テストで、var_dumpしてみる

前回同様、PDOクラスを使って記事を取ってみます。mysqlとことなり、SQLite接続時には、データベースユーザーやパスワードを必要としません(だからアクセス制限が必要というのもある)。とりあえず、c_postsテーブルから全件とるようなクエリを入れました。まずはテストでvar_dump。

データベース接続はokですね。[0]とか[1]みたいに配列の要素番号でも取れるけど、『[”c_posts_id”]=> int(4) [0]=> int(4)』みたいに返ってきているので、わかりやすいですね。

SQLiteデータベースに接続して、id指定し記事データを取るコードと表示例

コードはこんな感じになりました。『id指定』ということなので、『sqlのWHERE句でid指定（WHERE c_posts_id = 1）』というところがポイントです。これでidが1番の記事データが取得できます。 また、カテゴリは一つのフィールドにセパレータで区切って複数いれているので、まずexplodeで分割したあと、ループ回して全部出します。

表示例はこんな感じです。『ＣＭＳの個別記事ページ』みたいな表示になりました。

カテゴリで絞り込む場合はどうするか？

記事一覧やid指定はokですが、『ＣＭＳのとあるカテゴリのアーカイブ』みたいなときは、どうするでしょうか。今回管理人が設計したデータベースの構造上(カテゴリは一つのフィールドにセパレータで区切って複数)、『＝で一致するもの』というのは使えなそうです。逆にカテゴリは1種類固定みたいなときは『＝』で大丈夫なんですけど。

そういう時は『like検索』を使ってみますか。文字列のどこかに『cat-a』が入っていればよいので、部分一致で『like “%cat-a%”』と書きます。 (前方一致のときは後ろに%、後方一致のときは前に%をつけます)

var_dumpしてみました。２記事がヒットしていることを確認。一方は『cat-a|cat-b』のセパレータ込み、もう一方は『cat-a』という値を持っています。あとはループ回して『タイトル・日付・アイキャッチ・概要』などを出力していけばokです。

なお、『like検索を複数のフィールドにかけるとめっちゃ重くなる』という懸念事項があります。今回はフィールドが１つ＆記事データ数が一桁だけだったので、そこまで遅いという印象はなかったです。phpLiteAdmin上でSQLを実行してみたところ『2 行表示(クエリは 0.0003 秒かかりました)』という表示がでました。

あとがき・まとめ

まとめると、こんなところでしょうか。SQLiteは初めて使いましたが、mysqlやWordPressのwpdbクラスで使うようなSQLがそのまま使えたので、そこまで苦戦しませんでした。フロントはなんとかなりそうなので、あとは管理画面でINSERT・UPDATE・DELETEみたいな機能もやってみる予定です。