MySQL – How to Perform a Query on a Dynamic List of Tables

MySQLPHPselect

I have a database named as ( conntrack ) with unknown amount of tables.I have a static table "tabidx" which has one column "date".

The following table is static and its name is tabidx which provide reference to other tables:

 tabidx (table):
 +------------+
 |    date    |
 +------------+
 | 2015-04-25 |
 | 2015-04-26 |
 | 2015-04-27 |
 | 2015-04-28 |
 | 2015-04-29 |
 +------------+

The following are example to the referenced tables in 'tabidx' 'date':

2015-04-25 (table):                             
+---------------------------------------------+    
| time      | username | srcip      | scport  |    
+---------------------------------------------+    
| 19:20:00  | L001000  | 10.10.10.1 |  1304   |   
| 19:20:00  | L001001  | 10.10.10.2 |  1640   |    
| 19:20:01  | L001002  | 10.10.10.3 |  2001   |    
+---------------------------------------------+ 

2015-04-26 (table):                             
+---------------------------------------------+    
| time      | username | srcip      | scport  |    
+---------------------------------------------+    
| 19:21:00  | L001000  | 10.10.10.1 |  1304   |   
| 19:21:00  | L001001  | 10.10.10.2 |  1640   |    
| 19:21:01  | L001002  | 10.10.10.3 |  2001   |    
+---------------------------------------------+

I would like to list to the manager the available tables that exists in tabidx and count how many users we have in all table if they are referenced in tabidx. So if the manager requested:

$_GET['fromdate'] = '2015-05-25'  
$_GET['todate'] = date(); //till today date 2015-04-30
$_GET['username'];
$_GET['srcport'];
$_GET['srcip'];

I would like the output to match mysql rows according to the requested above.
For example:

OUTPUT: [available tables WHERE username=L00100]
+-------------------------------------------+    
| Date and Time | username | srcip | scport |     
+-------------------------------------------+    
|   2015-04-25  | L001000  |   %   |   %    |   
|   2015-04-26  | L001000  |   %   |   %    |    
|   2015-04-27  | L001000  |   %   |   %    |    
+-------------------------------------------+

How can i list or select all tables that are found in the tabidx .? I dont know how to use prepared statements in mysql.

I know that the first step should be listing the available tables .
What if someone wants to requests the available tables that has specific user like "L002000" , then we should not list the tables at all because our dynamic tables dont have this username .

$db = new dB();

$_GET['fromdate'] = ( isset($_GET['fromdate']) && isValideDate($_GET['fromdate']) ) ?       $_GET['fromdate'] : $todayDate;
$_GET['todate'] = ( isset($_GET['todate']) && isValideDate($_GET['todate']) ) ?  $_GET['todate'] : $todayDate;
$sql_fromdate = " date >= '{$_GET['fromdate']}' AND";
$sql_todate = " date >= '{$_GET['todate']}'";

$result = $db -> query("SELECT date FROM tabidx WHERE $sql_fromdate $sql_todate");
$_GET['username'] = ( isset($_GET['username']) && $_GET['username'] !='' ) ?                           ....$_GET['username'] : '';


if($result)
{
    // Loop through the available tables that are included in table 'tabidx' 

    while( $b_row = $result -> fetch_assoc() )
    {
        $table_name = $db_row['date'];

        // Query the table and count the number of rows according to the requested $_GET Method

        $result2 = $b -> query("SELECT count(*) FROM $table_name WHERE username LIKE '{$_GET['username']}%'");
        $num_row = $result2 -> fetch_row();

        // Check if the number of rows are greater than zero
        // then show the row of $result to the user

        if($num_row[0] > 0)
        {
            echo '<tr>';
            echo '<td>$table_name</td>';
            echo "<td>{$_GET['username']}</td>";
            echo '<td></td>';
            echo '<td></td>';
            echo '</tr>';
        }
    }

}

I have successfully made a temporary solution, but what i know that this solution is so bad and I am not the one who created this structure. We might reach up to 360 tables during a year.

Can we just do the whole work using one mysql query.?

Best Answer

All of this could have been accomplished with the INFORMATION_SCHEMA database

The table names are in the INFORMATION_SCHEMA

SELECT table_name FROM information_schema.tables
WHERE table_schema='conntrack';

You could then line up the tables that exist against the tabidx table

SELECT B.table_name FROM (SELECT date FROM tabidx) A
INNER JOIN (SELECT table_name FROM information_schema.tables
WHERE table_schema='conntrack') B ON A.date = B.table_name;

With these table_names, setup a long query against all tables in conntrack

PROPOSED QUERY

SET @fromdate = '2015-04-25';
SET @todate = CURRENT_DATE();
SET @given_username='L001000';
SELECT
    GROUP_CONCAT(qry SEPARATOR ' UNION ')
    INTO @sql
FROM
(
    SELECT CONCAT('SELECT ',QUOTE(table_name),' as "Date and Time"',
    ',username,''%'' srcip,''%'' scport FROM `',table_name,
    '` WHERE username=',QUOTE(@given_username)) qry
    FROM
    (
        SELECT BB.table_name FROM
        (
            SELECT date FROM tabidx
            WHERE date>=@fromdate AND date<=@todate
        ) AA
        INNER JOIN
        (
            SELECT table_name FROM information_schema.tables
            WHERE table_schema='conntrack'
        ) BB ON AA.date = BB.table_name
    ) A
) B;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SAMPLE DATA (From Your Question)

DROP DATABASE IF EXISTS conntrack_sample;
CREATE DATABASE conntrack_sample;
USE conntrack_s
CREATE TABLE tabidx (date date, PRIMARY KEY (date));
INSERT INTO tabidx VALUES 
('2015-04-25'),('2015-04-26'),('2015-04-27'),
('2015-04-28'),('2015-04-29');
SELECT * FROM tabidx;
CREATE TABLE `0000-00-00`
(
    time time,username CHAR(7),
    srcip CHAR(15),scport INT,
    PRIMARY KEY (username,time)
);
CREATE TABLE `2015-04-25` LIKE `0000-00-00`;
CREATE TABLE `2015-04-26` LIKE `0000-00-00`;
CREATE TABLE `2015-04-27` LIKE `0000-00-00`;
CREATE TABLE `2015-04-28` LIKE `0000-00-00`;
CREATE TABLE `2015-04-29` LIKE `0000-00-00`;
INSERT INTO `2015-04-25` VALUES
('19:20:00','L001000','10.10.10.1',1304),
('19:20:00','L001001','10.10.10.2',1640),
('19:20:01','L001002','10.10.10.3',2001);
INSERT INTO `2015-04-26` VALUES
('19:21:00','L001000','10.10.10.1',1304),
('19:21:00','L001001','10.10.10.2',1640),
('19:21:01','L001002','10.10.10.3',2001);

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS conntrack_sample;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE conntrack_sample;
Query OK, 1 row affected (0.00 sec)

mysql> USE conntrack_sample
Database changed
mysql> CREATE TABLE tabidx (date date, PRIMARY KEY (date));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tabidx VALUES
    -> ('2015-04-25'),('2015-04-26'),('2015-04-27'),
    -> ('2015-04-28'),('2015-04-29');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tabidx;
+------------+
| date       |
+------------+
| 2015-04-25 |
| 2015-04-26 |
| 2015-04-27 |
| 2015-04-28 |
| 2015-04-29 |
+------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE `0000-00-00`
    -> (
    ->     time time,username CHAR(7),
    ->     srcip CHAR(15),scport INT,
    ->     PRIMARY KEY (username,time)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `2015-04-25` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `2015-04-26` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `2015-04-27` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `2015-04-28` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `2015-04-29` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `2015-04-25` VALUES
    -> ('19:20:00','L001000','10.10.10.1',1304),
    -> ('19:20:00','L001001','10.10.10.2',1640),
    -> ('19:20:01','L001002','10.10.10.3',2001);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `2015-04-26` VALUES
    -> ('19:21:00','L001000','10.10.10.1',1304),
    -> ('19:21:00','L001001','10.10.10.2',1640),
    -> ('19:21:01','L001002','10.10.10.3',2001);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @fromdate = '2015-04-25';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @todate = CURRENT_DATE();
Query OK, 0 rows affected (0.02 sec)

mysql> SET @given_username='L001000';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     GROUP_CONCAT(qry SEPARATOR ' UNION ')
    ->     INTO @sql
    -> FROM
    -> (
    ->     SELECT CONCAT('SELECT ',QUOTE(table_name),' as "Date and Time"',
    ->     ',username,''%'' srcip,''%'' scport FROM `',table_name,
    ->     '` WHERE username=',QUOTE(@given_username)) qry
    ->     FROM
    ->     (
    ->         SELECT BB.table_name FROM
    ->         (
    ->             SELECT date FROM tabidx
    ->             WHERE date>=@fromdate AND date<=@todate
    ->         ) AA
    ->         INNER JOIN
    ->         (
    ->             SELECT table_name FROM information_schema.tables
    ->             WHERE table_schema='conntrack_sample'
    ->         ) BB ON AA.date = BB.table_name
    ->     ) A
    -> ) B;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

PROPOSED QUERY OUTPUT

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---------------+----------+-------+--------+
| Date and Time | username | srcip | scport |
+---------------+----------+-------+--------+
| 2015-04-25    | L001000  | %     | %      |
| 2015-04-26    | L001000  | %     | %      |
+---------------+----------+-------+--------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

NOTE: Here is what @sql has

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT '2015-04-25' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-25` WHERE username='L001000' UNION SELECT '2015-04-26' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-26` WHERE username='L001000' UNION SELECT '2015-04-27' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-27` WHERE username='L001000' UNION SELECT '2015-04-28' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-28` WHERE username='L001000' UNION SELECT '2015-04-29' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-29` WHERE username='L001000'
1 row in set (0.00 sec)

mysql>
Related Question