Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

This node falls below the community's threshold of quality. You may see it by logging in.

Replies are listed 'Best First'.
Re: Q & A
by eg (Friar) on Dec 14, 2000 at 23:50 UTC

    In perl, the best way to access a database is probably with DBI. The best way to display (presumably you mean over the web) the questions/answers is with CGI.

    No, it's not much of an answer. But then again it's not much of a question either.

    Do you have anything specific?

[reply]
Re: Q & A
by bnanaboy (Beadle) on Dec 15, 2000 at 03:59 UTC
    I use a script with 2 cases.
    (leaving out the HTML for the background, etc., and assuming $dbi is the connection to your database, which has a table named QA consisting of 3 columns, Questions, Answers, and ID)

    1st case: 
    my $statement = $dbh->prepare( "SELECT Questions, ID from QA;" );
$statement->execute();
my ($question, $id);
$statement->bind_columns( \$question, \$id );
print "<ul>\n";
while ($statement->fetch) {
   print qq`
   <li><a href="[script_name]?case=2&id=$id">$question</li>`
}

    [download]
    2nd case: 
    my $id = $cgi->param( 'id' );
my $statement = $dbh->prepare( qq`SELECT Questions, Answers from QA wh
+ere ID = "$id";` );
$statement->execute();
my ($question, $answer);
$statement->bind_columns( \$question, \$answer );
while ($statement->fetch) {
   print qq`
<h2>$question</h2>
<p>$answer</p>`
}

    [download]
    The script calls itself, passing the ID of the question selected. This way both output pages will have the same format, regardless of future changes made, without having to update multiple files. Using fetch() also retrieves the information one line at a time, so your query won't give you a 10gig hash (as Ovid pointed out).
    ID is a primary key auto-increment column used to identify each entry in the database. If you don't use that, you could also index Questions and do a select statement using something like WHERE Question like "[first 15 letters]%";, but this is the easiest way I've found. I'm sure there's a better way to do it, though, and I'm always open for suggestions/instruction
[reply]
[d/l]
[select]
      One minor point of style/performance is that it is often nice to use placeholders. 
      my $sth = $dbh->prepare( 'SELECT Questions, Answers from QA where ID =
+ ?' );
$sth->execute($id);
# put your bind_columns and fetches in
# and if you really want then you can do
$sth->finish;
$sth->execute($id2);
# and get another set of data (thought this situation doesn't look lik
+e it wants that)

      [download]
      This technique is know as using placeholders and has several advantages to your technique of interpolation in the SQL statement.
      • Statements prepared are cached for performance, which is no use if you keep on changing them slightly
      • If data you are interpolating contains characters such as " or ' they will break the statement. To solve this you can run all the data through $dbh->quote(), but placeholders are easier than that.
      • Placeholders are cool :)
      • They allow easy SQL statement reuse if you are applying a query multiple times with only the parameters tested in the WHERE clause are changing.
[reply]
[d/l]
Re: Q & A
by coreolyn (Parson) on Dec 14, 2000 at 23:51 UTC

    I say you should just use one call to the db and pull them into a hash, but wait five to ten minutes and probability says someone here will show me I'm wrong

    Learning is the process of withstanding ego pain :)

    coreolyn
[reply]
      And if you wind up with 10 gig of data in a hash? eg, I think, provided a reasonably vague answer to a reasonably vague question. Part of coming up with acceptable solutions is developing good specifications. I find that if I define the question clearly enough, I often don't need to ask anyone else for the answer.

      Hope that wasn't too painful :)

      Cheers,
      Ovid

      Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

[reply]
Re: Q & A
by Anonymous Monk on Dec 15, 2000 at 00:13 UTC
    Ok, Sorry all for the vagueness. Basically, I need to figure out how the best way to store the data after I retrieve it from the database. Im using DBI and CGI to display the data. After fetching the data, I don't know wheter to store it in an array, hash, etc. and how to do it the best way. After that, I need to display the questions in a list as links and when clicked on will display the corresponding answer.
[reply]
      I would store them as a hash. Possibly a nested data structure if I needed more info. This would be good: 
      %q_and_a = ( '105' => 'How do I use Netscape',
             '111' => 'How do I listen to MP3s',
             '119' => 'How can I send email with attachments'
            );

      [download]
      You can cycle through each question like this: 
      foreach (keys(%q_and_a)) {
    print "Q: <a href=answers.cgi?q=$_>$q_and_a{$_}</a>\n";
}

      [download]
      This is a simple hash, and the keys are the unique identifiers for each question in the database. So when someone clicks on the link, your script would capture the id number and then pull out the answer and display it.

      Sample output: 
      Q: <a href=answers.cgi?q=119>How can I send email with attachments</a>
Q: <a href=answers.cgi?q=111>How do I listen to MP3s</a>
Q: <a href=answers.cgi?q=105>How do I use Netscape</a>

      [download]
[reply]
[d/l]
[select]
      If you're retrieving the data with DBI, you should be able to iterate through doing something like : 
      while ( @row = $sth->fetchrow_array ) {
    print "<A HREF=$row[1]>$row[0]</A><P>\n";
  }

      [download]
      This snippet assumes you've already retrieved the Q&As, and have the Qs in row 0, and the link to the A in row 1. See also DBI's pod.
[reply]
[d/l]

Back to Seekers of Perl Wisdom