In Linux, MySQL DATA is periodically collected and stored in the database through PHP.

This document describes how to perform statistical calculations on database data in Linux and import the results to another table. This article is divided into three parts to explain: the first part introduces the execution of PHP commands in Linux; the second part introduces how to use scripts to collect statistics of data from the past few months and import data into the database; the third part introduces: how to make statistics on yesterday's data every morning and store the data into the database.

1. Run the php Command in Linux

In Linux, the command for executing the PHP file is: PhP filename arguments.

That is, PHP file name Parameters

Eg:/usr/local/PHP/bin/PHP/home/zhaolincheung/insert_talkcountdata.php

2. Use scripts to collect and store data from the previous months

For example, you can import data statistics from the past 60 days to the database.

First, write a shell file talkcount_insert_db.sh, calculate the date cyclically, and pass the date to the PHP file for statistics and import the data to the database. Shell:

#!/bin/shfor((i=1;i<=60;i++));do    echo -e $i":";    day=`date -d "-"$i" day" +%Y-%m-%d`;    /usr/local/php/bin/php /home/zhaolincheung/insert_talkcountdata.php $day;    echo "";done

Insert_talkcountdata.php collects statistics on the data of a certain day and stores the data in the database as follows:

<? PHP include_once ('/data/analys/DB/mydb. class. PHP '); // defines the class number $ cidarr = array (1002 ); $ yesterday = $ _ server ["argv"] [1]; // obtain the PHP Execution file parameter echo $ yesterday. "\ n"; // traverse the main category and import the data into the foreach ($ cidarr as $ CID) {// count the insert_db ($ CID, $ yesterday );} // calculate the message volume of the specified day based on the specified category and date, and store the data to function insert_db ($ category, $ date) {$ datearr = explode ("-", $ date ); if (checkdate ($ datearr [1], $ datearr [2], $ datearr [0]) = false) {return false;} // convert a date to a timestamp $ thisdate = mktime (0, 0, 0, $ datearr [1], $ datearr [2], $ datearr [0]); $ nextdate = $ thisdate + 3600*24; $ SQL = "select talkid, count (*) as msgcount from Table1 where postcategoryid = $ category "; $ SQL. = "and updatetime >=$ thisdate and updatetime <$ nextdate group by talkid order by msgcount DESC"; $ db = new mydb (); $ res = $ db-> query ($ SQL ); // Initial join array $ Mc ["C20"] = $ Mc ["C10"] = $ Mc ["C6"] = $ Mc ["C5"] = $ Mc [" c4 "] = $ Mc [" C3 "] = $ Mc [" C2 "] = $ Mc [" C1 "] = 0; // counts the number of sessions corresponding to the number of messages in segments. While ($ result = $ db-> fetch_assoc ($ res) {$ msgcount = $ result ["msgcount"]; if ($ msgcount> 20) $ Mc ["C20"] ++; else if ($ msgcount> 10 & $ msgcount <= 20) $ Mc ["C10"] ++; else if ($ msgcount> = 6 & $ msgcount <= 10) $ Mc ["C6"] ++; else if ($ msgcount = 5) $ Mc ["C5"] ++; else if ($ msgcount = 4) $ Mc ["C4"] ++; Else if ($ msgcount = 3) $ Mc ["C3"] ++; else if ($ msgcount = 2) $ Mc ["C2"] ++; else if ($ msgcount = 1) $ Mc ["C1"] ++ ;} // insert table 'talkcount' $ SQL _insert = "insert into talkcount (analysisdate, postcategoryid, C20, C10, C6, C5, C4, C3, C2, C1) values ('"; $ SQL _insert. = $ date. "',"; $ SQL _insert. = $ category. ","; $ SQL _insert. = $ Mc ["C20"]. ","; $ SQL _insert. = $ Mc ["C10"]. ","; $ SQL _insert. = $ Mc ["C6"]. ","; $ SQL _insert. = $ Mc ["C5"]. ","; $ SQL _insert. = $ Mc ["C4"]. ","; $ SQL _insert. = $ Mc ["C3"]. ","; $ SQL _insert. = $ Mc ["C2"]. ","; $ SQL _insert. = $ Mc ["C1"]. ")"; echo $ SQL _insert. "\ n"; $ db-> query ($ SQL _insert); $ db-> close () ;}?>

