1

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.?

2
  • Well, what strikes me is that you should not have a different table for each date. I think that your question needs to be simplified down to a database one - then worry about translating into ?Perl. If you have two tables, life will be much simpler than if you have 365 (+ 1 (tabidx)(. Apr 30, 2015 at 20:28
  • @Vérace I have built account manager for WISP . The connection tracker (not the one i made) is an external program which connects to the 'conntrack' database, creates a table according to the day we are and then insert thousands of records each 5 minutes . my job is to list them to the user according to the php form that requests certain available dates.
    – Ghassan
    Apr 30, 2015 at 21:15

1 Answer 1

5

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>
5
  • Hello, thanks for your quick reply. How to use this query using php ?.
    – Ghassan
    May 1, 2015 at 0:09
  • PROPOSED QUERY using php . As I know querying variables in php need multi query (multi call).
    – Ghassan
    May 1, 2015 at 0:10
  • Hello again . I tried your query . It works if I specify username then it shows one row or if nothing found then it wont show which is great.It has a problem .. If I change username to LIKE '%' which I meant to show one row even if have many rows . I want to show just one row as date if anything found incase username or srcport , srcip. .
    – Ghassan
    May 1, 2015 at 15:28
  • ok I fixed it by adding LIMIT 1 to each query . Now I can use 2 calls to MySQL through php instead many calls . I am working on solving more arguments with php to avoid getting errors when someone plays with $_GET variables.
    – Ghassan
    May 1, 2015 at 15:46
  • This gave me idea how to solve my issue. Thanks. Note: When using GROUP_CONCAT, be aware that result is limited by size. This helped "SET SESSION group_concat_max_len = 99999999;"
    – Nemke
    Feb 19, 2019 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.