Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

select 2 fields from 2 db and sort-help!

Quote Reply
select 2 fields from 2 db and sort-help!
For the last two days I have been trying to do two distinct selects from 2 distinct tables on fields that are named the same, then combine them into 1 array and sort it.

These are my queries
$sth = $LINKDB->prepare ("SELECT Series,ID FROM Links where CategoryID = '18' ORDER BY Series");
$adt = $LINKDB->prepare ("SELECT a.Series,c.ID FROM Links as c, Links_Additional_Dates as a where (c.ID=a.ID) and c.Series > '2001-04-31' and c.Add_Date > '2001-04-01' ORDER BY a.Series");

Links contains 31 fields or so.
Links_Additional_Dates contains ID and Series - ID is the same ID as found in Links


Then I read them into an array like this

while ($event = $sth->fetchrow_hashref) {
push @$events, $event;
}
while ($event = $adt->fetchrow_hashref) {
push @$events, $event;
}

Now I want to resort the array according to the dates as found in the Series column but I can't figure out how to sort it with something like:
foreach my $tmp(sort @$events) {.....

I've tried a hundred variations of sort(I'm not kidding) but can't seem to get it to sort by anything other than the address which looks like this:
HASH(0x827c974)

Once I step through the array I can access both tmp->{Series} and tmp->{ID}

I'm either reading @$events wrong, or I am doing something wrong in the sort. I've tried this:
foreach my $tmp (sort { ($events->{$a}->{Series} <=> $events->{$b}->{Series}) } @events) {...

and that just tells me NOT a HASHREF.

Does anyone have an idea how to sort that array on something other than the address or have a suggestion on
how to read into those two fields into a hash properly so that I can sort by the Series which are actually dates?

Thanks - you are a hero.

Kyle
Quote Reply
Re: select 2 fields from 2 db and sort-help! In reply to
What version ARE you using?

Regards,

Eliot Lee
Quote Reply
Re: select 2 fields from 2 db and sort-help! In reply to
I'm using version Links Sql 1.13, I thought I'd post it in both forums because the concept of what I'm doing seems pretty much the same as this isn't so much an SQL function, but rather a perl sort function.

My apologies for being redundant.

Peace.

Kyle

Quote Reply
Re: select 2 fields from 2 db and sort-help! In reply to
Well, the DISCUSSION forum is STRICTLY for newer versions of LINKS SQL.

Read the forum descriptions more carefully.

Regards,

Eliot Lee
Quote Reply
Re: select 2 fields from 2 db and sort-help! In reply to
And I thank you for having put me in place. Can you help?

Peace.

Quote Reply
Re: select 2 fields from 2 db and sort-help! In reply to
Hi Kyle,

You have $events which is an array reference of hashes so it looks like:

Code:
$events = [ { hash1 .. }, { hash2 .. }, { hash3 ..}, .. ];
If you want to sort $events by the Series key in the hashes you would do:

Code:
my @sorted = sort { $a->{Series} <=> $b->{Series} } @$events;
This is assuming that Series is a number.

If I missed what you are trying to do, let me know how you want it sorted. =)

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: select 2 fields from 2 db and sort-help! In reply to
Bless you Alex,

That's exactly what I was looking for - so close and so far away I was.
I did have to make this subtle change to work with the dates though, not sure why:

changed:
my @sorted = sort { $a->{Series} <=> $b->{Series} } @$events;
to
my @sorted = sort { $a->{Series} cmp $b->{Series} } @$events;

Thanks again.

Peace.

Kyle

Quote Reply
Re: select 2 fields from 2 db and sort-help! In reply to
Hi Kyle,

The <=> operator compares numerically and should only be used if you are comparing numbers. The cmp operators compares alphabetically. One word of caution is that it is case sensitive so 'Kyle' comes before 'alex'. You would change this to:

my @sorted = sort { lc $a->{Series} cmp lc $b->{Series} } @$events;

to ignore case.

Cheers,

Alex

--
Gossamer Threads Inc.