[SOLVED] Quering Multiple Databases for a single field.

[Errant_Shadow]

MySQL Server Version: 5.1.30

 

Okay, what I've got is an array of databases for a ring of sites I'm designing. They are similar in a few ways but since the sites must be kept separate for accounting purposes, I'm keeping the databases separate as well (this is my bosses call, so that much is concrete).

 

What I need to do is fetch data from these individual databases ONLY when this field is set correctly. The field name is the same in all of them, and they are all foreign keys for the same field in a master database used across all sites for registration. The actual table itself is named the same as well, the only difference (in naming at least), is the database name.

 

So what I need to know is if there's an easy way to query across different databases for the same field in the same table.

[Errant_Shadow]

Actually... I'm gonna go ahead and approach this an entirely different way.

 

An answer would still be nifty for future reference, but no rush *nods*

[fenway]

I don't follow... could you give an example?

[DavidAM]

To reference a table in a database you qualify the table name with the database name.

 

SELECT ID, Name FROM database.tablename WHERE ID = 3

 

 

[Errant_Shadow]

I'll try to be as simple as I can in my description.

 

Basically, I have 4 databases; for simplicity's sake we'll call them `global`, `red`, `blue`, and `green`.

 

`global` has a table called `accounts`, with `id` as it's primary field.

 

Now, `red`, `blue`, and `green` each have a table named `subscriptions`, with `id` as their primary key and `account_id` as a foreign key that references `global`.`accounts` (`id`).

 

Let's say your account id is 3, and you wanna see a list of all of your subscriptions. What I need to do is have my script fetch ALL the data held in the `subscription` table from any database where `account_id` matches a given `id` from the `accounts` table in the `global` database.

 

So I need to query all of the databases for the same field in the same table for a single value, and if that value is true, I need all of the data.

 

 

[Errant_Shadow]

would it just be like this?

SELECT * 
FROM `red`.`subscriptions`, `green`.`subscriptions`, `blue`.`subscriptions` 
WHERE `account_id` = "$num" 

 

... or would I need to describe each separate database in the WHERE clause?

 

like this?

WHERE `red`.`subscriptions`.`account_id` = "$num" 
OR `green`.`subscriptions`.`account_id` = "$num" 
OR `blue`.`subscriptions`.`account_id` = "$num" 

[DavidAM]

Ignore the fact that the tables are in separate databases for a moment, and design a query to get the data from the separate tables, then you can add the database qualifications.  The first query you suggest:

 

SELECT *
FROM `red`.`subscriptions`, `green`.`subscriptions`, `blue`.`subscriptions`
WHERE `account_id` = "$num" 

is  not going to work, because "account_id" (in the WHERE clause) is ambiguous; that is, it exists in more than one table and has not been qualified to indicate which table should be used.

 

The second WHERE clause is closer:

SELECT *
FROM `red`.`subscriptions`, `green`.`subscriptions`, `blue`.`subscriptions`
WHERE `red`.`subscriptions`.`account_id` = "$num"
OR `green`.`subscriptions`.`account_id` = "$num"
OR `blue`.`subscriptions`.`account_id` = "$num" 

since you have now qualified the table names, but using OR without JOINing the tables will result in a cartesian product; that is, the specified row in the first table matched to EVERY row in the second table matched to EVERY row in the third table.  And then the specifed row in the second table matched to EVERY row in the first table matched to EVERY row in the third table.  And then, the same for the third table.  I ran this query on a table with 6 rows and received 91 rows in the resultset!

 

Using AND would be much closer to the desired result:

SELECT *
FROM `red`.`subscriptions`, `green`.`subscriptions`, `blue`.`subscriptions`
WHERE `red`.`subscriptions`.`account_id` = "$num"
AND `green`.`subscriptions`.`account_id` = "$num"
AND `blue`.`subscriptions`.`account_id` = "$num" 

assuming that there is an entry in each table with the specifed account_id.  But if any one table does not contain the specified value, you get nothing.  An outer JOIN could help here, but will get complicated with more than two tables.  Also, using a single SELECT, will create a wide row with data from ALL THREE tables across, not a list.

 

I think the best approach would be to use a UNION:

SELECT 'Red' AS Source, R.*
FROM red.subscriptions AS R
WHERE R.account_id = "$num"
UNION
SELECT 'Green', G.*
FROM green.subscriptions AS G
WHERE G.account_id = "$num"
UNION
SELECT 'Blue', B.*
FROM `blue`.`subscriptions` AS B
WHERE B.account_id = "$num" 
ORDER BY 1

Which will return one row from each table if it is present.  I added the literals ('Red', 'Green', 'Blue') as "Source"  to the SELECT so you can tell which database/table a particular row came from if need be.  That could be anything, or nothing at all if you don't need it.

 

Of course, for the union to work properly, the columns returned by each SELECT must be the same.  The column names from the first select will be returned (you can see I did not use "AS Source" in the subsequent SELECTs).  And these columns should be the same data type throughout.  The ORDER BY 1 indicates that the first column in the returned result is to be used to sort the results.  You can use column names from the first SELECT (i.e. ORDER BY Source) to accomplish this as well.

 

Each query in the UNION should be sufficient to stand on its own.  So if you need additional data from the "globals" database for each row, you can add a join in each of the SELECTs to accomplish this:

SELECT 'Red' AS Source, R.*, M.MoreData
FROM red.subscriptions AS R JOIN globals.subscriptions AS M ON R.account_id = G.account_id
WHERE R.account_id = "$num"
UNION
SELECT 'Green', G.*, M.MoreData
FROM green.subscriptions AS G JOIN globals.subscriptions AS M ON R.account_id = G.account_id
WHERE G.account_id = "$num"
UNION
SELECT 'Blue', B.*, M.MoreData
FROM `blue`.`subscriptions` AS B JOIN globals.subscriptions AS M ON R.account_id = G.account_id
WHERE B.account_id = "$num"
ORDER BY M.MoreData, Source

I had to use "M" as the alias for the globals database/table since I already used "G" for green.

 

[Errant_Shadow]

What if the rows aren't all the same? Each subscription will contain different data, and even different data types, there are a few common denominators, but the bulk of each table will be unique.

 

Would I be better off just running separate queries for simplicity's sake?

[DavidAM]

Separate queries might be the easiest solution.  You can still use a UNION with different data, by supplying null fields in the appropriate place of each query and then looking at only those fields of interest in the result:

 

Example:

Table red.subscriptions

• ID
  
• Name
  
• Title
  
• StartDate
  
• ExpireDate
  

 

Table blue.subscriptions

• ID
  
• Title
  
• PaidDate
  
• IssueCount
  

 

SELECT 'Red' as Source, R.ID, R.Name, R.Title, R.StartDate, R.ExpireDate, NULL as PaidDate, NULL as IssueCount
FROM red.subscriptions AS R 
WHERE R.ID = 4
UNION
SELECT 'Blue' as Source, B.ID, NULL, B.Title, NULL, NULL, B.PaidDate, B.IssueCount
FROM blue.subscriptions as B
WHERE B.ID = 4

I think this will make the code more difficult to follow when it comes to maintaining it.  It would probably be best to use separate queries, even if you store them in a single array:

 

Note: This is an example, it is not intended to replace your code.  Use it to develop code that fits your process.

$ID = 4
$Data = array();
// Get subscriptions from "red"
$res = mysql_query("SELECT 'Red' AS Source, R.* FROM red.subscriptions AS R WHERE R.ID = " . $ID);
if ($res) {
  while ($row = mysql_fetch_assoc($res)) {
    $Data[] = $row;
  }
}

// Get subscriptions from "blue"
$res = mysql_query("SELECT 'Blue' AS  Source, B.* FROM red.subscriptions AS B WHERE B.ID = " . $ID);
if ($res) {
  while ($row = mysql_fetch_assoc($res)) {
    $Data[] = $row;
  }
} 

 

This should produce something along the lines of:

$Data = array (
  [0] => array ('Source'     => Red,
                'ID'         => 4, 
                'Name'       => John Doe,
                'Title'      => PHP Weekly, 
                'StartDate'  => 2009-10-01, 
                'ExpireDate' => 2010-09-30 ),
  [1] => array ('Source'     => Blue,
                'ID'         => 4,
                'Title'      => PHP Weekly,
                'PaidDate'   => 2008-09-12,
                'IssueCount' => 12 )
)

 

While the UNION example would produce something very similar but with empty fields in each:

