Multiple Table Query - Help :)

[tpupaz]

Ok so here's the problem, I'm building a query that will output data obv heh. Here's my layout and my idea.

Each table has a common id that being "machine" field. So that much is good.

Table Hard Drive

id---------machine--------available---------total---------datetime

Table Memory

id---------machine--------available---------total----------datetime

Table Load

id---------machine---------5avg---------10avg---------datetime

We have a script that updates these every 10 minutes , hence forth the datetime function in each table.

What I had planned on doing was a multi table select and then output them for each one and have them go that way. We have 10 machines so what I would want the output to look like would be.


Output Sample::

Machine: Fred
HDD Avail: 50 GB
HDD Total: 120 GB
Memory Avail: 45 MB
Memory Total: 120 MB
0AVG: .95
5AVG: 22
15AVG: 9.6

Machine: Joe
etc etc etc

I thought about having it do a for loop and to increment the machine number so it would cover all the machines, but my question is how do I get it to pull the latest one from the datetime function at the same time.

My original idea was to have a select that ordered by datetime desc and then limited to one but that wont help with the output and would have to have me make 12 diff queries. I know theres an easier way but would like some input please. thanks

T

[Barand]

Question - as they are updated every 10 mins will all three have a matching timestamp or does it take more than 1 sec to update the 3 tables?

[tpupaz]

they will not have matching time stamps...

all the tables have the same machine column, so they share that.

Here's the query i'm working on right now but its not working

$query = "SELECT cpu.idle, hdd.avilable, hdd.total, load.0avg, load.5avg, load.15avg, memory.machine, memory.available, memory.total, memory.dt" .
"FROM cpu, hdd, load, memory WHERE memory.machine = hdd.machine AND memory.machine = load.machine AND memory.machine = cpu.machine ORDER BY memory.dt LIMIT 1";
$result = mysql_query($query);

[Barand]

I think I'd take the easy route and do 3 queries, one on each table, each getting the latest data for each machine and write the data to an array keyed by machine

[tpupaz]

can you show me what you mean by having them keyed by machine....

we have 10 machiens, so i could write the 3 queries to pull the data from the 3 machines (tables)

then i'd have all this info queried and then have to sort them to gert 10 outputs

so for each machine i'd have the output...

thanks in advance tony

[Barand]

OK you can pretty-up the output by putting it in a table and formating date/time but this is the basics
[code]
<?php
include 'db.php';

$data = array();

//
//  get hard disk data
//
$sql = "SELECT a.machine, a.available, a.total, a.dt
    FROM `hard_drive` a
    WHERE a.dt = (SELECT MAX(b.dt)
            FROM `hard_drive` b
            WHERE b.machine = a.machine)
    ORDER BY a.machine";
$res = mysql_query($sql) or die(mysql_error());
while (list($mac, $avail, $tot, $dt) = mysql_fetch_row($res)) {
    //
    //  store in array
    //
    $data[$mac]['dt'] = $dt;
    $data[$mac]['hd_avail'] = $avail;
    $data[$mac]['hd_total'] = $tot;
}

//
//  get memory data
//
$sql = "SELECT a.machine, a.available, a.total
    FROM `memory` a
    WHERE a.dt = (SELECT MAX(b.dt)
            FROM `memory` b
            WHERE b.machine = a.machine)";
$res = mysql_query($sql) or die(mysql_error());
while (list($mac, $avail, $tot) = mysql_fetch_row($res)) {
    //
    //  store in array
    //
    $data[$mac]['mem_avail'] = $avail;
    $data[$mac]['mem_total'] = $tot;
}

//
//  get load data
//
$sql = "SELECT a.machine, a.5avg, a.10avg
    FROM `load` a
    WHERE a.dt = (SELECT MAX(b.dt)
            FROM `load` b
            WHERE b.machine = a.machine)";
$res = mysql_query($sql) or die(mysql_error());
while (list($mac, $av5, $av10) = mysql_fetch_row($res)) {
    //
    //  store in array
    //
    $data[$mac]['av5'] = $av5;
    $data[$mac]['av10'] = $av10;
}

//
//    putput the array
//

foreach ($data as $mac => $mdata) {
    echo "<h3>Machine $mac</h3>";
    foreach ($mdata as $k => $val) {
        echo "$k : $val<br>";
    }
}
?>[/code]

