Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Integration of SQL Search Results Into An Existing HTML Template

by Milti (Sexton)
on Sep 20, 2013 at 22:36 UTC ( #1055076=perlquestion: print w/replies, xml ) Need Help??

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

I have a MySQL database and am able to use a PERL cgi script to output search results to my browser in plain text. However, what I want to do is to use an existing HTML file (a template with header, some text, etc.) and integrate the search results into a designated space within that file and output the resultant page to my browser when the cgi is called.

I haven't been able to find any information as to how to accomplish my mission so I am seeking your help. The use of PERL with databases is completely new to me. Any help that anyone can provide will be greatly appreciated. If you would like to contact me at that would be fine as well. Thanks, Milt Spain

  • Comment on Integration of SQL Search Results Into An Existing HTML Template

Replies are listed 'Best First'.
Re: Integration of SQL Search Results Into An Existing HTML Template
by marinersk (Priest) on Sep 21, 2013 at 00:05 UTC

    I am not sufficiently familiar with the part about modifying contents on the webpage; sounds like Perl generating JavaScript to me.

    Now, the DBI stuff, I can give you a working example:

    Using SQLite to demonstrate:

    C:\Steve\Dev\PerlMonks\P-2013-09-21@0144-DBI-Select-Example>"C:\App\SQ +Lite\sqlite3.exe" test.db SQLite version 3.3.5 Enter ".help" for instructions sqlite> CREATE TABLE FOO ( SEQNUM integer primary key, BAR character(3 +0) ); sqlite> insert into FOO values (1, 'George'); sqlite> insert into FOO values (2, 'Mike'); sqlite> .mode col sqlite> .header on sqlite> select * from FOO; SEQNUM BAR ---------- ---------- 1 George 2 Mike sqlite> .exit

    And here's how you read the data into an array of hashes, which won't display on the output unless you make it happen:


    C:\Steve\Dev\PerlMonks\P-2013-09-21@0144-DBI-Select-Example>perl dbi-s ROW: SEQNUM = '1' BAR = 'George' ROW: SEQNUM = '2' BAR = 'Mike'

    So all you have to do is not do the display loop


Re: Integration of SQL Search Results Into An Existing HTML Template
by CountZero (Bishop) on Sep 21, 2013 at 09:10 UTC
    What you want is a Template Engine. I am very fond of Template Toolkit, but there are many others. Just search CPAN for "template".

    What you do is, you write the HTML page and replace the variable parts (where the output of your database query will go) with Template-Toolkit variables and then your CGI-script provide the data to the Template-Toolkit variables and you run the Template engine to combine both and output it to the browser.

    If you want to go one step further, explore the power of Web-frameworks such as Dancer or Catalyst where the templates and databases and all other important stuff (authentication, authorisation!) all come seamlessly together.


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
Re: Integration of SQL Search Results Into An Existing HTML Template
by TJPride (Pilgrim) on Sep 22, 2013 at 05:44 UTC
    1) Put a tag in your template where you want the SQL results to appear. Something like <SQLDATA>

    2) Run the SQL query

    3) Convert the results into a basic HTML table, with some ID or class that you can use to style the table using CSS. This table should be put into a variable.

    4) Load the template file into another variable

    5) Replace the tag from step 1) with the contents of your table variable.

    6) print "Content-Type: text/html\n\n";

    7) Print out your converted template.

Re: Integration of SQL Search Results Into An Existing HTML Template
by Jim (Curate) on Sep 20, 2013 at 23:14 UTC

    Your mission is easily accomplished by simply blending the HTML markup and the cached search result data together. You do this by reading the two files either simultaneously or successively and incorporating the contents of one into the other. This is a pretty standard scripting task, so it shouldn't be difficult.

    (It's Perl, not PERL.)

      The cgi I'm using is intended to generate an html page and output it to the inquirer's browser on the fly. I want to immediately display the search results but I want to open my template (to write to it and save as a different file. Then open the new file and display it. I haven't been able to get the SQL results to write to that file. They are simply displayed on my browser. Historically I have been able to create an array of files contained in a directory, for instance, and have the elements printed on a contents template. How do I get the SQL results and not have them displayed immediately?

Re: Integration of SQL Search Results Into An Existing HTML Template
by Anonymous Monk on Sep 20, 2013 at 23:14 UTC
       $html =~ s{\Q<div id='replacemewithdatabasetable'></div>\E}{$table};

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1055076]
Approved by Jim
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2022-01-24 22:29 GMT
Find Nodes?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:

    Results (65 votes). Check out past polls.