nlafferty has asked for the wisdom of the Perl Monks concerning the following question:

I want to make this display only the rows of data that do not have a value in the total_time (data type INTEGER) field. Along with the oid of each row displayed. 
#!/usr/bin/perl

use CGI qw/:standard/;
require "common.sub";

print header;

&Create_DB_Connection;
&get_info;
&print_results;

# Disconnect from the database
$dbh->disconnect;


################ BEGIN GET INFO SUBROUTINE
sub get_info{
  $SQL="SELECT oid,* FROM timeclock WHERE total_time = 0 ORDER BY l_na
+me";
  #my $sth = &Do_SQL;
  &Do_SQL;
}
################ END GET INFO SUBROUTINE


################ BEGIN PRINT RESULTS SUBROUTINE
sub print_results{
  print <<HTML;
   <HTML><HEAD><TITLE>Pennswoods.net - Timeclock Search</TITLE></HEAD>
   <BODY BGCOLOR="#FFFFFF">
   <CENTER><FONT SIZE=6 FACE=ARIAL>Search Results</FONT></CENTER>
    <HR WIDTH=80%>
     <P>
     <CENTER><TABLE BORDER=1 CELLSPACING=0>
      <TR>
       <TD BGCOLOR="#D7D3B9" COLSPAN=11><FONT FACE=ARIAL>
        <CENTER><B>Pennswoods.net - Employee Timeclock Database</B></C
+ENTER>
       </FONT></TD>
      </TR> 
    <TR>
<TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Delete</B></CENTER></FONT></T
+D>
     
<TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Oid</B></CENTER></FONT></TD>
      <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Employee ID</B></CENTER></FON
+T></TD>
          <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Date</B></CENTER></FONT></TD>
     <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Name</B></CENTER></FONT></TD>
     <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Clocked In</B></CENTER></FONT
+></TD>
     <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Clocked Out</B></CENTER></FON
+T></TD>
     <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Total Time</B></CENTER></FONT
+></TD>
     <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Start Stamp</B></CENTER></FON
+T></TD>
     <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>End Stamp</B></CENTER></FONT>
+</TD>
     <TD BGCOLOR="D7D3B9">
      <FONT SIZE=2 FACE=ARIAL><CENTER><B>Comment</B></CENTER></FONT></
+TD>
          
     </TR>
HTML

  while ($pointer = $sth->fetchrow_hashref){
    $oid = $pointer->{'oid'};
    $emp_id = $pointer->{'emp_id'};
    $date = $pointer->{'date'};
    $f_name = $pointer->{'f_name'};
    $l_name = $pointer->{'l_name'};
    $start_hour  = $pointer->{'start_hour'};
    $start_minute  = $pointer->{'start_minute'};
    $end_hour  = $pointer->{'end_hour'};
    $end_minute  = $pointer->{'end_minute'};
    $total_time  = $pointer->{'total_time'};
    $start_stamp  = $pointer->{'start_stamp'};
    $end_stamp  = $pointer->{'end_stamp'};
    $comment  = $pointer->{'comment'};


    
   ## Make our HTML look better if no data.
    $oid = "&nbsp;" if($oid eq "");
    $emp_id = "&nbsp;" if($emp_id eq "");
    $date = "&nbsp;" if($date eq "");
    $start_hour  = "&nbsp;" if($start_hour eq "");
    $start_minute  = "&nbsp;" if($start_minute eq "");
    $end_hour = "&nbsp;" if($end_hour eq "");
    $end_minute = "&nbsp;" if($end_minute eq "");
    $start_stamp  = "&nbsp;" if($start_stamp eq "");
    $end_stamp  = "&nbsp;" if($end_stamp eq "");
    $total_time  = "&nbsp;" if($total_time eq "");
    $comment  = "&nbsp;" if($comment eq "");

   print <<HTML;
    <TR BGCOLOR="#FFFFFF">
     <TD><FONT SIZE=1 FACE=ARIAL>
      [<A HREF="/cgi-bin/nick/contact/delete.cgi?$oid">Delete</A>]
     </FONT></TD>

     <TD><FONT SIZE=2 FACE=ARIAL>$oid</FONT></TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$emp_id</FONT></TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$date</FONT></TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$f_name $l_name</FONT></TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$start_hour:$start_minute</A></FONT><
+/TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$end_hour:$end_minute</FONT></TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$total_time</FONT></TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$start_stamp</FONT></TD>
     <TD><FONT SIZE=2 FACE=ARIAL>$end_stamp</A></FONT></TD>
          <TD><FONT SIZE=2 FACE=ARIAL>$comment</FONT></TD>
          
   </TR>
HTML
  } # End of while.

   print<<HTML;
    </TABLE></CENTER>
    <P>
     <HR WIDTH=80%>
    <P>

    <CENTER>
     <FONT SIZE=4>
      <A HREF="http://devel.pennswoods.net/nick/index.html">Main Page<
+/A>
     </FONT>
    </CENTER>
   </BODY></HTML>
HTML
} # End of print_results subroutine
################ END PRINT RESULTS SUBROUTINE

[download]

Replies are listed 'Best First'.
Re: Display row with no value in column
by rucker (Scribe) on Jul 20, 2001 at 21:36 UTC
    I'm not sure I understand this question. Are you trying to print rows where total_time is null?  SELECT oid,* FROM timeclock WHERE total_time IS NULL ORDER BY l_name
[reply]
[d/l]
      A lot depends on the RDMS or ODBC driver (if using a flat file). IS NULL works with MySQL if the date field is set to not null but if it isn't, you have to use 
      .... WHERE total_time = '' ...

      [download]
[reply]
[d/l]
      total_time = NULL. that works terrific. Thanks. I just didn't really know how to specify the correct value for an empty field
[reply]
Re: Display row with no value in column
by dsb (Chaplain) on Jul 20, 2001 at 21:51 UTC

    First of all, you need to load DBI.pm into your script for any of the database functions you are using to work. Then you have to connect to the database.

    I've got 2 words for you:
    perldoc DBI :-)

    You really need to read the docs for that(that command will get them for you), and perhaps even give yourself a rundown on packages and modules. Rework your code after that then come back and ask.

    For now a little demonstration of DBI.pm: 

    #!/usr/bin/perl

use DBI;
use strict;

# connect to the database, using mysql in example
my $dbh = DBI->connect("DBI:mysql:dbname:hostname", "user", "pass") or
+ die "Couldn't Connect: $DBI::errstr";

# prepare statement handle
$sth = $dbh->prepare("SELECT * FROM table WHERE field = ?);  
# ? is a placeholder that can be filled when execute is called
$sth->execute($val_of_field) or die "Error: $DBI::errstr";
# $DBI::errstr stores the error string returned by the DB

while (@vals = $sth->fetchrow->array()) {
    # do stuff
    # do more stuff
}
$sth->finish();       #close statement handle
$dbh->disconnect();   #disconnect

    [download]
    You also need to make sure that DBI.pm is installed on your system. Type:
    perl -e 'use DBI;' at your command line. If its installed nothing will happen, if not you'll get an error like: 
    Can't locate Blah.pm in @INC (@INC contains: /home/harvester/lib/perl5
+/i386-linux /home/harvester/lib/perl5 /usr/lib/perl5/5.00503/i386-lin
+ux /usr/lib/perl5/5.00503 /usr/lib/perl5/site_perl/5.005/i386-linux /
+usr/lib/perl5/site_perl/5.005 .) at -e line 1.
BEGIN failed--compilation aborted at -e line 1.

    [download]
    Really you should just start out by reading some docs and getting a better understanding of how to use DBI.pm

    Amel - f.k.a. - kel

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom