PHPやSQLでURLのクエリ文字列を削除するには

公開

フォト蔵APIで取得する画像 URL が最近のサイト不具合以降、某クラウドサービスとおぼしき クエリ文字列 を含でいることに最近気づきました。そこで、データベース内へ既に取り込んでしまった画像URLは SQL 文で、これから取り込む分はPHP で クエリ文字列 を 削除 する仕組みを考えてみます。

Hong Kong Tram Archive

弊サイトで10年以上続いている老コンテンツ、Hong Kong Tram Archiveは、日々自分で撮った香港トラム画像を時系列、車番別に閲覧できるシンプルなページです。

2万枚を超える膨大な画像データをレンタルサーバにホスティングするのは怖いので、当初より画像は画像SNSサービスのフォト蔵へアップロードし、画像URLへのリンクをサイト内のデータベースに保持する仕組みを採っています。

 

フォト蔵API

この画像URLをフォト蔵からまとめて取得するのに利用しているのが、フォト蔵APIです。

追加した画像のURLをフォト蔵APIで取得、データベースへインポートする仕組みをPHPで組んで、定期的にこれをブラウザで叩いて更新しているのですが、2023年9月より続くフォト蔵の不具合を機に、URLの仕様に変化がありました。

図1.フォト蔵API画像情報取得例

図1.フォト蔵API画像情報取得例

1つの画像から、大きさの異なる3つの画像URLが得られるのですが、そのいずれにもクエリ文字列が連結されるようになりました。ちなみに、クエリ文字列はあってもなくても画像を開くことは可能です。

 

SQL文でクエリ文字列を一括削除

この仕様変更に気づかず、既に大量のレコードをデータベースへインポート済みも、フィールド長超過により尻切れ不完全な状態で格納されているので、そのままでは利用できません。

フォト蔵APIからインポートするPHPを対処する前に、テーブル内の3つのフィールド( url1 , url2 , url3 )に格納されたURLから?以降を一括削除するSQL文を組んでみました(MySQL 5.1.73にて)。

図2.MySQL Workbenchの例

図2.MySQL Workbenchの例

やっていることは、文字列中の特定の文字 ? の位置を INSTR で取得、その位置以降の文字列を SUBSTR で取得、 REPLACE でそれを長さゼロ文字列に入れ替えるというもの。

SQL文の中に@変数を使う際の振る舞いの特性から、かなり見にくい記述になっていますが、上記 SELECT 文では、クエリ文字列を含むレコードの抽出、 UPDATE 文のコメントを外して SELECT 文側へ掛けると、一括削除更新されます。

@変数の使い方に手間取りましたが、これで既存レコードの修正は完了です。

 

PHPでのインポート時にクエリ文字列を削除

次に、今後インポートする際にPHP上で予めクエリ文字列を除去するよう、インポートプログラムを変更したいと思います。

これまで、APIから得たxmlで INSERT 文を実行するループを、次のように組んでいました。

 

strstr 関数を使うと、次のようにとてもシンプルに実現できるように思えるのですが、

検索文字が対象文字列中に含まれない場合、 false が返ってくるのみなので使えません。

 

結局、 strcspn 関数でURL文字列の左端から ? が登場するまでの長さを得て、 substr 関数でその長さ分を左端から抜き出すようにしました。これならクエリ文字列が含まれない場合も、元の文字列をそのまま得ることができます。

 

フォト蔵画像のhttpからhttpsへの対応

フォト蔵の画像URLは長らくhttpのままでしたが、今回の変更でようやくhttpsへ移行され、httpでのアクセスはできなくなりました。ただ、恒久的なものかしばらく見極めたいので、これについてはデータベースレコードを書き換えずに、クライアントサイドのJavaScriptで対応しています。

 

created by Rinker
¥2,970 (2024/04/19 02:48:19時点 Amazon調べ-詳細)

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA