Google Answers Logo
View Question
 
Q: MySQL query to two databases via php. ( Answered 5 out of 5 stars,   3 Comments )
Question  
Subject: MySQL query to two databases via php.
Category: Computers > Programming
Asked by: pmj2-ga
List Price: $50.00
Posted: 22 Mar 2005 23:39 PST
Expires: 22 Apr 2005 00:39 PDT
Question ID: 498988
On my Win2000/IIS5 server, I have two mySQL databases ("NYC" and "LA")
on a single server.  Tables in the databases are the same (e.g. DB1
has the same tables as DB2).  Table "employees" in each database
has fields "fname" "lname" "hire_date".

I need a query to list all employees from both locations, sorted by
hire date.  Output should read in four columns,
lname/fname/location/hire date.

Scripting language is php on Win2K/IIS.

Please include database connection syntax, assume
read_user/read_password as username/password with read-only rights to
database.

Clarification of Question by pmj2-ga on 22 Mar 2005 23:47 PST
"employees" table has additional fields (e.g. sequence number, phone
number, etc), but some values overlap and are not truly unique for
each database.

Request for Question Clarification by hammer-ga on 24 Mar 2005 05:24 PST
Which version of mySQL are you using?

- Hammer
Answer  
Subject: Re: MySQL query to two databases via php.
Answered By: webadept-ga on 24 Mar 2005 08:59 PST
Rated:5 out of 5 stars
 
Hi, 

There are two ways we can do this; a) use a temp table to place the
results from this query into, using the mysql server to do the
sorting, or b) bring the results into a multi-array and sort them
ourselves in PHP. Both have their uses.

Typically, you want to use the mysql database. Some mysql servers are
setup not to allow the creation of temporary tables, so it is good to
know both methods, and I'll demonstrate the MySQL method for you here.

Another note before we begin, when joining or sorting tables inside
databases or cross databases, it is always best to use indexing.
Hopefully your employee database has a schema something like the
following

create table employee(
emp_id int not null auto_increment primary key, 
fname varchar(20),
lname varchar(20),
hire_date date
);

ALTER TABLE `employee` ADD INDEX `hrdate` ( `hire_date` );

Having those two (at the very least those two) indexes on your
employee databases will help with the speed of our queries. We'll also
add an index to our temp table as well, and test the speed of the
process with adding the index, and just having the database work
through the sort raw. Your mileage may vary in this area, but again it
is good to know your options as you go. Adding an index on the fly is
sometimes a rather intensive process for the server. Really it depends
on the tables and structure, but the basic rule is if you are joining
tables, or sorting tables, it is always best to do so using the index.

Some notes on temp tables: MySQL allows the creation of temporary
tables created from the results of other queries (this is not just a
MySQL feature, it is common with most database servers). MySQL
controls the creation of these tables, and the clean up of these
tables after we are done with them. One common worry, especially with
web programming, is that the same table name is used over and over
again, "so wouldn't two users using the program at the same time also
be using the same data?" The answer to this is no. Even though in our
code the table is called the same name, the real name of the temp
table in the MySQL database is known to itself alone, and is unique to
each user for each session.

This is true even for existing table names. If I have a table called
"employee" and then I create a temporary table called "employee" and
during this session I delete all records form "employee" only the
records in the temporary table will be deleted, and the original
"employee" table will be left alone. Doing this in real live code on
an application server is probably going to get you a slap on the hand
with a ruler and you'll hear the words "bad programmer, no biscuit!"
but, it is nice to know that temporary tables are pretty safe to use.

Something else to consider in using the MySQL server for the sort,
rather than using the PHP code. For this application, it is probably
not much of a consideration. But if you were using the same solution
for a public area on a web page, where thousands of users could be
creating temp tables at the same time, then you might want to consider
using the PHP solution. Again we want to balance this with the amount
of data we are processing. Benchmarking is your friend. :-)

Now, with all that said, let's look at how we do this. I'll put my
instructions in comments for the code area here

Solution :Using MySQL to create a temp table and sort using a created index. 

<?php

