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:
Help with MySQL

 



hwnd
User

Feb 21, 2013, 6:34 AM

Post #1 of 4 (740 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 / Moderator

Feb 21, 2013, 7:13 AM

Post #2 of 4 (736 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 (720 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 / Moderator

Feb 22, 2013, 12:38 PM

Post #4 of 4 (716 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) Powered by Gossamer Forum v.1.2.0

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