3. How to make statistics on yesterday's data every morning and store the data into the database

In addition, the data of yesterday needs to be calculated and written into the database every early morning. Here we can useCrontab-eCommand to add the PHP file to be executed in the scheduled task. For example:

0 5 ***/usr/local/PHP/bin/PHP/data/analys/PHP/talkcount_insertdb.php

Indicates that the file talkcount_insertdb.php under the/data/analys/PHP Directory is executed at every day.

The content of talkcount_insertdb.php is as follows:

<? PHP include_once ('/data/analys/DB/mydb. class. PHP '); // defines the class number $ cidarr = array (1002 ); $ yesterday = date ("Y-m-d", strtotime ("-1 day"); // calculate the date of yesterday echo $ yesterday. "\ n"; // traverse the main category and import the data into the foreach ($ cidarr as $ CID) {// count the insert_db ($ CID, $ yesterday );} // calculate the message volume of the specified day based on the specified category and date, and store the data to function insert_db ($ category, $ date) {$ datearr = explode ("-", $ date ); if (checkdate ($ datearr [1], $ Datearr [2], $ datearr [0]) = false) {return false;} // convert a date to a timestamp $ thisdate = mktime (0, 0, 0, $ datearr [1], $ datearr [2], $ datearr [0]); $ nextdate = $ thisdate + 3600*24; $ SQL = "select talkid, count (*) as msgcount from Table1 where postcategoryid = $ category "; $ SQL. = "and updatetime >=$ thisdate and updatetime <$ nextdate group by talkid order by msgcount DESC"; $ db = new mydb (); $ res = $ db-> query ($ SQL ); // initialize the associated array $ Mc ["C20"] = $ Mc ["C10"] = $ Mc ["C6"] = $ Mc ["C5"] = $ Mc ["C4"] = $ Mc ["C3"] = $ Mc ["C2"] = $ Mc ["C1"] = 0; // counts the number of sessions corresponding to the number of messages in segments. While ($ result = $ db-> fetch_assoc ($ res) {$ msgcount = $ result ["msgcount"]; if ($ msgcount> 20) $ Mc ["C20"] ++; else if ($ msgcount> 10 & $ msgcount <= 20) $ Mc ["C10"] ++; else if ($ msgcount> = 6 & $ msgcount <= 10) $ Mc ["C6"] ++; else if ($ msgcount = 5) $ Mc ["C5"] ++; else if ($ msgcount = 4) $ Mc ["C4"] ++; else if ($ msgcount = 3) $ Mc ["C3"] ++; else if ($ msgcount = 2) $ Mc ["C2"] ++; else if ($ msgcount = 1) $ Mc ["C1"] ++ ;} // insert table 'talkcount' $ SQL _insert = "insert into talkcount (analysisdate, postcategoryid, C20, C10, C6, C5, C4, C3, C2, C1) values ('"; $ SQL _insert. = $ date. "',"; $ SQL _insert. = $ category. ","; $ SQL _insert. = $ Mc ["C20"]. ","; $ SQL _insert. = $ Mc ["C10"]. ","; $ SQL _insert. = $ MC ["C6"]. ","; $ SQL _insert. = $ Mc ["C5"]. ","; $ SQL _insert. = $ Mc ["C4"]. ","; $ SQL _insert. = $ Mc ["C3"]. ","; $ SQL _insert. = $ Mc ["C2"]. ","; $ SQL _insert. = $ Mc ["C1"]. ")"; echo $ SQL _insert. "\ n"; $ db-> query ($ SQL _insert); $ db-> close () ;}?>