CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
CGI::Application and MySQL

 



Iconx
Novice

Mar 3, 2015, 4:27 PM

Post #1 of 18 (6836 views)
CGI::Application and MySQL Can't Post

I've been looking around the net for some good examples but I'm missing something.

Can someone show me an example of using CGI::Application with HTML::Template to query a mysql database...say just "select * from dvd" and displaying it into the template? Just real basic and simple. Mainly I cant figure out how to return the result set from mysql into the template.

Iconx


FishMonger
Veteran / Moderator

Mar 4, 2015, 7:15 AM

Post #2 of 18 (6801 views)
Re: [Iconx] CGI::Application and MySQL [In reply to] Can't Post


Quote
Can someone show me an example of using CGI::Application with HTML::Template to query a mysql database

Sorry, we can't do that because neither of those modules are used to query a database.

The modules you're looking for are DBI and DBD::mysql
http://search.cpan.org/~timb/DBI-1.633/DBI.pm
http://search.cpan.org/~capttofu/DBD-mysql-4.029/lib/DBD/mysql.pm

If your query returns multiple rows, then the data structure you pass to the template will be an array of hash refs. The template would use the TMPL_LOOP tag to loop over that array.
http://search.cpan.org/~wonko/HTML-Template-2.95/lib/HTML/Template.pm#TMPL_LOOP


(This post was edited by FishMonger on Mar 4, 2015, 7:15 AM)


Iconx
Novice

Mar 4, 2015, 10:02 AM

Post #3 of 18 (6789 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

Yes I realize that, I'm using the DBI module. Can I just get an example? We can skip the main file and just go straight to the .pm file, and skip all the initialization stuff. Just when I have queried 'select * from dvd' how do I send it to the template. Im looking at your TMPL_LOOP article, but could I have a more straight forward example.

So I have:

Code
while(@row = $sth->fetchrow_array){ 
(how do I send results to the template from here)...
}



FishMonger
Veteran / Moderator

Mar 4, 2015, 11:16 AM

Post #4 of 18 (6778 views)
Re: [Iconx] CGI::Application and MySQL [In reply to] Can't Post

You don't send it to the template from within the while loop. The while loop builds an array of hashes and after the while loop you assign the array to a template var.

Here's an example from one of my retired apps.

Code
my @search_results = search_abook($form{searchfield}, $form{searchvalue}); 

$template->param(results => \@search_results);

print $template->output;

#--------------

sub search_abook {
my ($field, $search_pattern) = @_;

# stripped code that sets up the db connection and sql statement

if ($sth->rows > 0) {
while (my ($owner, $full, $first, $last, $email, $label) = $sth->fetchrow_array) {
my %account = (
owner => $owner,
fullname => $full,
firstname => $first,
lastname => $last,
email => $email,
label => $label,
);
push @accounts, \%account;
}
}
$dbh->disconnect;
return @accounts;
}


Here's the related code in the template file.

Code
    <tmpl_loop name="results"><tr> 
<td><tmpl_var name="owner"></td>
<td><tmpl_var name="fullname"></td>
<td><tmpl_var name="firstname"></td>
<td><tmpl_var name="lastname"></td>
<td><tmpl_var name="email"></td>
<td><tmpl_var name="label"></td>
<tmpl_if NAME="gic">
<td><a href="modify.pl?locate=1&amp;id=<tmpl_var name="owner">">mod</a></td>
<td><a href="delete.pl?locate=1&amp;id=<tmpl_var name="owner">">del</a></td>
</tmpl_if>
</tr>
</tmpl_loop>


I used fetchrow_array but you could use fetchrow_hashref instead which would allow to avoid manually assigning the hash data like I did.


(This post was edited by FishMonger on Mar 4, 2015, 11:18 AM)


Zhris
Enthusiast

Mar 4, 2015, 12:25 PM

Post #5 of 18 (6765 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

Or one step further, generate an array of hashes in one line:


Code
my $accounts = $sth->fetchall_arrayref( { } );


Chris


FishMonger
Veteran / Moderator

Mar 4, 2015, 12:33 PM

Post #6 of 18 (6758 views)
Re: [Zhris] CGI::Application and MySQL [In reply to] Can't Post

I thought I added that suggestion, but I guess it was only in my thought process and never reached my keyboard. :)


Zhris
Enthusiast

Mar 4, 2015, 12:52 PM

Post #7 of 18 (6753 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

It happens to the best of us. My posts are nearly always covered in multiple edits, often because of that very reason. I'm surprised I remembered, I've been using DBIx::Class for along time now, it makes life easier.

