Solved

query multiple tables with same field names

Posted on 2009-05-19
21
403 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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:racek
Comment Utility


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
Comment Utility

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
Comment Utility
I think you need to have a look at my posts...
0
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Forgot the txt file...
node-j-data.txt
0
 

Author Comment

by:peterbrowne
Comment Utility
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
Comment Utility
Don't worry worked it out....problem solved
0
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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…
This article discusses how to create an extensible mechanism for linked drop downs.
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 …

771 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

11 Experts available now in Live!

Get 1:1 Help Now