CODEWITHSUNDEEP
phpmysql
Shyju Abdul Razaq
Shyju Abdul Razaq

Reputation: 41

Run query on entire table in a specific MySQL database

I have a MySQL database for storing log files.

Every 30 days the system creates a new table with a name that ends with date. For example it could be log_01-11-2015, log_01-12-2015, etc., and store the log month-wise.

My requirement is to search for their activity log within a date-range like starting from month January to December. Writing a long SQL union query is not applicable in the web application. I am using php; is there any way to do query within the databsse without writing a long union query by putting all the table names? TABLE_NAME : log_01-11-2015 Table_content log_id log-date_time user_ip activity_id

TABLE_NAME : log_01-12-2015 Table_content log_id log-date_time user_ip activity_id And so on. every first of the month system creates a new table and store data month-wise

Upvotes: 2

Views: 375

Answers (1)

zedfoxus
zedfoxus

Reputation: 37079

If you are using PHP, you could perhaps create your own UNION'ed query with PHP somewhat similar to this:

Test.php

<?php
$startdate = new DateTime('2015-01-01');
$enddate = new DateTime('2015-05-31');
echo getLogSQL($startdate, $enddate);

function getLogSQL($startdate, $enddate) {
    $select = 'select * from log_';
    $sql = $select . $startdate->format('Y-m');
    if ($startdate->format('Y-m') === $enddate->format('Y-m')) {
        return $sql;
    }

    while ($startdate <= $enddate) {
        $startdate = new DateTime($startdate->add(new DateInterval('P32D'))->format('Y-m-01'));
        $sql .= "\nunion all {$select}" . $startdate->format('Y-m');
        if ($startdate->format('Y-m') === $enddate->format('Y-m')) {
            return $sql;
        }
    }
}
?>

Result:

$ /c/php/php.exe test.php

select * from log_2015-01
union all select * from log_2015-02
union all select * from log_2015-03
union all select * from log_2015-04
union all select * from log_2015-05

You can use and tweak the function to your needs with the kind of date/time format you use.

You can even add value to this function should you desire to not just get all data from the log files but also data between certain start and end dates falling within the month.

Upvotes: 2

Related Questions