Chris


FishMonger
Veteran / Moderator

Mar 4, 2015, 1:27 PM

Post #8 of 18 (6745 views)
Re: [Zhris] CGI::Application and MySQL [In reply to] Can't Post

I've often thought about using DBIx::Class but never have. The main reason is probably due to a decision change we had in upper management. Some key management person doesn't like Perl so now all my perl apps need to be rewritten in PHP (which I can't stand using). The odd thing is that a good portion of our most important 3rd party apps (such as RT) are written in Perl and we're not about to get rid of them. Even stranger is that they like writing apps in AutoHotKey and say it's better than perl.


Zhris
Enthusiast

Mar 5, 2015, 10:18 AM

Post #9 of 18 (6712 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

Definitely worth playing around with it if you find the time, or when you begin a personal project, I only wish I had used it sooner. I'm with you on PHP too, I learnt both PHP and Perl together early on, but wouldn't even consider using PHP for a new project nowadays, I can only describe PHP from my experience as being clunky.


FishMonger
Veteran / Moderator

Mar 5, 2015, 10:27 AM

Post #10 of 18 (6709 views)
Re: [Zhris] CGI::Application and MySQL [In reply to] Can't Post

I'd describe it as clunky, convoluted and promotes obfuscation.


(This post was edited by FishMonger on Mar 5, 2015, 10:27 AM)


Iconx
Novice

Mar 11, 2015, 11:47 AM

Post #11 of 18 (6654 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

Ok that works thanks, Now how do I do wildcard matching from perl within a sql query. Example:

Code
my $sql; 
if ($search_type eq 'Title'){
$sql = "SELECT * FROM dvd WHERE Title Like ? and Format=?";
}


I want my sql query to be more like 'SELECT * FROM dvd WHERE Title Like % ? % and Format=?";
This above obviously cant work, so how is it taken care of typically?


(This post was edited by Iconx on Mar 11, 2015, 11:48 AM)


FishMonger
Veteran / Moderator

Mar 11, 2015, 12:58 PM

Post #12 of 18 (6646 views)
Re: [Iconx] CGI::Application and MySQL [In reply to] Can't Post


Code
my $sql;  
if ($search_type eq 'Title'){
$sql = "SELECT * FROM dvd WHERE Title Like ? and Format=?";
}

my $sth = $dbh->prepare($sql);
$sth->execute('%value%', 'my_format');



(This post was edited by FishMonger on Mar 11, 2015, 12:59 PM)


Iconx
Novice

Mar 12, 2015, 1:08 PM

Post #13 of 18 (6629 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

No wait, I dont understand,

Currently its

$sth->execute($search_string,$media_format) or die "SQL Error\n";

search_string and media_format are shifted into this sub, media format is typically DVD,Blu-ray or Both.

Search_string though is the title in this example.
where did %value% come from? and whats my_format?

please elaborate


FishMonger
Veteran / Moderator

Mar 12, 2015, 1:26 PM

Post #14 of 18 (6622 views)
Re: [Iconx] CGI::Application and MySQL [In reply to] Can't Post

Well, you never showed your execute statement so I based it off of the only clue I had from your #11 post.


Quote
I want my sql query to be more like 'SELECT * FROM dvd WHERE Title Like % ? % and Format=?";

Maybe I should have used "%$search_type%" instead of '%value%'


Iconx
Novice

Mar 12, 2015, 1:36 PM

Post #15 of 18 (6620 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

I realize my failure in providing the info, thanks, that makes sense now.


Iconx
Novice

Mar 12, 2015, 1:44 PM

Post #16 of 18 (6616 views)
Re: [Iconx] CGI::Application and MySQL [In reply to] Can't Post

ok im getting errors, im still missing something,
Software error:

syntax error at MyApp/User.pm line 83, near "%,"
Compilation failed in require at /var/www/cgi-bin/index.cgi line 5.
BEGIN failed--compilation aborted at /var/www/cgi-bin/index.cgi line 5.

heres the code.....correct me if there's a better way.....


Code
package MyApp::User; 
use strict;
use warnings;

use base 'CGI::Application';
use CGI::Application::Plugin::DBH (qw/dbh_config dbh/);
use CGI::Application::Plugin::ConfigAuto (qw/cfg cfg_file/);
use DBI;
use Data::Dumper;

#------------------------------------------------------------------------------
# Define our run modes
#------------------------------------------------------------------------------
sub setup {
my $self = shift;
$self->start_mode('search_DB');
$self->error_mode('error');
$self->mode_param('rm');
$self->run_modes(
'search_DB' => 'search_DB',
'display_results' => 'display_results',
);
}

#------------------------------------------------------------------------------
# Process any fatal errors
#------------------------------------------------------------------------------
sub error {
my $self = shift;
my $error = shift;
return "There has been an error: $error";
}

sub database{
my $self = shift;
my $search_type = shift;
my $media_format = shift;
$media_format = "DVD/Blu-Ray" if($media_format eq 'Both');
my ($search_string) = shift;

my @flicks;
my $dbh = DBI->connect('dbi:mysql:movies','mvdb','garbage') or die "Connection Error: $DBI::errstr\n";

my $sql;
if ($search_type eq 'Title'){
$sql = "SELECT * FROM dvd WHERE Title LIKE % ? % and Format=?";
}
elsif ($search_type eq 'Year'){
$sql = "SELECT * FROM dvd WHERE year = ? AND Format=?";
}
elsif ($search_type eq 'Genre'){
$sql = "SELECT * FROM dvd WHERE Genre LIKE ? AND Format=?";
}
elsif ($search_type eq 'Keywords'){
$sql = "SELECT * FROM dvd WHERE Keywords LIKE ? AND Format=?";
}


my $sth = $dbh->prepare($sql) or die "Couldnt prepare statement: $DBI::errstr \n";
$sth->execute(%$search_string%,$media_format) or die "SQL Error: $DBI::errstr \n";

if ($sth->rows > 0){
while (my ($number, $title, $year, $genre, $keywords, $format, $imdb, $notes, $double) = $sth->fetchrow_array){
my %flick = (
number => $number,
title => $title,
year => $year,
genre => $genre,
keywords => $keywords,
format => $format,
imdb => $imdb,
notes => $notes,
double => $double,
);
push @flicks, \%flick;
}
}
else{
my %flick = (
number => 'not found',
title => 'not found',
year => 'not found',
genre => 'not found',
keywords => 'not found',
format => 'not found',
imdb => 'not found',
notes => 'not found',
double => 'not found',
);
push @flicks, \%flick;
}

$dbh->disconnect;
return @flicks;
}

#------------------------------------------------------------------------------
# Display the form for creating/maintaining users
#------------------------------------------------------------------------------
sub search_DB {
my $self = shift;
my $errs = shift;
my $q = $self->query;

# Process the template
my $template = $self->load_tmpl('searchDB.tmpl');
my $query = $self->query();

$template->param('showsearch_page' => 1 );

$template->param($errs) if $errs;

#Display the form
return $template->output();
}

sub display_results{
my $self = shift;
my $errs = shift;
my $query = $self->query();
my $search = $query->param('query');
my $search_type = $query->param('type');
my $media_format = $query->param('format');

my $template = $self->load_tmpl('searchDB.tmpl');

my @results = $self->database($search_type, $media_format, $search);
$template->param(showresults_page => \@results);


# $template->param(format => $format);
return $template->output;
}

1;



FishMonger
Veteran / Moderator

Mar 12, 2015, 5:19 PM

Post #17 of 18 (6612 views)
Re: [Iconx] CGI::Application and MySQL [In reply to] Can't Post

Clearly, these 2 statements aren't going to work.

Code
$sql = "SELECT * FROM dvd WHERE Title LIKE % ? % and Format=?";  
$sth->execute(%$search_string%,$media_format) or die "SQL Error: $DBI::errstr \n";


Since $sql is being assigned in an if/elsif block and a single execute statement after that, you need to adjust the value of $search_string as needed inside that block.


Code
	my $sql;  
if ($search_type eq 'Title'){
$search_string = '%' . $search_string . '%';
$sql = "SELECT * FROM dvd WHERE Title LIKE ? and Format=?";
}
elsif ($search_type eq 'Year'){
$sql = "SELECT * FROM dvd WHERE year = ? AND Format=?";
}
elsif ($search_type eq 'Genre'){
$sql = "SELECT * FROM dvd WHERE Genre LIKE ? AND Format=?";
}
elsif ($search_type eq 'Keywords'){
$sql = "SELECT * FROM dvd WHERE Keywords LIKE ? AND Format=?";
}


my $sth = $dbh->prepare($sql) or die "Couldnt prepare statement: $DBI::errstr \n";
$sth->execute($search_string, $media_format) or die "SQL Error: $DBI::errstr \n";



Iconx
Novice

Mar 24, 2015, 3:58 PM

Post #18 of 18 (5059 views)
Re: [FishMonger] CGI::Application and MySQL [In reply to] Can't Post

Awesome it works now, that was the hard part thanks for the assist.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives