in reply to Q & A

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>` }
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>` }
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

Replies are listed 'Best First'.
Re: Re: Q & A
by repson (Chaplain) on Dec 15, 2000 at 10:46 UTC
    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)
    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.