Solved

query multiple tables with same field names

Posted on 2009-05-19
21
413 Views
Last Modified: 2013-12-12
I have eight tables that have the same field names (timestamp, user_count), but hold distinct data.  How can I select the timestamp field in just one table (say, node_j_data) and the user_count field in all tables (e.g. node_a_data, node_b_data, node_c_data and so on...) as seperate entities, ie alias as the follow code suggests.

Thanks for any help!
<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$query = "SELECT node_j_data.timestamp AS timestamp, node_a_data.user_count AS users_a, node_b_data.user_count AS users_b, node_c_data.user_count AS users_c, etc...";
$result = mysql_query($query, $connection) or die(mysql_error());
 
 
while($row = mysql_fetch_assoc($result)){  
  $timestamp =  date('d-m-y H:i',$row['timestamp']);
  $users_a =  $row['users_a'];
  $users_b =  $row['users_b'];
  $users_c =  $row['users_c'];
   
  echo "$timestamp;$users_a;$users_b;$users_c\n";
} 
 
?>

Open in new window

0
Comment
Question by:peterbrowne
  • 10
  • 9
  • 2
21 Comments
 
LVL 14

Expert Comment

by:racek
ID: 24428851

select * from yourtable1
UNION ALL
select * from yourtable2
UNION ALL
select * from yourtable3
order by node_j_data.timestamp

Open in new window

0
 
LVL 9

Expert Comment

by:Sander Stad
ID: 24428861
You could do something like this:


SELECT
  j.timestamp AS timestamp,
  a.user_count AS users_a,
  b.user_count AS users_b,
  c.user_count AS users_c
FROM
  node_j_data j,
  node_a_data a,
  node_b_data b,
  node_c_data c
WHERE
  ............

Open in new window

0
 

Author Comment

by:peterbrowne
ID: 24428931
I tried the following query, but it times out (not to mention almost killing the server...)

SELECT node_j_data.timestamp AS timefield, node_b_data.user_count AS users_b, node_f_data.user_count AS users_f FROM node_j_data, node_b_data, node_f_data

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 9

Expert Comment

by:Sander Stad
ID: 24429400
How many rows do the tables have. The reason the server could timeout is because it has no indexes to search what makes the server go and search every field in the database.

Did you create any indexes on the tables? Assuming you use MySQL you can find more information about indexes in this website: http://dev.mysql.com/doc/refman/5.0/en/create-index.html


0
 
LVL 14

Expert Comment

by:racek
ID: 24429440
SELECT node_j_data.timestamp AS timefield,
       node_b_data.user_count AS users_b,
       node_f_data.user_count AS users_f
FROM node_j_data j, node_b_data b, node_f_data f
WHERE j.timestamp = b.timestamp and b.timestamp = f.timestamp
0
 

Author Comment

by:peterbrowne
ID: 24439087
There are 8 tables...same field names...all data is different (the tables are populated every 15 minutes from 8 different node usage logs) including the timestamps.  Yes, the tables all have indexes and return all data no problem for for each table when the query is specific to a table.  However, I need to have a single query which returns all the data from all 8 tables.  The query must feed 8 different variables for user_count for each table.
0
 
LVL 14

Expert Comment

by:racek
ID: 24439239
like this???
SELECT 
(SELECT COUNT(*) FROM node_a_data WHERE timestamps>='2008-01-01' ) AS t1count,
(SELECT COUNT(*) FROM node_b_data WHERE timestamps>='2008-01-01' ) AS t2count,
(SELECT COUNT(*) FROM node_c_data WHERE timestamps>='2008-01-01' ) AS t3count,
(SELECT COUNT(*) FROM node_d_data WHERE timestamps>='2008-01-01' ) AS t4count;

Open in new window

0
 

Author Comment

by:peterbrowne
ID: 24439304
No, I just want return timestamp (just 10 digit numbers) for all rows in one table and user_count for all trows for all 8 tables (no count involved and no WHERE clause).  E.g.:

"SELECT node_j_data.timestamp AS timestamp, node_a_data.user_count AS users_a, node_b_data.user_count AS users_b, node_c_data.user_count AS users_c, node_d_data.user_count AS users_d, node_e_data.user_count AS users_e,  node_f_data.user_count AS users_f. node_i_data.user_count AS users_i,  node_j_data.user_count AS users_j"

However when I run that query, I get a MySQL error "unknown table node_j_data" etc.  As already stated, querys on individual tables work fine, e.g.

"SELECT * FROM node_j_data"
0
 

Author Comment

by:peterbrowne
ID: 24439351
To clarify, the tables have 3 columns, eg,:

node_a_data_id

timestamp (which is a 10 digit number, no format)

user_count (i.e. the number of users online for on that server node for that timestamp)
0
 

Author Comment

by:peterbrowne
ID: 24439370
timestamp for one table needs to go into $timestamp.  Then user_count for each table needs to go into:

