Table columns in CGI 1

JimJx · Sep 9, 2007

Hi all, I have a script that someone else wrote that I am trying to do some small mods to. The only thing I have left to do is have the output in a 2 column table instead of a 1 column.

Any suggestions?

Thanks for reading!
Jim

Code:

my $dbh = DBI->connect('DBI:mysql:farthing_valleyweb','farthing_farthin','ginajim')
                or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM valley WHERE category = ?')
                or die "Couldn't prepare statement: " . $dbh->errstr;
				
# Collect parameters that determine where we are in the display.
# Default to beginning of result set, 10 records/page if parameters
# are missing/malformed.

my $cat1 = param("search1");
my $cat2 = param("search2");
$search = param("search");

if ($cat1) { $search = $cat1 };
if ($cat2) { $search = $cat2 };

my $start = param ("start");
$start = 1
    if !defined ($start) || $start !~ /^\d+$/ || $start < 1;

my $per_page = param ("per_page");
$per_page = 10
    if !defined ($per_page) || $per_page !~ /^\d+$/ || $per_page < 1;;

# If start > 1, then we'll need a live "previous page" link.
# To determine whether or not there is a next page, try to select one
# more record than we need.  If we get that many, display only the first
# $per_page records, but add a live "next page" link.

# Select the records in the current page of the result set, and
# attempt to get an extra record.  (If we get the extra one, we
# won't display it, but its presence tells us there is a next
# page.)

if ($type eq 'alpha') {
$query = sprintf (
                "SELECT name, address, city, phone
                FROM valley
				        where name like '$search%'
                ORDER BY name LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     # number of records to select
} else {

$query = sprintf (
                "SELECT name, address, city, phone
                FROM valley
				        where keywords like '%$search%'
                ORDER BY name LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     # number of records to select
}             
                
                
my $tbl_ref = $dbh->selectall_arrayref ($query);

$dbh->disconnect ( );

# Display results as HTML table
my @rows;
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
    # add cells to table
    push (@rows, Tr (td (\@cells)));
}

$page .= table ({-border => 0}, @rows) . br ( );

# If we're not at the beginning of the query result, present a live
# link to the previous page.  Otherwise present static text.

if ($start > 1)                 # live link
{
    my $url = sprintf ("%s?start=%d;per_page=%d;search=$search",
                        url ( ),
                        $start - $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "previous page") . "] ";
}
else                            # static text
{
    $page .= "[previous page]";
}

# If we got the extra record, present a live link to the next page.
# Otherwise present static text.

if (@{$tbl_ref} > $per_page)    # live link
{
    my $url = sprintf ("%s?start=%d;per_page=%d;search=$search;type=$type",
                        url ( ),
                        $start + $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "next page") . "]";
}
else                            # static text
{
    $page .= "[next page]";
}
$page .= end_html ( );

print $page;

KevinADC · Sep 9, 2007

Very hard to offer a suggestion wthout knowing what the current code produces for output as far as the table goes.

------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]

JimJx · Sep 9, 2007

Sorry about that...

Produces 1 column, in the style of:

field1
field2
fiels3
field4

field5
field6
fiels7
field8

field9
field10
fiels11
field12

What I would like is something more along the lines of
field1 field5
field2 field6
field3 field7
field4 field8

field9
field10
field11
field12
field2
fiels3
field4

chrismassey · Sep 9, 2007

Hello,

Hopefully this will help. Here is part of a code I have copied and pasted from one of my scripts (just the 2 column table output part).

Code:

my ($data, @data, $column1, $column2));

$data = 'A1,B1:A2,B2:A3,B3:A4,B4';

@data = split(/:/ , $data);

print "Content-type: text/html\n\n";
print "<table border=1>\n<tr><th 
align=left>Column 1</th><th
align=left>Column 1</th><th</tr>

foreach (@data) {
($column1, $column2) =  split (/,/);

print "<tr><td>$column1</td>
           <td>$column2</td>
       </tr>";
)

print "</table>";

The result...

Column 1 Column 2
A1 B1
A2 B2
A3 B3
A4 B4

Chris

chrismassey · Sep 9, 2007

Sorry, I didn't check code before posting... Couple of mistakes...

Code:

my ($data, @data, $column1, $column2);

$data = 'A1,B1:A2,B2:A3,B3:A4,B4';

@data = split(/:/ , $data);

print "Content-type: text/html\n\n";
print "<table border=1>\n<tr><th 
align=left>Column 1</th><th
align=left>Column 2</th><th</tr>

foreach (@data) {
($column1, $column2) =  split (/,/);

print "<tr><td>$column1</td>
           <td>$column2</td>
       </tr>";
)

