How to create a single recordset/array from two results? 1

[Apollo6]

I've got two databases, same schema structure, basically company A and company B. I can get the data I want from each individually but need to combine the results of identical queries then echo it out on the page, i.e an employee directory that list employees from both companies but all in one page.

I have the following code from company A:

$A_query = "SELECT employee_name
FROM
UPEMPL";

$A_result = odbc_exec($connect_companyA, $A_query);
$A_rows = odbc_fetch_row($A_result);

I have the following code from company B:

$B_query = "SELECT employee_name
FROM
UPEMPL";

$B_result = odbc_exec($connect_companyB, $B_query);
$B_rows = odbc_fetch_row($B_result);

How can I get the results of both into a single result to work with? Another twist with this is that it could be possible that an employee could be in both companies so I would only want to see that employee one time in the final result.

Any suggestions or direction would be great.

 

[jpadie]

parse both recordsets into an array.

$A_query = "SELECT employee_name
    FROM
        UPEMPL";

    $A_result = odbc_exec($connect_companyA, $A_query);
    while ($A_rows = odbc_fetch_row($A_result)){
$employees[] = $A_rows;
}
$B_query = "SELECT employee_name
    FROM
        UPEMPL";

    $B_result = odbc_exec($connect_companyB, $B_query);
while ($B_rows = odbc_fetch_row($B_result)_{
  $employees[] = $B_rows;
}

then dedupe the array on whatever key you want.

 

[sleipnir214]

Does your database system support unions? You might just combine the two queries into one with a union and fetch one set of data.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[Apollo6]

Thanks jpadie. I was thinking this was what was going to be the answer. As far as looping through the $employees[] to find dups, is that a specific PHP function that I can read up on how to use. I am guessing that once I start loading in $B_rows (maybe not), I need to be checking at that point for the dups and to skip them.

 

[Apollo6]

sleipner214... I thought about this but couldn't get the syntax to work, specifically when you call odbc_exec. I need to execute against both databases. ???

 

[sleipnir214]

Insufficient data for a meaningful answer.

I do not know which database system your script is communicating with. Your database system may not support that type of query at all.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[jpadie]

you could implicitly deduplicate by building the combined array with a key that was the same as the key on which you were deduplicating.

e.g.
$A_query = "SELECT employee_name
FROM
UPEMPL";

$A_result = odbc_exec($connect_companyA, $A_query);
while ($A_rows = odbc_fetch_row($A_result)){
$employees[$A_rows['key']] = $A_rows;
}
[/code]

as the array is associative now, later additions with identical keys will overwrite the prior entries.

 

[Apollo6]

sleipnir214... Sorry about that, I am going against a PervasiveSQL database. From what I've Googled, it appears to support UNION queries. Again, I just don't understand how the syntax would be. Would it be something like:

$query = "SELECT employee_name FROM companyA.UPEMPL
UNION
SELECT employee_name FROM companyB.UPEMPL";

$result = odbc_exec($connect_???, $query);
$rows = odbc_fetch_row($result);

If I have two connections, companyA and companyB, how would I excecute the query for both at the same time?

 

[Apollo6]

jpadie... Good info, I will try and post back. Also interested in the UNION approach as it would seem to be more efficient, a single execution to get the dataset to work with.

 

[jpadie]

i'd be interested to know if UNION works across two separate database connections to different servers. i had always thought they must be across two tables in the same db. would be useful if it were possible tho.

 

[Apollo6]

Actually, with PervasiveSQL, it is much like MSSQL, it has a Manager that you can have multiple databases on the same server. In my case, both databases live on the same server. I have done UNION queries plenty but within the same database so I am not sure it can be done across two databases, i.e. when the script goes to execute, how would it know to execute against both...

 

[jpadie]

exactly.

 

[Itshim]

I know nothing about PervasiveSQL; but in MySQL, if you have two different databases on the same server you connect to the server, then when you list the tables in the FROM clause you prefix the tables with the db name where that tables resides, such as:

SELECT employee_name
FROM db_name_1.UPEMPL
UNION
SELECT employee_name
FROM db_name_2.UPEMPL

This will return one column 'employee_name' with values from both tables. I don't know if this will work on your db, but you could give it a try.

 

[Apollo6]

Itshim... Going from your idea, the following worked.

$query = "SELECT DISTINCT altaddr2 FROM companyA.UPEMPL
			 UNION
			 SELECT DISTINCT altaddr2 FROM companyB.UPEMPL";

	$result = odbc_exec($connect_companyA, $query);
	$rows = odbc_fetch_row($result);

I still don't quite understand how it resolves the connection specifically directed at companyA to apply to companyB but it does work.

Thanks to all for input on this.

 

[Itshim]

Speaking from a MySQL viewpoint... When you connect to a database server, you login to the server, not a specific database. You can then issue a 'USE' statement to set the default database (normally termed 'current' db) for the connection. This does not 'bind' the connection to that database; it simply tells the server 'if not noted use the this db'.

You do not have to select a default db for a connection you would just need to prefix all your tables with the db name. You can also change the default database for a connection, by issuing another 'USE' statement on the same connection. Basically the 'USE' statement is convenient to the programmer.

One thing to keep in mind is that the user you login to the server with will need to have the appropriate permissions for all the databases referenced in your statements.

Hope this makes sense, I have yet to get my caffeine levels up.