Sorting on two fields 2

DonP · Feb 6, 2001

I have a small Perl script that presents the info from a flatfile database, but I would like it to sort on one field, then sub-sort on another. But I'm not sure how to do a sort at all! What I have so far, doesn't work but it is:

[tt]
while (($line = &read_file("FILE1&quot

) && ($counter < 200)) {
# split the fields at the | character
@tabledata = split(/\s*\|\s*/,$line ,$fields);

my @tabledata;
my @sort_indexes;
my @sorted;
@sorted =
map {$_->[3]} (
sort {$a->[3] <=> $b->[3]} (
map {[$_, shift(@sort_indexes)]} @tabledata));
[/tt]

I'm trying to sort on the third field ($tabledata[3]) but would also like to sort on $tabledata[8]. I'm not a programmer so any help is appreciated. Thanks!

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

DonP · Mar 7, 2001

Thanks! The whole code is posted above though some of the appearance parts have changed - that is, the generated HTML. But to see it as it is now, it is pasted in below. There are some remmed out lines were I was trying to fetch the database file's last saved date but that's in another post. I couldn't get it to work either. With the hyperlink type of input that it uses, the search is on the second field [1], which needs to be verbatum and spaces filled with + in the hyperlink but I want to also search via a form, typed in partial or full keyword(s) to search either the third field only [2] or possibly all the fields. Searching all would be nice but searching only on [2] is OK too. Thanks again for your help:

[tt]#!/usr/bin/perl

require "../setup.pl";

$database = "$database/recordings.db";

$querystring = $ENV{'QUERY_STRING'};

# URL example: "discography.cgi?12+inch+LP,1"
# keywords
# convert plus signs to spaces
$querystring =~ tr/+/ /;
($query,$searchfield) = split(/\s*\,\s*/,$querystring,$searchfield);

}
if ( $query ne '*') {
$querytitle = $query
} else {
$querytitle = "All Formats"
}

# ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blocks) = stat($database);

#($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($database);

# use POSIX qw(strftime);
# $now_time = strftime "%a %b %e , %Y at %H:%M:%S", localtime;

@file_attr = stat($database);

print "Content-type: text/html\n\n";
print "<HTML><HEAD>\n";
print "<link rel=stylesheet href=\"$baseurl/style/style.css\" type=\"text/css\">\n";
print "</HEAD>\n\n";
print "<BODY BGCOLOR=#FFFFFF TEXT=#000000 LINK=#0000FF VLINK=#800040 ALINK=#800040>\n";
print "<TITLE>Recordings</TITLE>\n";
print "<CENTER>\n";

# if (-x $database && (($d) = stat(_)) && $d < 0) {
# print "<b>Database size: $size</b><br>\n";
# print "<b>Last modification: </b><br>\n";
# print "<b>Query String: $form{querystring}</b><br>\n";
# print "<b>Keywords: $querystring</b><br>\n";
# }

print "<TABLE width=100\% border=0 cellpadding=0 cellspacing=5>\n";
print "<CAPTION>Recordings / $querytitle<br>\ \;<br></CAPTION>\n";
print "<TR><TD>\n";
print "<a href=\"$baseurl/discography/formats_experimental.html\">Return to the Experimental Database</a><p>\n";
print "</TD></TR>\n";

open(DBRECORDS, "$database&quot

;

while (<DBRECORDS>){
push @database, [split (/\s*\|\s*/, $_) ];
}

my @sorted =
sort {&sort_fields} @database;

sub sort_fields {
if ($a->[3] cmp $b->[3]) {
return $a->[3] cmp $b->[3]}
elsif ($a->[7] cmp $b->[7]) {
return $a->[7] cmp $b->[7]}
else {
return $a->[1] cmp $b->[1]}
}

foreach (@sorted) {
$listing = "$_->[4],
$_->[5],
$_->[6],
$_->[7],
$_->[8]";

if ($_->[$searchfield] =~ /^$query$/i) {

print "<tr><td>\n";
print "<a href=\"$cgiurl/linkredirect/link_redirect.cgi?link=$_->[10]\">";
print "<i><b>$_->[3]</b></i></a>";
if ($_->[2] ne "&quot

{
print " <b>($_->[2])</b>";
}
print "<menu>\n";
print "$listing";
print " $$_->[1]$\n";
print "</menu>\n";
print "</tr></td>\n";

}
}

close (DBRECORDS);

print "</table>\n\n";
print "</body>\n</html>";[/tt]

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

stillflame · Mar 7, 2001

okay, um, i don't think i'm completely on the level with everything you have to do, but i've modified parts of your code so as to accept a search string that is then compared against all the fields of a record, which then prints the record if any field has matches for all search strings - just a basic search loop structure, nothing special. there are lots of modifications you could make to optimize this for your task(concatenate all the fields of a record and then perform the search on that; use an ANY style search control, rather than ALL), but i'm not familiar enough with your actual task to ensure these optimizations would be correct. In any event, this change is the one that seemed to me to require the least amount of restructuring. you may want more than this can provide, in which case i still see lots of options unexplored (switching to POST being one). I ommitted the parts of your code that weren't affected, and i didn't format it for this page, so it's best viewed after being copied into an editor of your choice:
[tt]
#!/usr/bin/perl -w

$querystring = $ENV{'QUERY_STRING'};
# URL example: "discography.cgi?12+inch+LP"
$querystring =~ tr/+/ /;
$query = $querystring;

#....your other code stuffs, print the start of the page, opening and sorting the database

#the following will print a record only if ALL search strings are matched, in any field. for this to work properly, replace your "foreach(@sorted)" loop with this one. i may have taken out some of the little parts that didn't get affected too much by the change, but it should be clear where to add them.
@query = split(" ", $query);
my $matches = 0;
DATABASE: foreach $record (@sorted)
{
RECORD: foreach $field (@$record)
{
my $counter = 0;
QUERY: foreach $string (@query)
{
if ($field =~ /$string/i)
{
$counter++;
}
}
if ($counter == length(@query)) #this could be ">0" if you want 'ANY' behavior
{
my $listing = "$record->[4],...etc.";

print "<a href=\"$cgiurl/linkredirect/link_redirect.cgi?link=$record->[10]\">";
print "<i><b>$record->[3]</b></i></a>";
if ($record->[2] ne "&quot

{
print " <b>($record->[2])</b>";
}
#you had some simple printing stuff in here
print "$listing";
print " $$record->[1]$\n";
$matches++;
next DATABASE;
}
}
}

if ($matches == 0)
{
print "<H1>No matches found.</H1>"; #or whatever
}
[/tt]

hope this helps. "If you think you're too small to make a difference, try spending a night in a closed tent with a mosquito."

DonP · Mar 7, 2001

Thanks a bundle! It seems to be working now after a fashion, once I got the swiggly brackets in the right places. My original version had a counter too, though at the time, it did not seem necessary, but I do like the error protection that it allows. Can it be used to give a count of resulting records from a search?

But I can't figure out what field(s) it's searching now. Is it searching all of them? The results seem somewhat odd. For example, if I type in a title in the form, it lists those titles but gives many others after it. If I select existing hyperlink searches that have spaces, it gives lots of results but not necessary the correct ones. I probably just have a small error in the brackets still, but can't seem to find it!

Another little thing is that it seems to need a complete match and I wasn't sure if I understood what needed to be done for a partial search, but the hard-coded hyperlink queries need to be exact and specific. Also, when I type a keyword using the form (I have to use GET but I would prefer POST), it still submits the "querystring=" That in itself is fine but since I use that string's value for presentation at the top of the page, I need to be able to strip it back off for that purpose.

[tt]#!/usr/bin/perl

require "../setup.pl";
$database = "$database/recordings.db";

$querystring = $ENV{'QUERY_STRING'};

# URL example: "discography.cgi?12+inch+LP"
$querystring =~ tr/+/ /;
($query,$searchfield) = split(/\s*\,\s*/,$querystring,$searchfield);
$query = $querystring;

if ( $query ne 'a') {
$querytitle = $query
} else {
$querytitle = "All Formats"
}

@file_attr = stat($database);

print "Content-type: text/html\n\n";
print "<HTML><HEAD>\n";
print "<link rel=stylesheet href=\"$baseurl/style/style.css\" type=\"text/css\">\n";
print "</HEAD>\n\n";
print "<BODY BGCOLOR=#FFFFFF TEXT=#000000 LINK=#0000FF VLINK=#800040 ALINK=#800040>\n";
print "<TITLE>Recordings</TITLE>\n";
print "<CENTER>\n";

print "<TABLE width=100\% border=0 cellpadding=0 cellspacing=10>\n";
print "<CAPTION>Recordings / $querytitle<br>\ \;<br></CAPTION>\n";
print "<TR><TD>\n";
print "<a href=\"$baseurl/discography/formats_experimental.html\">Return to the Experimental Database</a><p>\n";
print "</TD></TR>\n";

open(DBRECORDS, "$database&quot

;

while (<DBRECORDS>){
push @database, [split (/\s*\|\s*/, $_) ];
}

my @sorted =
sort {&sort_fields} @database;

sub sort_fields {
if ($a->[3] cmp $b->[3]) {
return $a->[3] cmp $b->[3]}
elsif ($a->[7] cmp $b->[7]) {
return $a->[7] cmp $b->[7]}
else {
return $a->[1] cmp $b->[1]}
}

@query = split(" ", $query);

my $matches = 0;
DATABASE: foreach $record (@sorted)
{
RECORD: foreach $field (@$record)
{
my $counter = 0;
QUERY: foreach $string (@query)
{
if ($field =~ /$string/i)

{
$counter++;
}
}
if ($counter == length(@query)) #this could be ">0" if you want 'ANY' behavior
{

my $listing = "$record->[4],
$record->[5],
$record->[6],
$record->[7],
$record->[8]";

print "<tr><td>\n";
print "<a href=\"$cgiurl/linkredirect/link_redirect.cgi?link=$record->[10]\">";
print "<i><b>$record->[3]</b></i></a>";

if ($record->[2] ne "&quot

{
print " <b>($record->[2])</b>";
}

print "<menu>\n";
print "$listing";
print " $$record->[1]$\n";

$matches++;
next DATABASE;
}
}
}

if ($matches == 0)
{
print "<tr><td>\n";
print "<H1>Sorry, no matches were found.</H1>";
print "</tr></td>\n";
}

close (DBRECORDS);

print "</table>\n\n";
print "</body>\n</html>";[/tt]

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

stillflame · Mar 9, 2001

the script is comparing the search string to every field of every record and should only print it if all of the space-delimited search strings match. i have looked at it again, and found a way to make it simpler at little.
at the top, where you're splitting up the query string, don't. remove the red parts:[tt]

$querystring = $ENV{'QUERY_STRING'};
# URL example: "discography.cgi?12+inch+LP"
$querystring =~ tr/+/ /;
($query,$searchfield) = split(/\s*\,\s*/,$querystring,$searchfield);
$query = $querystring;[/tt]

this'll just set $query to the string. then, in the line later where you split up the string, change it to the following:[tt]

@query = split("+", $query);
[/tt]
and this my remove that problem it had with the strings that had spaces in them.

to perform the search on only some of the records, but without changing the structure too much, change the line starting with RECORD: to something like the following:
[tt]
RECORD: foreach $field (@{$record}[0..6], @{$record}[8..11])
[/tt]
of course pointing to the fields you want. if it's only one filed, use "${$record}[14]" syntax.

okay, to get the search to print a record if it matches ANY of the search strings, the if conditional "if ($counter..." needs to look like this:[tt]
if ($counter > 0)
[/tt]
but you'll probly want to the sort the records based on how many of the search strings matched, which will take a bit more work and a bit of restructuring.

lastly, the "$matches" counter is the number of records that matched the search string, so yes you could just print that out and mr. user would get to know how many matches were made, but you probly want that to be displayed at the TOP of the page, rather than the bottom after it's done printing all the results. in order to get this behavior, you'd have to store all the search results in a temporary array, then print out how many matched, then print out the array. this would be a structure supportive of have the ANY behavior i described above...

good luck. "If you think you're too small to make a difference, try spending a night in a closed tent with a mosquito."

DonP · Mar 10, 2001

I think I replaced the proper lines as suggested (thanks!) but this new line seems to be causing a problem:

[tt]@query = split("+", $query);[/tt]

giving this error:

[tt][red]/+/: ?+*{} follows nothing in regexp[/red][/tt]

Just for clarification, the hyperlink searches have the "+" symbols hard-coded into the link where there would otherwise be spaces per the example in one of my postings above and does not use a form (they are not in the database though). Some do not have spaces and therefor no "+". The hyperlink search must search only one specific field for an exact match, but the keyword searches do not have "+" and can search any or all fields via a form, preferrably using POST.

Per your e-mailed suggestion, I still can't get any results good or bad with POST, but GET still does fetch results.

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

stillflame · Mar 10, 2001

no, that error is just a little oversight on my part, sorry. change it to:[tt]
@query = split(/\+/, $query);[/tt]
and that'll fix it.

i don't know why you can't get a POSTing form to work. i sent you an example with a form that worked for me. otherwise, i don't know what else you need. you could post your failed attempts to show what you're doing (include both the html for the form that calls the script, and the script).
"If you think you're too small to make a difference, try spending a night in a closed tent with a mosquito."

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sorting on two fields 2

DonP

IS-IT--Management

DonP

IS-IT--Management

stillflame

Programmer

DonP

IS-IT--Management

stillflame

Programmer

DonP

IS-IT--Management

stillflame

Programmer

Similar threads

Part and Inventory Search

Sponsor