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!
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);
}
%s
\n", & selectrow_array($sth, $i) - freeworlder 2012-04-03 23:58Your second suggestion looks the best, but still not as slick as my beautiful pseudo-code.. :) Thanks for your help - freeworlder 2012-04-04 00:02
printf
is much cleaner than concatenation - ikegami 2012-04-04 02:04
tags. That was my example, but is not the reality of the website in question I'm afraid.
Can I not just call a sub-routine with arguments somehow within the print statement? eg. (Pseudo-code, assuming a working sub routine)
Print qq~<p>&grab_txt(0)</p><div>&grab_txt(1)</div>~;
Apologies, I'm a bit of a hack.. ; - freeworlder 2012-04-30 12:02
print qq~Some text follows:<br/>~ . &txt(4) . qq~<br/>~ . &txt(5) . qq~<br/>~ . &txt(6) . qq~<br/>~ . &txt(7) . qq~<br/>~;
Is this efficient do you think - freeworlder 2012-04-30 14:29
join '', map "<br/>" . txt($_), 4..7
ikegami 2012-04-30 15:04