Calculating the range between two numbers

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,

I'm a bit new to Perl, so sorry if this question may be a bit easy. I'm used to using DBI and MySQL, but when it comes to other tricks in Perl...thats where I lack.

I'm looking foward to finding out the range between two numbers. These numbers are stored in variables so it may change at given times.

So lets say $a = 2, and $b = 8 then I want to retreive the range that would be: 2, 3, 4, 5, 6, 7 , 8 I would like this to be in some sort of array list so I can use these numbers inside a MySQL select statement.

I have this mysql table that includes numbers and a type of dog. :)

| numbers |  type |
-------------------
|   2     |  ac   |
|   3     |  dc   |
|   4     |  ca   |
|   4     |  md   |
|   5     |  ae   |
|   6     |  ce   |
|   7     |  da   |
|   8     |  ac   |
___________________
[download]

I need some sort of way to display the values in column type by using the range of numbers. So if the range was 2-8, I would need to display all those values listed on the above table. Notice how it shows the number 4 twice , both having different values.

I'm not sure if I should just do a foreach statement and use the array range to find out all the values for each number. for ex.

my $type;
my $sth = $dbh->prepare_cached(<<SQL);
SELECT type from dogs where number = ?
SQL
foreach $number (@num_range) {

        $sth->execute($number);
        $sth_x->bind_columns(undef, \$type);

                while ($sth->fetch) {
                    print "$num: $type"; 
                }

}
[download]

Is this the best way to do this, or is there a better way to do this in a MySQL syntax? And if it is the best way, then I wouldn't know how to calculate the number range :(!!!

Any suggestions is greatly appreciated.

Thank you.

Comment on Calculating the range between two numbers Select or Download Code

Replies are listed 'Best First'.
Re: Calculating the range between two numbers by Zaxo (Archbishop) on Jun 24, 2005 at 06:49 UTC
The `..` operator does exactly that, `my ($c, $d) = (2, 8); my @range = $c .. $d;` [download] I've avoided $a and $b as names since thay are sacred to sort. After Compline, Zaxo	[reply] [d/l]
Re: Calculating the range between two numbers by Anonymous Monk on Jun 24, 2005 at 06:52 UTC
Thanks!!, I really have to read up more on those type of operators!!	[reply]
Re: Calculating the range between two numbers by anonymized user 468275 (Curate) on Jun 24, 2005 at 12:26 UTC
If we must talk mysql, even more efficient is... ...WHERE column-name BETWEEN lower-limit AND upper-limit -S	[reply]
Re^2: Calculating the range between two numbers by fmerges (Chaplain) on Jun 24, 2005 at 12:41 UTC
True :)	[reply]
Re: Calculating the range between two numbers by rev_1318 (Chaplain) on Jun 24, 2005 at 07:36 UTC
Slightly OT: in MySQL (and most other Relational DBMSes) you can use the IN condition in your select, e.g `SELECT type FROM dog WHERE number IN (2, 3, 4, 5, 6, 7, 8)` [download] Paul	[reply] [d/l]
Re^2: Calculating the range between two numbers by monarch (Priest) on Jun 24, 2005 at 07:50 UTC
And putting it all together we have: `my @range = ( $c .. $d ); my $rangesql = join( ', ', @range ); my $sql = "SELECT type FROM dog WHERE number IN ( $rangesql )";` [download] Or if you're paranoid: `my @range = ( $c .. $d ); my $rangesql = join( ', ', map { $dbh->quote($_) } @range ); my $sql = "SELECT type FROM dog WHERE number IN ( $rangesql )";` [download]	[reply] [d/l] [select]
Re^3: Calculating the range between two numbers by jhourcle (Prior) on Jun 24, 2005 at 13:45 UTC
If you're going to be using 'IN' a lot, and the set changes, but have a high likelyhood of having the same number of values, you may get better performance by using bind variables. (although, in the cases of numeric ranges, use 'BETWEEN' or <= and >=) `my @set = ($c .. $d); my $sql = 'SELECT type FROM dog WHERE number IN ( ?'.(',?' x $#set).') '; my $sth = $db->prepare($sql) $sth->execute(@set);` [download]	[reply] [d/l]
Re^2: Calculating the range between two numbers by Anonymous Monk on Jun 24, 2005 at 09:31 UTC
Yeah, but that leads to huge query strings if the numbers differ a lot. I'd write it as: `SELECT type FROM dog WHERE number >= 2 AND number <= 8` [download] using placeholders for the two values.	[reply] [d/l]
Re: Calculating the range between two numbers by fmerges (Chaplain) on Jun 24, 2005 at 09:41 UTC
Hi, Use SQL to do such things, you have comparators on SQL. Do it like the other post from Anonimous posted, using a SQL statement with comparators <, >, =, !=, <=, >=. Get out the power of SQL! If we where talking about a flat file or a DBM file, then you should do it with perl programming, f.e. you could do a grep on the data, but having this data query that offer you a RDB, is more powerfull and time saving doing it with the correct method. Regards, :)	[reply]