Posted on: 17.08.2020 Posted by: Alex D Comments: 0

How to split data into multiple pages and then combine them

My service spage.me collects site statistics. Up to 10,000 records are entered into the table every day. In two months, the table will have 600,000 rows. This is a big problem. If you make a request by the date for the last week, the server will be heavily loaded. If you make a request often, you will run out of RAM.

Solution to the problem. It is necessary to create a new table every day. If you need to make a sample for a week, then you need to combine 7 tables. Then the combined table will have 70,000 rows.

Each new table must have a unique number. Let’s use php function date(). And create a unique table name.

//time() Returns the current timestamp.
//Returns the number of seconds since the beginning of the Unix Epoch (January 1, 1970 00:00:00 GMT) until the current time.
$kol_day = (int)(time()/60/60/24);//number of days since January 1, 1970
$name_stat_table = "z_".$id_site."_".(string)$kol_day."_stat";

Before creating a table, let’s check if a table with this name already exists?

if(!mysqli_query($link, "DESCRIBE `".$name_stat_table."`")) {
    $sql_create_table = "CREATE TABLE `".$name_stat_table."` ( `id` 
    mediumint(10) NOT NULL AUTO_INCREMENT, `timestamp` timestamp NOT NULL 
    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `referer` text 
    NOT NULL, `webpage` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) 
    ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
    mysqli_query($link, $sql_create_table);  
}

Thus, ten tables are created in 10 days.

How to combine from these 10 tables, the last 7?

The SQL operator UNION is used for this.

SELECT * FROM table_name1 WHERE condition
   UNION SELECT * FROM table_name2 WHERE condition
   UNION SELECT * FROM table_name3 WHERE condition
   UNION SELECT * FROM table_name4 WHERE condition
   UNION SELECT * FROM table_name5 WHERE condition
   UNION SELECT * FROM table_name6 WHERE condition
   UNION SELECT * FROM table_name7 WHERE condition

Let’s create a new union.php file.

PHP code for joining tables in our case.

$kol_day = (int)(time()/60/60/24);
$Ndays = 7;
$Finish_table = $kol_day;
$Start_table = $Finish_table-$Ndays;
$query_union = "CREATE TEMPORARY TABLE `union_table` ";
for ($x=$Start_table; $x++<$Finish_table;) {
    $tek_table = "SELECT * FROM `z_".$id_site."_".$x."_stat` WHERE 1 ";
    if(mysqli_query($link, "DESCRIBE `z_".$id_site."_".$x."_stat`")){
        $query_union .= $tek_table;
        if ($x <> $Finish_table) $query_union .= " UNION ";
    }
}
$DB_query_union = mysqli_query($link, $query_union);

We now have a combined table of union_table for seven days. This table is temporary, not real, not permanent. It exists as long as you are in the current union.php file.

Therefore, we will add below in union.php the query code for the temporary table union_table.

$query = "SELECT timestamp, referer FROM `union_table`";
$DB_query = mysqli_query($link, $query);
While ($row = mysqli_fetch_array($DB_query)) {
	$timestamp = $row['timestamp'];
	$referer = $row['referer'];
	echo "<DIV>".$timestamp." - ".$referer."</DIV>";
}

Sample code

Code for create.php file.

$referer = $_GET['referer'];
$webpage = $_GET['webpage'];

$kol_day = (int)(time()/60/60/24);
$name_stat_table = "z_".$id_site."_".(string)$kol_day."_stat";

$link = mysqli_connect($IP_BD, $LOGIN_BD, $PASSWORD_BD, $NAME_BD);

if(!mysqli_query($link, "DESCRIBE `".$name_stat_table."`")) {
    $sql_create_table = "CREATE TABLE `".$name_stat_table."` ( `id` 
    mediumint(10) NOT NULL AUTO_INCREMENT, `timestamp` timestamp NOT NULL 
    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `referer` text 
    NOT NULL, `webpage` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) 
    ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
    mysqli_query($link, $sql_create_table);  
}

$query_insert="INSERT INTO `".$name_stat_table."` (`referer`,`webpage`) VALUES ('".$referer."','".$webpage."')";
mysqli_query($link, $query_insert);

mysqli_close($link);

Code for union.php file.

$link = mysqli_connect($IP_BD, $LOGIN_BD, $PASSWORD_BD, $NAME_BD);

$kol_day = (int)(time()/60/60/24);
$Ndays = 7;
$Finish_table = $kol_day;
$Start_table = $Finish_table-$Ndays;
$query_union = "CREATE TEMPORARY TABLE `union_table` ";
for ($x=$Start_table; $x++<$Finish_table;) {
    $tek_table = "SELECT * FROM `z_".$id_site."_".$x."_stat` WHERE 1 ";
    if(mysqli_query($link, "DESCRIBE `z_".$id_site."_".$x."_stat`")){
        $query_union .= $tek_table;
        if ($x <> $Finish_table) $query_union .= " UNION ";
    }
}
mysqli_query($link, $query_union);

$query = "SELECT timestamp, referer FROM `union_table`";
$DB_query = mysqli_query($link, $query);
While ($row = mysqli_fetch_array($DB_query)) {
	$timestamp = $row['timestamp'];
	$referer = $row['referer'];
	echo "<DIV>".$timestamp." - ".$referer."</DIV>";
}

mysqli_close($link);

Additional Information.

In files create.php and union.php, I used SQL statement DESCRIBE – Getting information about the columns of a table.

This statement checks the existence of a table in the database.

DESCRIBE name_table

If the table does not exist, create a table.

if(!mysqli_query($link, "DESCRIBE `".$name_stat_table."`")) {
    //create a table.;  
}

If tables exist, add the table to the query.

if(mysqli_query($link, "DESCRIBE `z_".$id_site."_".$x."_stat`")){
    //add the table to the query
}
Categories:

Leave a Comment