Integration of SQL Search Results Into An Existing HTML Template

[Milti]

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 [e-mail] that would be fine as well. Thanks, Milt Spain

[marinersk]

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:

Read more... (3 kB)

Results:

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

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

Cheers!

[CountZero]

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.

[TJPride]

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.

[Jim]

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.)

[Milti]

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?

[Anonymous Monk]

See open, print, and Watching long processes through CGI (Aug 02)

[Anonymous Monk]

DBIx::XHTML_Table

perlintro#Simple substitution $html =~ s{\Q

\E}{$table};
DBIx::XHTML_Table
DBIx_XHTML_Table usage - Urgent Please
DBIx::XHTML_Table - Cookbook

[Anonymous Monk]

 $html =~ s{\Q<div id='replacemewithdatabasetable'></div>\E}{$table};