$Data = array (
  [0] => array ('Source'     => Red,
                'ID'         => 4, 
                'Name'       => John Doe,
                'Title'      => PHP Weekly, 
                'StartDate'  => 2009-10-01, 
                'ExpireDate' => 2010-09-30,
                'PaidDate'   => ,
                'IssueCount' => ,),
  [1] => array ('Source'     => Blue,
                'ID'         => 4,
                'Name'       => ,
                'Title'      => PHP Weekly,
                'StartDate'  => , 
                'ExpireDate' => ,
                'PaidDate'   => 2008-09-12,
                'IssueCount' => 12 )

[Errant_Shadow]

I see.

so, to save on lines and make it all easier to update each query...

 

$ID = 4
$_SCRIPS = array();
$myDatabases = array('red','blue');

for ($i = 0; $i < count($myDatabases); $i++)
{
$result = mysql_query("SELECT * FROM $myDatabases[$i].subscriptions AS SCRIP WHERE SCRIP.ID = $ID");

if ($result)
{
	while ($row = mysql_fetch_assoc($result))
	{
		$_SCRIPS[$myDatabases[$i]][] = $row; // would this need that extra [] to work right?
  		}
}
}

 

might come out something like...

 

$_SCRIPS = array
(
['RED'] => array
(
	'ID'		=> 4,
	'Name'		=> John Doe,
	'Title'		=> PHP Weekly,
	'StartDate'	=> 2009-10-01,
	'ExpireDate'	=> 2010-09-30
),

['BLUE'] => array
(
	'ID'		=> 4,
	'Title'		=> PHP Weekly,
	'PaidDate'	=> 2008-09-12,
	'IssueCount'	=> 12
)
)

 

So not only would I be able to change the script and effect each query, I could aslo do something like:

if (isset($_SCRIPS['RED']['ID'])) { do something... }

[DavidAM]

while ($row = mysql_fetch_assoc($result))
{
$_SCRIPS[$myDatabases[$i]][] = $row; // would this need that extra [] to work right?
}

Yes, you need that extra [] at the end to add the $row array to the [db] array. 

 

As a result, the output will have one additional level from your sample:

$_SCRIPS = array
(

['red'] => array
(
	[0] => array
	(
		'ID'		=> 4,
		'Name'		=> John Doe,
		'Title'		=> PHP Weekly,
		'StartDate'	=> 2009-10-01,
		'ExpireDate'	=> 2010-09-30
	)
),

['blue'] => array
(
	[0] => array
	(
		'ID'		=> 4,
		'Title'		=> PHP Weekly,
		'PaidDate'	=> 2008-09-12,
		'IssueCount'	=> 12
	)
)
)

 

If more than one row is returned from the query then [ 0 ] will be [ 1 ] for the second record and so on.  If you KNOW for a fact, that there will be only 1 record from each query, you can leave off the extra [] and get the output as you suggested in your post (without the extra level).  Just be aware that $_SCRIPS['blue'] will not exist if no rows were returned for that query.

 

If there is to be only one record (because ID is the primary key and there can not be more than one) or if you only want the first record, you could eliminate the loop and do something along these lines:

	if ($result)
{
	$_SCRIPS[$myDatabases[$i]] = mysql_fetch_assoc($result);
}

mysql_fetch_assoc will return false if there is no row.  So then you can check it like this:

if ($_SCRIPS['blue'] !== false) {
    // Process data from BLUE
    }

 

Also note, the $myDatabases array values are in lower-case so your resulting array keys will be lower-case.

[Errant_Shadow]

Cool. That's pretty much exactly what I'm after; fetching all current subscriptions, and doing something for each existing subscription.

 

As a side question, what's the difference between "!=" and "!==" and when would it be appropriate to use one over the other? Like, I know "!=" means "NOT EQUAL TO", so what additional limitations does "!==" include?

[DavidAM]

The "!==" can be thought of as NOT EXACTLY THE SAME AS.  It checks the datatypes as well as the value.  For instance; the number zero (0) evaluates to false in a test.  So if you set "$var = 0", then test it "if ($var == 0)" the IF is true.  If you set "$var = false", and test "if ($var == 0)" the IF is also true.  However, "if ($var === 0)" [note three equal signs], then it is false, because the datatypes are different (integer vs. boolean).  See the PHP manual on comparison operators (http://us3.php.net/manual/en/language.operators.comparison.php)

 

This is especially handly with the strpos() function.  This function will return false if the value is not found.  If it finds the value, it returns the offset position.  So, if the value is found at the first character, it returns 0 (zero).  An equal test "if ($pos == 0)" or "if ($pos != 0)" will not provide the results you expect.

 

For example:

$Sent = 'Now is the time for all good men to come to the aid of their country.';
$Find = 'Now';
$Pos = strpos($Sent, $Find);
// $Pos is now equal to zero
if ($Pos) {
   // We found something - except since $Pos is zero, and zero evaluates to false, this does not execute
  echo 'Found it at: ' . $Pos . PHP_EOL;
}
if (! $Pos) {
  // We did not find it - except since $Pos is zero, and zero evaluates to false, this IS executed
  echo 'Did NOT find it.' . PHP_EOL;
}
if ($Pos !== false) {
  // We found something - this will work
  echo 'Really Found it at: ' . $Pos . PHP_EOL;
}
if ($Pos === false) {
  // We did not find it - this works too
  echo 'It is NOT there at all' . PHP_EOL;
}

Note: I wrote this code off-the-cuff, so it may have typos in it, but I think it shows the intent.

 

Many functions may return zero or false with different meanings.  Also note that zero in a string "$var = '0';" may also evaluate to the number zero or false, since PHP is not a strongly typed language. 

 

So, sometimes you want to make sure that a value is infact the string or numeric or boolean value you are testing it against.  In those cases, you need to use the additional equal sign.

 

EDIT:  The !== operator was not strictly necessary in the code I posted last time.  I used it because I was looking for a specific value I had assigned to the array.  In that code, the variable we were testing is either an array or it is false, so there should not be any ambiguity.