print "</table>";

Chris

chrismassey · Sep 9, 2007

Code:

align=left>Column 1</th><th</tr>";

:(

KevinADC · Sep 9, 2007

all you need do is use an array slice and make as many cells as you want, so instead of this:

Code:

push (@rows, Tr (td (\@cells)));

you can do something like this:

Code:

push (@rows, Tr (td (@cells[0,1]),
                 td (@cells[2,3],
                 td (@cells[4,5]));

with array slices you can mix up the indexes to arrange the data how you want:

Code:

push (@rows, Tr (td (@cells[0,3]),
                 td (@cells[1,4],
                 td (@cells[2,5]));

it all depends on what is in @cells and how you want to layout the data.

------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]

JimJx · Sep 9, 2007

Thanks for the replies guys!

Chris, I know how to do what you proposed but it just isn't right in this situation. But thanks for taking the time to reply.

Kevin, I think your solution will work, but I have a question.... After trying to integrate it into my script it still only pulls one entry at a time. Is there any way that I can get it to pull 2 entries at a time from the database?

In other words, right now my database is like
entry1:entry2:entry3:entry4
entry5:entry6:entry7:entry8
entry9:entry10:entry11:entry12

And your addition to the script has the same problems that mine had, in addition to me not knowing how to do the table.... Only pulling entries 1-4, then 5-8, then 9-12

Is there any way to have it pull 1-4 and 5-8 while keeping them seperate so they can be placed in different td's?

Thanks for everything!
Jim

KevinADC · Sep 9, 2007

All of your data is in the array reference: $tbl_ref:

Code:

my [b]$tbl_ref[/b] = $dbh->selectall_arrayref ($query);

You can get any or all elements of the array you want during the loop that generates the html table code:

Code:

for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
    # add cells to table
    push (@rows, Tr (td (\@cells)));
}

$i is the current index number of the array that the loop is working on. If you want the very next indexed element of the array yu can use $i+1:

Code:

for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}[b]-1[/b]; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
   [b] my @cells2 = @{$tbl_ref->[$i+1]};  # get data values in row $i+1[/b]
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
[b]    @cells2 = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells2;[/b]
    # add cells to table
    push (@rows, Tr (td (\@cells),[b](td (\@cells2)[/b]));
}

I doubt that will produce the results you want but you can fiddle round with the code until you get the table to display how you want.

Note the "$i < @{$tbl_ref}-1" in the loop initiator. You have to stop looping at the next to last array index if you always want to get the current index and the next index otherwise you go past the end of the array on the last loop.

------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]

JimJx · Sep 10, 2007

That was it Kevin!

Thank you very much, have a star!

Really appreciate the help.
Jim

JimJx · Sep 17, 2007

One more thing.....

I am pulling the data from the db and getting it into 2 columns. But, the data is turning out like this:

1st Choice GMAC Real Estate
2282 N. Augusta St.
Staunton
540-886-0145
1st Choice GMAC Real Estate - Diane Woodson
213 Meadow Beauty Court
Waynesboro
540-886-0145
1st Choice GMAC Real Estate - Diane Woodson
213 Meadow Beauty Court
Waynesboro
540-886-0145
1st Choice GMAC Real Estate - Minnie Stevenson
2282 N. Augusta Street
Staunton
540-480-0648

Notice the double entries....

I am using the routine from above with some minor changes....

Code:

for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    my @cells2 = @{$tbl_ref->[$i+1]};  # get data values in row $i+1
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
    @cells2 = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells2;
    # add cells to table
    @cells="$cells[0]<br>$cells[1]<br>$cells[2]<br>$cells[3]<br>$cells[4]";
    @cells2="$cells2[0]<br>$cells2[1]<br>$cells2[2]<br>$cells2[3]<br>$cells2[4]";
    push (@rows, Tr (td (\@cells),(td (\@cells2))));
}

As always, any help is greatly appreciated.....

Jim

KevinADC · Sep 17, 2007

I think you need to change the loop initiation code:

for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)

the very last condition, the increment: $i++ change it to: $i+=2

------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]

JimJx · Sep 17, 2007

That took care of it, excellent, thanks!

Jim

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Table columns in CGI 1

JimJx

Technical User

KevinADC

Technical User

JimJx

Technical User

chrismassey

Programmer

chrismassey

Programmer

chrismassey

Programmer

KevinADC

Technical User

JimJx

Technical User

KevinADC

Technical User

JimJx

Technical User

JimJx

Technical User

KevinADC

Technical User

JimJx

Technical User

Similar threads

Part and Inventory Search

Sponsor