This is a code tweaking question; a question to improve my coding skills. One of my web site runs almost totally on Perl CGI and DBI scripts. All data for the site is stored in MySQL. For each web page generated, I want to include meta-keywords for the search engines to use. These keywords are stored in a table in MySQL. For each CGI script, I call a subroutine located in a library file which queries the meta-keywords table for the keywords for a given script and its parameters. I need those keywords to be returned in a comma-separated list and in a string.

Here's an example of the line of code in the calling CGI script:

my $meta_keywords = &get_meta_keywords($script,$parameter);

Below is the code for the called subroutine:

sub get_meta_keywords {
  my ($script,$parameter) = @_;

  $sql_stmnt = "SELECT meta_keyword
                FROM site_meta_keywords 
                WHERE script = ?
                AND parameter = ?";
  $sth = $dbh->prepare($sql_stmnt);
  $sth->execute($script,$parameter);    
  my $keywords = $sth->fetchall_arrayref();
  $sth->finish();
    
  my $meta_keywords = join(', ', @$keywords);
    
  return $meta_keywords;
}

[download]

This returns the correct number of elements, separated by commas, but each element is an array and not text. I realize that this is part of the nature of the fetchall_arrayref() from DBI. However, I should think that there is a way I can dereference this, properly. For some background understanding, I created this simple script to test my use of join with a referenced array:

#!/usr/bin/perl -w

my @keywords = qw(one two three);
my $keywords = \@keywords;
my $meta_keywords = join(',', @$keywords);

print $meta_keywords, "\n";

exit;

[download]

The code above returns the following text:

one,two,three

The difference, of course and again, is that fetchall_arrayref() returns not only a reference to an array, but the array it references is an array of arrays. Meaning, although I'm only selecting one column in MySQL, I could as easily select multiple columns and thereby each element of the array that's referenced contains an array of all of the columns I select. In this case only one element is contained in each sub-array.

A simple solution to my problem would be to do the following:

sub get_meta_keywords {
  ...
  
  my @keywords; 
  while(my ($keyword) = $sth->fetchrow_array()) {
     push(@keywords, $keyword);
  }
  $sth->finish();
    
  my $meta_keywords = join(', ', @keywords);
  
  return $meta_keywords;
}

[download]

This works just fine. If you've read this far, you may be wondering what is my question and what is my problem. It seems to me that I should be able to retrieve the whole array in one line and then dereference the array to the level I need, and not have to resort to a while statement. I know it's not that much extra code, but it seems amateurish and I'm trying to improve my coding skills. So, if you know a way to tighten up this code, please let me know. It seems like I should be able to do something like, $@keywords->[0] (which doesn't work either) inside the join. Please let me know if there is a way to do this or if there isn't anything better than using a while as I've done, so that I can give up. Thanks.

-Spenser

That's Spenser, with an "s" like the detective.

In reply to join and fetchall_arrayref() by Spenser

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.