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

The Perl Guestbook script I use prints the form results ($guestbookreal) to an html page (http://example.com/new_listings_publish.html):

For example, here is a typical form result: 
INSERT INTO `mysql_db` (`Title`, `Email`, `City`,
`State`, `Country`, `URL`, `Date`, `Description`,
`rid`, `dt_create`, `publish` ) VALUES ('Test',
'example.com', 'Sebastopol', 'Ca', 'USA ',
' ', 'Friday, February 13, 2004
', 'Test', '', 'NOW()', 0)

[download]
I then post the ads to MySQL by copying and pasting the INSERT INTO statements into "Run SQL query/queries" window on the phpMyAdmin interface provided by my host and hitting "GO."

Though the above method works, I'd like to bypass the copy & paste step if possible. Can somebody please be so kind as tell me how (or if it's even possible) I need to change the following code so that it will write the results directly to MySQL instead??

For example:
$guestbookreal = "/home/flute/example-www/new_listings_publish.html";

# becomes (and I know this isn't legit...):
$guestbookreal = substr(
$sth = $dbh->prepare($guestbookreal) execute);

[download]
# anyhow...something like that perhaps??

Many thanks in advance,

-Bob

Edited by BazB: added code tags

Replies are listed 'Best First'.
Re: Modifying Perl script to write to MySQL??
by CountZero (Bishop) on Feb 14, 2004 at 22:58 UTC
    Almost there:

    $sth=$dbh->prepare($guestbookreal);
$sth->execute();

    [download]

    However, it is better if you use placeholders and put all your parameters as an argument of execute. That way all necessary quoting is done by the DBI/DBD.

    And of course, you would also like to do some error checking, so you are sure the data made it into your database.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]
      Thanks so much CountZero! :-)

      That looks very much like what I thought might do it.
      I tried inserting your code (after the $guestbookurl line) but unfortunately something is returning a "500 Internal Server Error" ...

      So here's the script (obviously there's something I'm not seeing that's causing the snag...):
      Read more... (8 kB)
      Thanks once again for your help with this,

      -Bob
[reply]
[d/l]
        Please don't use programs from Matt's Script Archive. They really aren't very good and you'll learn nothing but bad practices from reading that code. The guestbook at nms is a drop-in replacement for Matt's that is written in something approaching decent Perl :)
        --
        <http://www.dave.org.uk>

        "The first rule of Perl club is you do not talk about Perl club."
        -- Chip Salzenberg

[reply]
        I think you have inserted the code I proposed too early. At that place in the script the SQL-statement is not yet written and moreover $guestbookreal just holds the name of the file where the SQL-statement will end up.

        So you will have to retrieve the contents of that file (you can do that between the 

        close (GUEST);
$dbh->disconnect;

        [download]

        statements and then plug it into a dbh->prepare statement there. You can then execute the SQL and put the data in your database.

        Why Matt goes through so many hoops to do something which is rather easy to do, no-one at Perlmonks has ever been able to understand.

        CountZero

        "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]
[select]
Re: Modifying Perl script to write to MySQL??
by dbwiz (Curate) on Feb 14, 2004 at 22:46 UTC
[reply]
      Hi dbwiz,

      < Hello? Ever heard about the DBI? DBD::mysql?

      yes, I'm already using DBI in the script (don't know about DBD::mysql yet though, sorry)

      < Ever seen our Tutorials? Reading from a database? Tricks with DBI? DBI Recipes?

      no, I didn't know about them... thanks for the tip. :-)

      I just checked the "DBI Recipes" but didn't see anything that covered the way I'm trying to modify the script -
      namely, run the INSERT INTO statement as it is presently outputting to the HTML page to execute/write to MySQL instead.
      Is that covered somewhere??

      Thanks again,

      -Bob
[reply]

Back to Seekers of Perl Wisdom