[tpupaz]

So here's the code I wrote today and when I run this, it is able to pull the machine1 info and it spits it out nicely.

I guess my question is - based off the queries, can I merge them into one maybe and then run a for loop that would increment machine and output the information there? My theory is that if I can merge all the data I query, I can then run a for loop , where $machine = 1 ; $machine <=10; $machine++; and that would run the loop 10 times and get the latest data for each machine so that its up to date. So the output would look like.

Machine 1
HDD Total: 446345556
HDD Available: 4323455634
0 avg: 4353
5 avg: 5450
15 avg: 3425
Memory Available: 345324
Memory Total: 454235
Idle Time: .093

Machine 2
HDD Total: 43534243
etc etc etc

Also I would have to add a rule in my loop that if $machine = 1 , then $machine = (text name - like Zeus)
So it would show up as , Machine : Zeus instead of Machine : 1

Am I on the right path or can anyone provide constructive criticism in the way of helping me finish this out. Thanks much and its appreciated.

I also realize that I'd have to redo the query so that it wold not serch just for machine = 1, but for all machines. I would just change the limit to 10 so I'd pull the top 10 by datetime , which would be the latest 10 to be updated (which the script would do).

[code]
include 'config.php';

$db = mysql_connect("$dbhost", "$dbuser", "$dbpass", $dbname);
$dbname = mysql_select_db($dbname);

$query  = "SELECT machine, available, total, dt FROM memory WHERE machine = 1 ORDER BY dt desc LIMIT 1";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
    echo "Machine :{$row['machine']} <br>" .
        "Available : {$row['available']} <br>" .
        "Total :{$row['total']} <br>" .
"Date/Time : {$row['dt']}<br><br>";
}

$query_cpu = "SELECT machine, idle, dt FROM cpu WHERE machine = 1 ORDER by dt desc LIMIT 1";
$cpuresult = mysql_query($query_cpu);

while($row = mysql_fetch_assoc($cpuresult))
{
    echo "Machine :{$row['machine']} <br>" .
        "Idle : {$row['idle']} <br>" .
      //  "Total :{$row['total']} <br>" .
"Date/Time : {$row['dt']}<br><br>";
}

$query_load = "SELECT machine, 0avg, 5avg, 15avg, dt FROM `load` WHERE machine = 1 ORDER by dt desc LIMIT 1";
$loadresult = mysql_query($query_load);

while($row = mysql_fetch_assoc($loadresult))
{
    echo "Machine :{$row['machine']} <br>" .
        "0 avg : {$row['0avg']} <br>" .
        "5 avg :{$row['5avg']} <br>" .
        "15 avg :{$row['15avg']} <br>" .
"Date/Time : {$row['dt']}<br><br>";
}

$query_hdd = "SELECT machine, available, total FROM hdd where machine = 1 ORDER by dt desc LIMIT 1";
$hddresult = mysql_query($query_hdd);

while($row = mysql_fetch_assoc($hddresult))
{
    echo "Machine :{$row['machine']} <br>" .
        "HDD Available : {$row['available']} <br>" .
        "HDD Total :{$row['total']} <br>";

}
[/code]

[emehrkay]

SELECT (do not use * here because you have a lot of fields with the same name. if you wanted to specify a field from a certain table just use the alias as the prefix ie hd.machine)
FROM
hard_drive hd
INNER JOIN
memory m ON hd.machine = m.machine
INNER JOIN
load l ON hd.machine = l.machine
WHERE (you may not need this and if you use it, it may return only one row)
hd.machine = XXX

from this query i would do

$query = WHAT I POSTED ABOVE
$result = mysql_query($query) or die("<h2>THERE IS A PROBLEM WITH THE QUERY</h2>".$query."<br />\nERROR: ".mysql_error());
$num = mysql_num_rows($result);
for($i = 0; $i < $num; $i++){
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$mac  = "Machine ". ++i ."<br />\n";
$mac .= "HDD Total: ".$row['avail'] ."<br />\n";
etc...
echo $mac;

}

but to use the assocative array, you need to should create aliases in your select statement
select hd.available AS hdAvail
and then use that alias hdAvail as the key in the array - $row['hdAvail']
if you're having trouble at this point, inside the for loop, just print_r($row) and see what it gives you