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 |
