Gossamer Forum
(Advanced Search)
for
Home : Products : DBMan : Customization :

Sorting multiple fields (CGI coding question)

Sep 21, 1999, 3:44 PM
TonyB
Novice (6 posts)
Sep 21, 1999, 3:44 PM
Post #1 of 8
Views: 3186
Shortcut
Quote Reply
Sorting multiple fields (CGI coding question)
Please excuse my ignorance, but I have no idea how to code a sorting algorithm for a script i wrote.

Basically, I have a seperate script that i wrote to extract each record, check to see if 3 fields are not empty, and if it is not, lists the name, birthday, birthmonth, and birthyear.

What I want to do, is sort the birthdays by month, then day, and then year. Right now it just lists them according to their location in the database, and is confusing to look at, and tough to see who's birthday is coming up.

Here's the code for the script I wrote:
Code:
#!/usr/bin/perl

my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime(time());
my (@months) = qw!Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec!;
($day < 10) and ($day = "0$day");
$year = $year + 1900;

$dadate = "$day-$months[$mon]-$year";

my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime(time());
($sec < 10) and ($sec = "0$sec");
($min < 10) and ($min = "0$min");
($hour < 10) and ($hour = "0$hour");

$datime = "$hour:$min:$sec";

# Display some header
$html1 = qq~
<html>
<head>
<title>Birthday List</title>
</head>
<body>
<div align="center">
<center>
<table border=1>
~;


# Display some footer
$html2 = qq~
</table>
<font size=\"1\">Last updated $dadate $datime</font>
</div>
</center>
</body>
</html>
~;

# database structure
# 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16
# ID, As_Name, Full_Name, BirthDay, BirthMonth, BirthYear, Country, Street, City, State, Zip_Code, Email, Home_Page, ICQ_Name, ICQ_Number, ICQ_Remarks, Last_Updated

# Open the database
open(LOGFILE, "<default.db");
@entries = <LOGFILE>;
close LOGFILE;


# Open the output file
open (HTML,">../../cgi_out/birthday.html");

# Print header
print HTML"$html1";

# Now, lets split what ever we got in the database ...
foreach $line (@entries) {
@fields = split(/\|/,$line);

if (!$fields[3] eq "")
{
if (!$fields[4] eq "")
{
print HTML " <tr>\n <td nowrap><p ALIGN=\"LEFT\"><b><font face=\"Verdana\" size=\"2\">$fields[1]</font></b></td>\n";
print HTML " <td nowrap><p ALIGN=\"LEFT\"><b><font face=\"Verdana\" size=\"2\">";
if ($fields[3]) { print HTML "$fields[3]/"; }
else { print HTML "--/"; }
if ($fields[4]) { print HTML "$fields[4]/"; }
else { print HTML "---/"; }
if ($fields[5]) { print HTML "$fields[5]"; }
else { print HTML "----"; }
print HTML "</font></b></td>\n";
# print HTML " <td nowrap><p ALIGN=\"LEFT\"><b><font face=\"Verdana\" size=\"2\">$fields[3]/$fields[4]/$fields[5]</font></b></td>\n";

print HTML " <td nowrap><p ALIGN=\"LEFT\"><b><font face=\"Verdana\" size=\"2\">$fields[9]</font></b></td>\n </tr>";
# print HTML "$fields[1] - $fields[3] $fields[4] $fields[5] $fields[9] <br>\n";
}
}

};

# Print footer
print HTML"$html2";


# Close the output file
close (HTML);

# Print some visual output, when your script finished working with database Smile
#print "Content-type: text/plain\n\n";
#print "Done!";

print "Location: http://www.kickas.org/cgi_out/birthday.html\n\n";

Here's the record structure of my database:
Code:
%db_def = (
ID => [0, 'numer', -2, 8, 1, '', ''],
As_Name => [1, 'alpha', 40, 255, 1, '', ''],
Full_Name => [2, 'alpha', 40, 255, 0, '', ''],
BirthDay => [3, 'numer', 2, 2, 0, '', ''],
BirthMonth => [4, 'alpha', 3, 3, 0, '', ''],
BirthYear => [5, 'numer', 4, 4, 0, '', ''],
Country => [6, 'alpha', 10, 255, 0, '', ''],
Street => [7, 'alpha', 0, 255, 0, '', ''],
City => [8, 'alpha', 40, 255, 0, '', ''],
State => [9, 'alpha', 40, 255, 0, '', ''],
Zip_Code => [10, 'numer', 5, 12, 0, '', ''],
Email => [11, 'alpha', 50, 255, 0, '', ''],
Home_Page => [12, 'alpha', 50, 255, 0, '', ''],
ICQ_Name => [13, 'alpha', 20, 255, 0, '', ''],
ICQ_Number => [14, 'alpha', 12, 255, 0, '', ''],
ICQ_Remarks => [15, 'alpha', 40, 255, 0, '', ''],
Last_Updated => [16, 'date', 12, 15, 0, &get_date, ''],
Phone_Number => [17, 'alpha', -2, 20, 0, , ''],
Picture_Url => [18, 'alpha', 50, 255, 0, , ''],
Picture_Title => [19, 'alpha', 40, 40, 0, , ''],
Story_Url => [20, 'alpha', 50, 255, 0, , ''],
Story_Title => [21, 'alpha', 40, 40, 0, , ''],
);

The database is located here:
www.kickas.org/cgi-bin/dbman/db.cgi

And the birthday list is here:
www.kickas.org/cgi_out/birthday.html

Any help is appreciated, as I do not know nor understand the functions or procedures to use to sort the data, or to read in the entire database and sort it. The script above just reads it line by line.

BTW, some of this code is straight from a script I downloaded called "my silly database" or something like that. I know my coding is sloppy, but it works :}

Thanks in advance.

[This message has been edited by TonyB (edited September 21, 1999).]
Sep 22, 1999, 6:42 AM
JPDeni
Veteran / Moderator (8669 posts)
Sep 22, 1999, 6:42 AM
Post #2 of 8
Views: 3083
Shortcut
Quote Reply
Re: Sorting multiple fields (CGI coding question) In reply to
I've never been able to work out how to sort by more than one field. You have gone further than I ever have. Smile

I would go about it a different way. (One nice thing about Perl is that there are usually a number of ways to accomplish the same task.)

I would add another field to the database -- call it BirthDate. It would be an optional hidden field:

Code:
BirthDate => [22, 'numer', -1, 8, 0, , '']

In db.cgi, sub add record, just after

my ($output, $status, $counter);

add

Code:
my (%months) = ("Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5,
"Jul" => 6, "Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10,"Dec" => 11);

if ($in{'BirthMonth'} && $in{'BirthDay'} && $in{'BirthYear'}) {
if ($in{'BirthDay'} < 10) {
$BirthDay = "0".$in{'BirthDay'};
}
else {
$BirthDay = $in{'BirthDay'};
}
$in{'BirthDate'} = $months{$in{'BirthMonth'}} . $BirthDay . $in{'BirthYear'});
}

Then you can just use the DBMan search, sorting by the BirthDate field -- field 22.


------------------
JPD





Sep 23, 1999, 2:20 AM
TonyB
Novice (6 posts)
Sep 23, 1999, 2:20 AM
Post #3 of 8
Views: 3088
Shortcut
Quote Reply
Re: Sorting multiple fields (CGI coding question) In reply to
Thanks JP!

One more thing. Would it be feasible to create variables for each month, then sort them by days?

Can I declare a PERL variable, that contains the month, then allow me to add the dates?

E.Q.
date_structure = { #, string, # }
date_list { date_structure array }
months (date_list {12} )

Then, a I go through the fields I could assign the dates to each months structure?

Sorry, but the coding for this cofuses the heck out of me, and since I have a drop down list for selecting days/months, it is tough to do a search. I suppose i could seperate the database into month-based databased instead on one big db.
Sep 23, 1999, 6:03 AM
JPDeni
Veteran / Moderator (8669 posts)
Sep 23, 1999, 6:03 AM
Post #4 of 8
Views: 3082
Shortcut
Quote Reply
Re: Sorting multiple fields (CGI coding question) In reply to
Maybe it's because I just got up and my brain isn't working yet Smile, but I don't understand what you mean. Can you give me an example of what you have in mind, using sample data?


------------------
JPD





Feb 24, 2000, 2:44 AM
mergs
Novice (12 posts)
Feb 24, 2000, 2:44 AM
Post #5 of 8
Views: 3092
Shortcut
Quote Reply
Re: Sorting multiple fields (CGI coding question) In reply to
I am still trying to get my date to sort. I spotted this code from JPDeni (the BirthDate part below in db.cgi), which seems to be exactly what I needed. I am getting a syntax error and can't get by it. I even cut and pasted it, as is, and still won't compile.
Feb 24, 2000, 3:12 AM
mart
User (473 posts)
Feb 24, 2000, 3:12 AM
Post #6 of 8
Views: 3106
Shortcut
Quote Reply
Re: Sorting multiple fields (CGI coding question) In reply to
Mitch wrote a great mod, for sorting on three fields, it works perfect on my site.
Look here: http://www.gossamer-threads.com/...m12/HTML/001080.html
Feb 24, 2000, 3:50 AM
mergs
Novice (12 posts)
Feb 24, 2000, 3:50 AM
Post #7 of 8
Views: 3080
Shortcut
Quote Reply
Re: Sorting multiple fields (CGI coding question) In reply to
Thanks, I saw that, but it didn't seem to address the fact that the months would need to be numbers instead of Jan, Feb...I am trying to give the user the words (Jan, Feb..) then convert them for the sorting process. Seems hard to believe that no one has written a date sort. Does everyone just post the records as they come in to the database?
Feb 24, 2000, 4:19 AM
mergs
Novice (12 posts)
Feb 24, 2000, 4:19 AM
Post #8 of 8
Views: 3095
Shortcut
Quote Reply
Re: Sorting multiple fields (CGI coding question) In reply to
THANKS to everyone for responding. I figured out the problem in the code and it WORKS!!!! The code below allowed me to have
three date fields (SMonth SDay SYear) and concatenate them, then sort on another field called StartDate:

my (%months) = ("Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5, "Jul" => 6, "Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10,"Dec" => 11);
if ($in{'SMonth'} && $in{'SDay'} && $in{'SYear'})
{ if ($in{'SDay'} < 10)
{$SDay = "0".$in{'SDay'};
}
else {
$SDay = $in{'SDay'};
} ($in{'StartDate'} = $months{$in{'SMonth'}} . $SDay . $in{'SYear'});
}