I have a MySql database that contains all the text for a multilingual website, with each different language in a separate column. (For example, the column 'en', id 1 = 'Hello', column 'fr', id 1 = 'Bonjour', and so on)

I am using a Perl/DBI script that pulls the text from the database according to the user language and required section in the usual way by preparing a query, executing the query, then copying the resulting records to an array. This all seems terribly cumbersome and inefficient to me though.

Here's the business end of what I'm using now.

omy (@TEXT, $getText);
$getText = $dbh->prepare("SELECT `$language` FROM text WHERE Page_Section = ? ORDER BY line_number");
$getText->execute('Section_Name');
while($_ =  $getText->fetchrow_array){push(@TEXT, $_)};
$getText->finish;

print qq~
<p>$TEXT[0]</p>
<p>$TEXT[1]</p>
<p>$TEXT[2]</p>
~;

The problem with this method for me is, what if I want to delete one of those lines from the database, or add in a few new lines? It messes up the whole script which is only incrementing 'blindly' through the array from the beginning. In this example, if I deleted the second row in the database and wanted to insert two new lines, I would have to rewrite the relevant parts of the script, otherwise it will print in the wrong order.

I could of course pull each line of text by 'absolute' ID, but that requires a separate 'execute' statement and 'fetchrow_array' line for every bit of text that I want.

Is there a neater way, like a one-shot 'pull and print' method where I just can grab single fields and output them according to ID? Something like.. (warning - pseudo-code cometh)

$query = $dbh->prepare(SELECT 'language' FROM 'text' WHERE 'id' = ?);

print qq~
<p>$query->execute(0)</p>
<p>$query->execute(1)</p>
<p>$query->execute(2)</p>
~;
$query->finish;

You get the idea.. ;) Is something like this possible? Thanks in advance!

• mysql
• perl
• dbi

Yes, it's most definitely possible. They're called "subroutines".

sub selectrow_array {
   my $sth = shift;
   $sth->execute(@_)
      or return ();
   my $row = $sth->fetch();
      or return ();
   $sth->finish()
      or return ();
   return @$row;
}

my $sth = $dbh->prepare(q{SELECT 'language' FROM 'text' WHERE 'id' = ?});

for my $i (0..2) {
   printf "<p>%s</p>\n", selectrow_array($sth, $i);
}

That said, such a sub already exists (although it has slightly different syntax) as a method of dbh.

my $sth = $dbh->prepare(q{SELECT 'language' FROM 'text' WHERE 'id' = ?});

for my $i (0..2) {
   printf "<p>%s</p>\n", $dbh->selectrow_array($sth, undef, $i);
}