Sorting within mySQL tables.

[ilya]

Ok. Another question :)
I have 3 different tables:
users1
users2
users3
All of them have the same fields (FirstName, Email, Registration Date).
How do I sort the fields by Registration Date? I know how to do it if it's all in one table, but what if it's in 3 different ones?
I need this to be in Perl.
Thanks.

[ilya]

*bump*

[mike_w]

Currently, mySQL doesn't support UNION, which would solve your problem with a single query. A simplistic perl solution would be to make multiple queries and create a hash that combines the results of those queries. Then, just sort the hash and print.

Here is a quick code snippet that shows the basics. Remember that a hash's keys are unique, so you could lose data. You would have to create a unique key to be sure you are getting all of the rows from your queries into the hash.



#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect('DBI:mysql:example',undef,undef);

my $q = "SELECT name, phone FROM mytable";
my $q1 = "SELECT name, phone FROM mytable1";

my $output = $dbh->prepare($q);
my $output1 = $dbh->prepare($q1);

my ($table,$table1,%combined);

$output->execute;
$output1->execute;

my ($name,$name1,$phone,$phone1);

$output->bind_columns(\$name, \$phone);
$output1->bind_columns(\$name1, \$phone1);

while ($output->fetch) {
[nbsp][nbsp][nbsp][nbsp]$combined{$name} = $phone;
}

while ($output1->fetch) {
[nbsp][nbsp][nbsp][nbsp]$combined{$name1} = $phone1;
}

my $key;

foreach $key (keys %combined) {
[nbsp][nbsp][nbsp][nbsp]print "$key - $combined{$key}\n";
}

$dbh->disconnect;

<!-- NO_AUTO_LINK -->
[This message has been edited by mike_w (edited 02-26-00@12:23 pm)]