[SOLVED] How to combine results from multiple SQL queries into one

[ekestes]

This is probably pretty straightforward, but I'm lost.

 

I've got two mysql tables ("systems" and "changes") that are linked by the data in a third table ("links").  Each entry in the links table has a 'hostname' (each system has one of these) and a 'changeid' (each change has one of these).  I need to query all of the "changes" whose changeid is in the links table for a particular hostname.  The problem is, I need the results in a single result set so that I can pass them to another php function that displays them in a certain way.

 

I guess my 2 options are...

1)  figure out an SQL query that returns exactly what I need

2)  combine the results from multiple queries into one result set with php (is this possible?)

 

I was going down the road of option 2, because I know how to loop through the links table, get the 'changeid' values in an array, and then do a separate query for each one.  But I'm stuck from there.  Is it even possible to combine multiple query results into a single result set in php?  I was trying $result .= mysql_query($query), but that doesn't seem to give a valid MySQL resource.

 

What am I missing? 

 

<?php

// Get all change ID's for this particular hostname
$links = array();
$query = "SELECT changeid FROM links where hostname = '$hostname'";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
        $links[] = $row['changeid'];

// Get the changes for each ID in the array
foreach ($links as $link) {
       $query = "SELECT * from changes where id = '$link'";
       $result .= mysql_query($query) or die("Error: ".mysql_error());
}

$printresult = printChanges($result);

?>

[revraz]

If you are linking tables and they all have a foreign key, then you would use a JOIN function in SQL.

 

Display all your tables and their layouts.

[ekestes]

table1 = "changes"

contains 'id' (integer, auto-increment, primary key), and various long text fields

 

table2 = "systems"

contains 'id' (integer, auto-increment, primary key), 'hostname' (varchar[80]), and various long text fields

 

table3 = "links"

contains 'linkid' (integer, auto-increment, primary key), 'changeid' (integer, corresponds to id in table1), and 'hostname'(varchar[80], corresponds to hostnames in table2)

 

 

In case context is useful....

This is a simple change control system that I'm building.  The idea is that people will use it to document changes to our servers (there's also an email notification piece for communicating the changes among various groups).  A change could be applied to multiple hosts, so that's the purpose of the "links" table.  Right now I'm just trying to display all of the changes relevant to a particular system (hostname).

[ekestes]

Any ideas ?

[revraz]

It's easier if you don't repeat data.

 

So

 

changes

cid |  text

 

systems

sysid | hostname | text

 

links

linkid | cid | sysid

 

Now you can query the links table and you can do querries on the links and use JOIN to pull the Text from changes and Hostname from systems.

[rhodesa]

You should be able to use something like so:

 

SELECT * FROM links L LEFT JOIN systems S on L.hostname = S.hostname LEFT JOIN changes C ON L.changeid = C.id

 

But I agree with revraz. The links table should be using the ID from the systems table, not the hostname

[ekestes]

Thanks for the feedback.  I have restructured the tables as suggested......

 

changes

cid |  text

 

systems

sysid | hostname

 

links

linkid | cid | sysid

 

I'm getting close to constructing a query that works using LEFT JOINs.  But I'm getting a result for each link, when I only want a result for each change.  In other words, I want the main display to show distinct changes, and display something like "multiple hosts" when there is more than one link, and just display the single hostname when there is only one.  Here is my current query:

SELECT DISTINCT text,hostname from changes LEFT JOIN links on changes.id = links.changeid LEFT JOIN systems on links.sysid = systems.id;

 

 

I've tried altering it in several ways (with DISTINCT, and changing the order of the joins, etc).  But I can't quite get it.  Ideas?

 

thanks again

 

[ekestes]

Solution was:

 

SELECT text,hostname from changes LEFT JOIN links on changes.id = links.changeid LEFT JOIN systems on links.sysid = systems.id GROUP BY changes.id;