Database Output Help

Im Writing a Function that Searches 1 database called financial that selects all "id" numbers where the value of "name = income" then I take that result to run another query to search my banking database to select all my transactions with the account_id that = all the ids from the last query, but then I want to display "Income" and the sum amount. The problem is I cant do a join table method cause each table is in a different database. I hope that makes sense. Anybody have a simpler way to make this work?? here is my code below, Thanks Experts!!
function test()

echo '<table border=1>';

include ($_SERVER['DOCUMENT_ROOT'] . '/onlineoffice/config/config.php');

mysql_pconnect($host, $user, $pass) or die ('Unable To Connect To Database!');

mysql_select_db('finanical_schema') or die ('Unable To Select Database!');

$query = "SELECT id, name from shema_names WHERE type = 'Income' GROUP BY id";

$result = mysql_query($query) or die ('Error in Selection query: $query. ' . mysql_error());

while ($row = mysql_fetch_array($result))

		"id" => $row[0];
		"name" => $row[1];

	foreach ($data as $id)


		mysql_select_db('b_accounts') or die ('Cant Select Bank Database!');

		$sql = "SELECT account_id, sum(if(type='Withdrawl', -amount,amount)) FROM Banking WHERE account_id  IN  ('$id') GROUP BY account_id";

		$compute = mysql_query($sql) or die ('Error in Bank Query: $sql. ' . mysql_error());
			while ($display = mysql_fetch_array($compute, MYSQL_NUM))

				echo '<tr><td>'. $id['name'] . '<td><div align="right">' . $display[1] . '</div></td></tr>';

		echo '</table>';


Open in new window

Who is Participating?
merwetta1Connect With a Mentor Commented:
You should be able to do one query on both databases like this:

SELECT id, name,
account_id, sum(if(type='Withdrawl', -amount,amount))
from finanical_schema.shema_names, b_accounts.Banking
WHERE type = 'Income'
and account_id = id

If you have conflicting field names, you might have to put "<database name>.<table_name>." in front of field names (e.g. WHERE b_accounts.Banking.type = 'Income' )
Easyrider43Author Commented:
Wow that worked, in a much easier format also. I appreciate your Help very much! Thankyou
Easyrider43Author Commented:
He made my query more simple and faster and less complex. Im a very happy guy now. Thankyou!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.