Is there a 'one-shot' way to pull data from MySql using Perl?

Go To StackoverFlow.com

-2

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!

2012-04-03 20:04
by freeworlder
The call to finish() isn't needed. See the docs. I really wish I'd called it discardpendingrows() - Tim Bunce 2012-04-05 16:31


4

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);
}
2012-04-03 20:29
by ikegami
Interesting. Should the first one not be: printf "

%s

\n", & selectrow_array($sth, $i) - freeworlder 2012-04-03 23:58
Thanks for that, but I was hoping that Perl would be able to grab them on the fly during print. I'm not familiar with PHP, but I'm pretty sure you don't have to copy all your data to an array first..? Could be wrong though...

Your 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

@silkfield, If you think no arrays are involved in reading records, you are quite mistaken - ikegami 2012-04-04 02:00
@silkfield, Re "&", no, why would you instruct Perl to disable prototypes? The sub doesn't even have one - ikegami 2012-04-04 02:00
@silkfield, Actually, its slicker than your pseucode. The only change I made is that I removed your redundant code in favour of a loop. As for the implementation of your interpolation, printf is much cleaner than concatenation - ikegami 2012-04-04 02:04
Someone -1'd my question??? What's that about - freeworlder 2012-04-09 14:09
This solution assumes a regular loop pattern of wrapping each data field in

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

I've just discovered this method works. (Again assuming a working sub) 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

@silkfield, I would write that join '', map "<br/>" . txt($_), 4..7ikegami 2012-04-30 15:04