Perl Guru Forums: Perl Programming Help: Intermediate: Help with MySQL

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Intermediate:
Help with MySQL

hwnd
User

Feb 21, 2013, 6:34 AM

Post #1 of 4 (163 views)

Help with MySQL	Can't Post

This code works fine for me to connect to the database and with using the template system but when it prints the output from getting the records from the database it is only printing the last record in that database instead of looping and returning all records. Any advice?

Code

  
 
My template file holds this information for the output:  
 
        <TMPL_LOOP NAME=DB_RECORDS> 
         <tr> 
           <td class="therecord"><TMPL_VAR NAME=ID></td> 
           <td class="therecord"><TMPL_VAR NAME=DATE></td> 
           <td class="therecord"><TMPL_VAR NAME=AUTHOR></td> 
           <td class="therecord"><TMPL_VAR NAME=HEADLINE></td> 
           <td class="therecord"><TMPL_VAR NAME=NEWS></td> 
         </tr> 
        </TMPL_LOOP>

I am trying to fetch the records with fetch but it is only returning the last record:

Code

  
 
while ( $sth->fetch ) { 
 $template->param(DB_RECORDS => [ {   id       => $news_id, 
           date     => $news_date, 
           author   => $news_author, 
           headline => $news_headline, 
           news     => $news_text }, 
                                  ]); 
}

Code

  
 
Entire code:  
 
#!/usr/bin/perl -T  
 
use strict;             
use warnings; 
use CGI qw(:standard); 
use DBI;  
 
use HTML::Template;  
 
my $template = HTML::Template->new(filename => 'templates/newsboard.tmpl');  
 
 
 my $dbh = DBI->connect('DBI:mysql:updatesdb:mysql-updatesdb.liveforfaith.com', 
     '*****', '*****') or die $DBI::errstr;  
 
 my $query = "select id, date, author, headline, news FROM news";  
 
 my $sth = $dbh->prepare($query); 
    $sth->execute();  
 
 my ($news_id, $news_date, $news_author, $news_headline, $news_text ); 
 $sth->bind_columns( undef, \$news_id, \$news_date, \$news_author, \$news_headline, \$news_text );  
 
 while ( $sth->fetch ) { 
 $template->param(DB_RECORDS => [ {   id       => $news_id, 
           date     => $news_date, 
           author   => $news_author, 
           headline => $news_headline, 
           news     => $news_text }, 
                                  ]); 
}  
 
print header, $template->output;

FishMonger
Veteran

Feb 21, 2013, 7:13 AM

Post #2 of 4 (159 views)

Re: [hwnd] Help with MySQL [In reply to]	Can't Post

Each iteration of your while loop is overwriting the hash data of the prior iteration. That's why you're ending up with only the last record.

The loop needs to build an array of hash refs.

Code

my @news; 
while ( $sth->fetch ) { 
    my %news = ( 
            id       => $news_id,  
            date     => $news_date,  
            author   => $news_author,  
            headline => $news_headline,  
            news     => $news_text 
    ); 
    push @news, \%news; 
} 
$template->param(DB_RECORDS => \@news);

hwnd
User

Feb 22, 2013, 11:50 AM

Post #3 of 4 (143 views)

Re: [FishMonger] Help with MySQL [In reply to]	Can't Post

With the code you just referenced to me, im using DATE field in mysql database and the records are in format of 2013-02-22.

During the:

Code

  
 
 while ( $sth->fetch ) { 
     my %news = ( date => $news_date );  push @news, \%news; 
 } 
  
 $template->param(DB_RECORDS => \@news);

How would i be able to format that date to something like Friday, Feb 22, 2013 to output?

FishMonger
Veteran

Feb 22, 2013, 12:38 PM

Post #4 of 4 (139 views)

Re: [hwnd] Help with MySQL [In reply to]	Can't Post

It depends on how you setup the database scheme.

If the field was setup as a datetime data type, then you can use a mysql date format function in the select statement to return a formatted date string.

If it was setup as a varchar, then you'll need to use Perl to parse the string and use one of the date modules to reformat the date string as you wish.

Without doing any testing, my first thought on the manuall approach would be to split the string into the 3 fields and use the mktime function to get the datestamp. Then use the strftime function to format the new date string.

Search for (options)

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