// Once we have connection to the mysql database
// we have access to all databases on the server
// which our user name and password would normally
// give us access too.
//
// So even though i'm connecting to "test" and it is
// my "current database" I do have access to test2 and 
// any other databases webuser has access to using that password// 
  $mysqlusr = "webuser";
  $mysqlpass = "webword";
  $dbname = "test";
  $idlink = mysql_connect('localhost', $mysqlusr, $mysqlpass)
  or die("Could not connect: " . mysql_error());
  mysql_select_db($dbname);

// we create our temporary table and define it with the results of one
of our tables//
  $q = "create temporary table emplist select * from test2.employee";
  $r = mysql_query($q);
// now add to our temporary table, the results from the other table
  $q = "insert into emplist select * from test.employee";
  $r = mysql_query($q);
// add our index ... this can be commented out for benchmarking
  $q = "ALTER TABLE `emplist` ADD INDEX `hrdate` ( `hire_date` )";
  $r = mysql_query($q);
// and then select our sorted results from the temporary table // 
  $q = "select * from emplist order by hire_date";
  $r = mysql_query($q);

  echo "<table>";
  echo "<tr><th>id</th><th>first name</th><th>last
name</th><th>startdate</th></tr>";
  while($rs = mysql_fetch_array($r))
  {
    echo "<tr>";
  	
  	print "<td>$rs[0]</td>";
  	print "<td>$rs[1]</td>";
    print "<td>$rs[2]</td>";
    print "<td>$rs[3]</td>";
            
        echo "</tr>";
  }
  echo "</table>";
  
  // this part is done after everything you want to do with the 
  // temporary table emplist for this sesson
 $q = "drop table emplist";
 $r = mysql_query($q);

?>


If you have any further questions regarding this, please feel free to
use the Clarification Request button.

Thanks, 

webadept-ga
pmj2-ga rated this answer:5 out of 5 stars

Comments  
Subject: Re: MySQL query to two databases via php.
From: chandrars-ga on 23 Mar 2005 01:49 PST
 
Hi,

It works! You can use the following code snippet ..

I have created two databases, d1 and d2.  Each has a table named emp.

<?php
  $mysqlusr = "root";
  $mysqlpass = "";
  $dbname = "test";
  $idlink = mysql_connect('localhost', $mysqlusr, $mysqlpass)
  or die("Could not connect: " . mysql_error());
  mysql_select_db($dbname);

  $qry = "select * from d2.emp, d1.emp";
  $res = mysql_query($qry);

  echo "<PRE>";
  while($rs = mysql_fetch_array($res))
  {
  	echo "\n";
  	echo $rs[0];
  	echo "\n";
  	echo $rs[1];
  	echo "\n";
  	echo $rs[2];
  	echo "\n";
  	echo $rs[3];
  }
  echo "</PRE>";

?>
Subject: Re: MySQL query to two databases via php.
From: gee_orge-ga on 23 Mar 2005 04:16 PST
 
I couldn't understand if u have 2 completly diffrent databases on youe mySQL server
or u have 1 database and you want results from 2 diffrent table!
Subject: Re: MySQL query to two databases via php.
From: pmj2-ga on 23 Mar 2005 16:23 PST
 
chandrars,

     $qry = "select * from d2.emp, d1.emp;" 

...returns all values from employee table, but does not sort them by
date, nor include the location (i.e. name of the database).  The
"ORDER BY [x] DESC" and including the db name are the parts I'm
struggling with.

gee_orge,

I have two mySQL databases.  Each database has a table named
"employees".  The "employees" tables (NYC.employees, LA.employees)
have fields "lname" "fname" "hire_date".

I need the query to go to one table in each database, concatenate the
results, and sort by hire_date, and include the name of the database
in the results.

I can't query with "ORDER BY NYC.hire_date DESC"

I'm looking for results like this:

Smith     John     LA    01/01/2000
Jones     Jimmy    NYC   01/02/2000
Williams  Larry    LA    01/03/2000
User      Joe      LA    01/04/2000
Doe       Jane     NYC   01/05/2000

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy