Programming Question

Hi,

I'm writing a Perl program to create a web page that allows faculty
at the college where I work to propose new courses using a web
form. At one point during the filling out of the form, the faculty
member must propose a course number to be used (like "101" for
English 101). I have a link that the faculty member can click that
runs a javascript routine that looks at the department choice made
earlier in the form and then opens a new window to display the
available course numbers--those not already in use by other courses
in their specified department.

The javascript just runs a Perl program to create the window and the
Perl program queries a MySQL table to get all of the currently in use
numbers in the specified department. I then need to compare the
results of the query with the list of all possible numbers (100 -
499) to determine which of the possible numbers are *not* in use, and
then display the available numbers in the new window for the faculty
member to choose from.

My problem (and I am sure it must be simple to do) is that I can't
figure out how to compare the list of possible numbers (100 - 499)
with the list of already in use numbers returned by my query. I've
tried various loops and while statements all to no avail. Usually
what I get is that for *each* number in the result set of my query,
it prints out all of the numbers from 100 - 499.

Can anyone point me in the right direction. So in other words,
when I do my fetchrow() on the MySQL table, how can I take the
results and compare them to the 100 - 499 set of numbers.

I'm *not* a great Perl programmer, so if you can explain clearly in
the most basic (not necessarily the most efficient or technically
impressive) way, I'd really appreciate it!



Douglas





Douglas S. Davis
Programmer/Analyst
Haverford College
Administrative Computing
370 Lancaster Ave.
Haverford, PA 19041
610-896-4206


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Programming Question

Douglas S. Davis wrote:
> Hi,
>
> My problem (and I am sure it must be simple to do) is that I can't
> figure out how to compare the list of possible numbers (100 - 499)
> with the list of already in use numbers returned by my query. I've
> tried various loops and while statements all to no avail. Usually
> what I get is that for *each* number in the result set of my query,
> it prints out all of the numbers from 100 - 499.


Sounds like you could use a sieve, just like the famous Sieve of Eratosthenes.

> Can anyone point me in the right direction. So in other words,
> when I do my fetchrow() on the MySQL table, how can I take the
> results and compare them to the 100 - 499 set of numbers.

It roughly goes like this:

# create a hash containing all numbers
@sieve{100 .. 499} = (1)x399;

# loop over your "in use" numbers, and delete them from the hash
while( my ($n) = $sth->fetchrow ) {
delete $sieve{$n};
}

# what you have left is the available numbers
@available = sort keys %sieve;


HTH,
Rhesa

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Programming Question

[ CC to Author ]

On Oct 4, 2005, at 10:37 AM, Douglas S. Davis wrote:

> My problem (and I am sure it must be simple to do) is that I can't
> figure out how to compare the list of possible numbers (100 - 499)
> with the list of already in use numbers returned by my query. I've
> tried various loops and while statements all to no avail. Usually
> what I get is that for *each* number in the result set of my query, it
> prints out all of the numbers from 100 - 499.
>
> Can anyone point me in the right direction. So in other words, when
> I do my fetchrow() on the MySQL table, how can I take the results and
> compare them to the 100 - 499 set of numbers.
>

My suggestion - don't fetch each row, unless there is a compelling
reason. Fetch all of the matching rows at once as a hashref. For
instance, if you have a table called tblcourses with the following
structure:

courseNumber | courseTitle | departmentNumber | creditHours |
instructor |
------------------------------------------------------------ ------------
--

# UNTESTED
my $keyfield = 'courseNumber';

my $courses = $dbh->selectall_hashref( q(Select * From tblcourses Where
departmentNumber = ?), $keyfield, $departmentNumber );

foreach ( 100..499 ) {
# if $courses -> { $_ } is defined, the class exists
next if $courses -> { $_ };

# If not do something with the unassigned course number in $_
}

$courses has a structure like:

$courses = {
'100' => {
'courseTitle' => 'Introduction to Programming',
'courseNumber' => '100',
'departmentNumber => '10',
'creditHours' => '3.0',
'instructor => 'Douglas S. Davis'
},
....
};




--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org