icon
Let's create! WordPressサイト制作とメンテ/エラー対応から、PHP/CSS/javascriptプログラミング、動画・音・イラスト等制作まで扱うコラム

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

更新: 2024/02/10 2990文字

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

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

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

c_posts_id 投稿id
c_posts_date 投稿時間
形式『2023-06-03 20:00』
c_posts_modified 更新時間
更新時に保存
c_posts_description 概要欄
meta_description出力
c_posts_permanent_link パーマリンク
ドメイン後ろ部分
c_posts_status 公開(published)
または非公開(private)
c_posts_title 記事タイトル
c_posts_cat カテゴリ
セパレータで複数
c_posts_thumb アイキャッチ画像パス
c_posts_content 記事本文

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

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

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

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

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

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

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

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

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

まずはデータベース接続テストで、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で分割したあと、ループ回して全部出します。

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

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

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

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

select * from c_posts WHERE c_posts_cat like “%cat-a%”

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

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

あとがき・まとめ

  • SQLiteデータベースは直接アクセスされるとまずいので、公開領域の外に置くか、アクセス制限をかける
  • PDOでSQLite接続時には、データベースユーザーやパスワードを必要としない
  • id指定時のクエリは『WHERE 〇〇(idのフィールド) = ◇◇(idの数値)』という形に
  • 特定の文字列でヒットさせたいときはlike検索

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


【カテゴリ】- PHP
【タグ】- , , ,

関連記事