【MYSQL】【PHP】データベース操作で効率的なループ処理を行う方法【初心者】

現在、プログラムの勉強をしている者です。
勉強目的でサイトを作成して「アクセス解析」も簡単なものを作成する事が出来ました。

アクセス解析の中のIN数とOUT数を集計する部分で壁にあたっています。

当サイトへのIN数と
当サイトからの登録したブログへの送ったOUT数を集計する際に
効率的なコードの書き方に悩んでいます。

現在、勉強中という事もあり
とても単純な方法でコードを書いたのですが
単純な方法だった為、条件分岐を登録しているブログの数だけ書かないといけなくなり
効率の面でも処理の面でも大変な事になっています。

これを効率的に書く方法について教えて頂けると嬉しいです。

テーブル説明：
テーブル名：blog（記事URL,記事タイトル,ブログ名等が入っています。）

テーブル名：incount（参照元URL,IP等が入っています。）

テーブル名：outcount（飛んだ先のURL等が入っています。）

テーブル名：site（登録ブログ名,登録ブログのURL,IN数,OUT数等が入っています。）

IN数とOUT数の集計
登録ブログAのURLが「http://test.com/」だった場合（ドメインは「test.com」が登録されています。）

MYSQL
1//アクセス集計のIN数が入っているテーブル：「incount」のURLが入っているカラム：「referer 」にて
2//test.comに部分一致している数を集計します。
3$stmt = $db->query("select count(*) as cnt from incount where referer LIKE '%test.com%'");
4$incount = $stmt->fetchColumn();
5
6//アクセス集計のOUT数が入っているテーブル：「outcount」のURLが入っているカラム：「out_url 」にて
7//test.comに部分一致している数を集計します。
8$stmt = $db->query("select count(*) as cnt from outcount where out_url LIKE '%test.com%'");
9$outcount = $stmt->fetchColumn();
10
11//テーブル：siteにてIN数とOUT数を書き込む為に
12//該当する登録ブログを「test.com」にて検索しています。
13$sitedomain = "test.com";
14$stmt = $db->prepare("select * from site where sitedomain = :seachword limit 1");
15$stmt->bindParam(':seachword', $sitedomain);
16$stmt->execute();
17$inoutseach = $stmt->fetchAll(\PDO::FETCH_OBJ);
18
19//foreachで１回だけループ回しています。
20foreach($inoutseach as $inout) {
21
22//ここで集計したIN数とOUT数をテーブル：「site」の該当するカラムにアップデート更新します。
23//$sitedomainにて検索しています。（$sitedomainには「登録ブログのドメイン」が入っています。）
24$stmt = $db->prepare("update site set incount = :incount,outcount = :outcount
25where sitedomain = :domain");
26$stmt->execute([
27  ':incount' => $incount,
28  ':outcount' => $outcount,
29  ':domain' => $sitedomain
30]);
31}
32

このコードでアクセス解析で取得したIN数とOUT数を集計して
siteテーブルに集計結果を書き込む事が出来たのですが、
これですと

php
1//ここの部分にて登録ブログのドメインを手動でいれなくてはいけない為、
2//登録ブログの数だけ、複数のコードが必要になってしまいます。
3$sitedomain = "test.com";

登録しているブログの数だけコードを複数書いていかないといけなく、
（登録ブログが10個なら10個コードを書かなくてはいけません。）
もっと効率的な書き方があるのでは？と考え、
色々と調べたり、試してみたのですが上手くいきませんでした。

テーブル名：site
このテーブルにあります
カラム名：
name,（登録ブログの名前が入っています。）
url,（登録ブログのURLが入っています。）
url_domain,（登録ブログのURLのドメインが入っています。）
rss_url,（登録ブログのRSSのURLが入っています。）

ここの「ドメイン」が入っている「url_domain」を利用する事が出来たら
手動で登録ブログのドメインを設定する必要が無く
登録ブログが設定されている回数分（5個登録していたら5回ループを回す）だけループ処理で　
siteテーブルの
1週目：url_domain（ドメイン）の1番目を$sitedomainに指定
2週目：url_domain（ドメイン）の2番目を$sitedomainに指定
3週目：url_domain（ドメイン）の3番目を$sitedomainに指定
といった風にできれば・・・と理想を抱いて試行錯誤を繰り返しているのですが
実力が足りず、上手くいきませんでした。
私に出来たのは上の方にある、手動で設定していく簡単なコードで精一杯でした。

解決方法をご存知の方や
何かお気付きになられた点がありましたら
お力をお貸し頂けると嬉しいです。
どうかよろしくお願いします。

テーブルの構造です。
遅れてしまって申し訳ありません。

テーブル名：incount

mysql> SHOW CREATE TABLE incount;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| incount | CREATE TABLE `incount` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `ip` varchar(50) NOT NULL,
  `url_file` varchar(255) NOT NULL,
  `referer` varchar(255) NOT NULL,
  `user_agent` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=649 DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

テーブル名：outcount

mysql> SHOW CREATE TABLE outcount;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                        |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| outcount | CREATE TABLE `outcount` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `ip` varchar(100) NOT NULL,
  `out_url` varchar(255) NOT NULL,
  `user_agent` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

テーブル名：site

mysql> SHOW CREATE TABLE site;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| site  | CREATE TABLE `site` (
  `id` int(22) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `url` varchar(200) NOT NULL,
  `siterss` varchar(200) NOT NULL,
  `sitedomain` varchar(100) NOT NULL,
  `incount` int(20) NOT NULL DEFAULT '0',
  `outcount` int(20) NOT NULL DEFAULT '0',
  `henkan` float NOT NULL DEFAULT '0',
  `rsscount` int(10) NOT NULL DEFAULT '0',
  `lastdate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

退会済みユーザー

2017/12/16 04:11

コードに関連しているテーブルの定義を提示してください。SHOW CREATE TABLE LIKE "テーブル名"で返ってくる結果を追記してください

shimane

2017/12/16 04:29

教えて頂いて有難うございます！操作に不慣れで時間がかかって申し訳ないです、ただいま追記として記載しました！

退会済みユーザー

2017/12/16 04:32 編集

CREATE TABLE から記述してください。（カラム名：）などコピペに不要な文字入れないください。あくまで回答者の負担を減らすことが目的です。3つとも出している情報が不十分です。

shimane

2017/12/16 04:42

何度も申し訳ありません。今、記載致しました。　こういう事も経験がありませんでしたのでとても勉強になりました。今度からは教えて頂いた通りにしていきます。感謝です！

退会済みユーザー

2017/12/16 04:43

こうしてくれると、回答者は楽なんです。CREATE 〜 charset=?? まで書かれていると、手元で実験しやすいので。

shimane

2017/12/16 04:46

書籍やドットインストールさんやネットの情報で勉強していて、学校や職場でプログラムの事を教えて頂くといった経験が無かったので、とても良い事を教えて頂けました。大感謝です！

行動規範の内容に同意します

回答1件

ベストアンサー

sql
1UPDATE `site` SET 
2  `incount` = (select count(*) as cnt from incount where referer LIKE CONCAT('%', `site`.`sitedomain`, '%'))
3  , `outcount` = (select count(*) as cnt from incount where referer LIKE CONCAT('%', `site`.`sitedomain`, '%'))

多分これで、行けると思う。
検証はしていません。

検証が必要な場合、はテーブル定義を　CREATE TABLE 〜　で提示してください。

投稿2017/12/16 04:19

編集2017/12/16 04:37

退会済みユーザー

総合スコア0

shimane

2017/12/16 04:55

回答有難うございます！私の説明が上手くいかず、申し訳ありません。今回、質問させて頂いたのは今の私の実力だと変数：$sitedomain（登録ブログのドメインを入れます。）に対して手動でドメインを入れていかなければならず、その為に登録ブログの数だけ、コードを複数書かないといけません。（現在は登録ブログが10個なら10個コードを書いています。）これを効率よくするためにテーブル：siteに登録されているブログの数だけ「ループを回して」 $sitedomainの中に「カラム：sitedomain」に登録されているドメインを自動的に入れていければ手動で設定せずに IN数とOUT数の集計結果を該当する登録ブログに書き込む事が出来るのではないかな？と思っているのですが実力が足りなくて、上手く出来ていません。この「登録ブログの数だけループを回す」＋「変数にドメイン名を自動で入れていく」が試行錯誤しても上手くいきませんでした。何かお気付きの点がありましたらお力をお貸し頂けると嬉しいです。

退会済みユーザー

2017/12/16 04:58

このクエリを実行すると、ループを使う必要がありません。全ての登録されているドメインに対して、更新が可能です。

shimane

2017/12/16 05:13

素晴らしいです！ループ処理といえば foreach()という意識がありました。まさかあんなにも多かったコードがこんなにも短縮出来るなんて！！これが本物のプログラム技術者の人なんですね。本当にびっくりしました。私ももっともっと勉強していこうと思います。悩んで悩んでそれでも解決出来なかった事でしたので本当に助かりました！大感謝です！

退会済みユーザー

2017/12/16 06:18 編集

そもそもですが、UPDATEが一発でできるということはSELECTも一発でできるということはお気付きですか？つまり、設計上 site テーブルにそれぞれのカウント数を保存するカラムは不要です。パフォーマンス対策である場合を除いて。

shimane

2017/12/16 06:42

！！そうか、そうですよね。たしかにそうです！今はまだまだ基礎の基礎を勉強している段階なので 1歩1歩進んで行くのを意識しているので、念のために集計・結果を書き込み・変更といった作業を通してプログラムの勉強をしていく予定ですが教えて頂いた考え方を頭の中にいれて勉強を進めていこうと思います。今日は本当に色々な事が勉強出来てとても充実した1日になりました。有難うございます。感謝です！

行動規範の内容に同意します