How to write a query for PHP and MySQL?

MySQL query to two databases via php.

• 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.

• Answer:

  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