CODEWITHSUNDEEP
mysqlperl
Resident Reporter
Resident Reporter

Reputation: 7

Perl and MySQL user input to select a row?

Suppose I have a simple database table that doesn't have an ID_KEY but has a name column. I want to display the output like this

+----+---------+
|    | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Then have a <STDIN> for like, say, 3 to select penguin. 3 is just the line number that appears when you do the select call.

Is there any way to do this, or is it only possible with an id key associated and then a subsequent select statement matching that id key?

Upvotes: 0

Views: 149

Answers (3)

Borodin
Borodin

Reputation: 126722

I misunderstood you at first but I've caught on. But it doesn't make much sense, as when you're entering a number into a Perl program you won't be working with the MySQL command-line tool, and won't be able to see what numbers to enter..

What you need to do is to write your Perl program so that it prints all the name fields from the table together with a line number. Then your program can translate from an input animal number to its name because it knows what it printed.

Something like this would work. Of course you will have to set the name, IP address and credentials correctly so that DBI can connect to the database.

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect(
    'DBI:mysql:database=animal_test',
    'username',
    'password',
    { RaiseError => 1 },
);

my $names = map @$_, $dbh->selectall_arrayref('SELECT name FROM animals');
for my $i ( 0 .. $#$names ) {
  printf "%3d: %s\n", $i+1, $names->[$i];
}
print "\n";

print "Enter animal number: ";
my $animal = <>;
chomp $animal;

my $name = $names->[$animal-1];

printf "Animal chosen is %s\n", $name;

Upvotes: 1

Alex
Alex

Reputation: 17289

Just use query:

my $select = $dbh->prepare('
SET @id:=0; 
SELECT name, 
       @id = @id+1 
FROM table
');

Upvotes: 0

Lars
Lars

Reputation: 600

Option 1 - You would have put a id field in the DB if you want to find by integer 3 because row 3 will not always be penguin from an SQL query.

Option 2 - Dump the data into and array or hash and use the index of that to find the item from with in the variable and not the DB when 3 is captured from STIN.

Upvotes: 0

Related Questions