Solved

query multiple tables with same field names

Posted on 2009-05-19
21
406 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now