$users_a =  $row['users_a'];
$users_b =  $row['users_b];
$users_c  =  $row['users_c'];
$users_d =  $row['users_d'];
$users_e =  $row['users_e'];
$users_f  =  $row['users_f'];
$users_i =  $row['users_i'];
$users_j =  $row['users_j'];

This is done in the while loop as first posted...

0
 
LVL 14

Expert Comment

by:racek
ID: 24439414


SELECT t.ts,
            count(a.timestamp) as acount,
            count(b.timestamp) as bcount,
            count(c.timestamp) as ccount,
            count(d.timestamp) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp;


(SELECT COUNT(*) FROM node_b_data WHERE timestamps>='2008-01-01' ) AS t2count,
(SELECT COUNT(*) FROM node_c_data WHERE timestamps>='2008-01-01' ) AS t3count,
(SELECT COUNT(*) FROM node_d_data WHERE timestamps>='2008-01-01' ) AS t4count;
0
 
LVL 14

Expert Comment

by:racek
ID: 24439418

SELECT t.ts,
            count(a.timestamp) as acount,
            count(b.timestamp) as bcount,
            count(c.timestamp) as ccount,
            count(d.timestamp) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp;

Open in new window

0
 

Author Comment

by:peterbrowne
ID: 24439482
I think you need to have a look at my posts...
0
 
LVL 14

Expert Comment

by:racek
ID: 24439533
try to execute my query and you can understand it :-) result will be exactly what are you asking for: timestamp and count from each table for this timestamp ...

timestamp               acount   bcount   ccount
2008-01-01 15:30        0           55         0
0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 24439566
it should be sum not count - if you have only one row per timestamp, you don't need sum :-) but it doesn't matter ... group by at the end...
SELECT t.ts,
            sum(a.user_count) as acount,
            sum(b.user_count) as bcount,
            sum(c.user_count) as ccount,
            sum(d.user_count) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp
GROUP BY 1;

Open in new window

0
 

Author Comment

by:peterbrowne
ID: 24446765
I don't know why you are looking at count or sum. The data returned needs to be all records as is.  Have a look at the attached node_j_data.txt which is an output from table node_j_data.  This is what the output from the combined table query needs to look like, except where there is, for example:

25-04-09 00:15;78   (this is the timestamp and user_count for one record in node_j_data)

needs to be:

25-04-09 00:15;78;88;102;55;67;78;34;47

which has just the one timestamp from one table and then the user_count column for each of the other tables.

This data is then repeated for all rows in all tables (in the while loop) as you can see in the txt file (except that is just for one table).  See code snippet again for the code used to get the data as per the txt file from node_j_data.


<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$query = "SELECT * FROM node_j_data";
$result = mysql_query($query, $connection) or die(mysql_error());
 
while($row = mysql_fetch_assoc($result)){  
  $timestamp =  date('d-m-y H:i',$row['timestamp']);
  $users =  $row['user_count'];
 
  echo "$timestamp;$users\n";
} 
?>

Open in new window

0
 

Author Comment

by:peterbrowne
ID: 24446771
Forgot the txt file...
node-j-data.txt
0
 

Author Comment

by:peterbrowne
ID: 24447216
OK, I have results in the format that I need with the following code.  However, now I have another problem. The timestamps for node_j_data start several hours before the other other tables timestamp, and none of the tables timestamps would actually be in sinc if overlayed in a graph (which is what I am doing).  

I think that the only answer to this problem is to set the tables in the database back to a common starting point and somehow reset the auto increment...can you tell me how to do this for the 500 points (resetting the auto increment to start again after I delete rows from the beginning of each table so that they start from the same time (or close to it))?

Thanks
<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$query = "SELECT node_j_data.timestamp AS timefield, node_b_data.user_count AS users_b, node_f_data.user_count AS users_f FROM node_j_data, node_b_data, node_f_data WHERE node_b_data_id = node_j_data_id AND node_f_data_id = node_j_data_id";
$result = mysql_query($query, $connection) or die(mysql_error());
 
while($row = mysql_fetch_assoc($result)){  
  $timestamp =  date('d-m-y H:i',$row['timefield']);
  $users_b =  $row['users_b'];  
  $users_f =  $row['users_f']; 
  echo "$timestamp;$users_b;$users_f\n";
} 
 
?>

Open in new window

0
 

Author Comment

by:peterbrowne
ID: 24447580
Don't worry worked it out....problem solved
0
 
LVL 14

Expert Comment

by:racek
ID: 24448237
If you should provide table saying that timestamp is the unique key, then will be query without disscussion as follows:-)
SELECT t.ts,a.user_count  as acount,b.user_count  as bcount,c.user_count  as ccount,d.user_count ) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp;

Open in new window

0
 
LVL 14

Expert Comment

by:racek
ID: 24448348
Autoincrement can be changed easy with

create table newtab (node_a_data_id int not null auto_increment,
timestamp ... user_count ...);

insert into newtab select 0,timestamp, user_count
 from oldtab order by autincrement;

drop oldtab;
rename netab to oldtab ;
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question