help deriving mysql query from perl script

[alison-g]

Greetings great and generous monks

I've inherited the task of extracting data from a mysql dbase.

My predecessor used this perl script called from a browser. It just keeps timing out - and I don't know if it's really a time out or a script error and I know nothing about perl and I can't get my cpanel shell access to work and they want the data yesterday and they are a non profit group recording marine creature sightings and have no money and AAARGH.

I thought the simplest way might just be to figure out what the mysql query equivalent would be then I can just run it directly on the dbase

<if that's even possible??

anywhere here is the code and insert relevant deity here bless youse all!!

#!/usr/bin/perl

        $whichmethod = $ENV{'REQUEST_METHOD'};
        if($whichmethod eq "GET"){ 
            $forminfo = $ENV{"QUERY_STRING"};
        }else{
        read(STDIN, $forminfo, $ENV{'CONTENT_LENGTH'});
        } 

      @key_value_pairs = split(/&/,$forminfo); 

      foreach $pair (@key_value_pairs){
           ($key,$value) = split(/=/,$pair);
           $value =~ s/\+/ /g; 
           $value =~ s/%([0-9a-fA-F][0-9a-fA-F])/pack("C", hex($1))/eg
+;
           $FORM_DATA{$key} = $value;
      }


$where_clause = $FORM_DATA{'where_clause'};
$group_clause = $FORM_DATA{'group_clause'};

print "Content-type: text/html\n\n";

$cginame="http://$ENV{'SERVER_NAME'}$ENV{'SCRIPT_NAME'}";


print "<form action='$cginame' method=post>\n";

print "<p><b>Enter the where clause (boolean condition)</b>:<br>";
print "For example, event <> \"Training\"";
print "<textarea rows='3' cols='80' name=where_clause></textarea>\n";

print "<p><b>Enter the group clause (list of fields separated by #).</
+b><br>";
print "Potential fields are any from the rwsurvey, rwlit, rworganism, 
+rwlocation, rwRHSites, rwSeasons.<br>";
print "Examples are:<br>";
print "&nbsp;&nbsp;rh_name [Reef Health site name]"; 
print "&nbsp;&nbsp;year(date); month(date);<br>";
print "&nbsp;&nbsp;season; if(month(date)<12,year(date),year(date)+1) 
+[the year but with December included in the following year]<br>";
print "Therefore seasonal data summaries would be:<br>";
print "&nbsp;&nbsp;rh_name#if(month(date)<12,year(date),year(date)+1)#
+season<br>";

print "<textarea rows='3' cols='80' name=group_clause></textarea>\n";
print "<br><input type=submit></form>\n";


die unless length($where_clause) > 0;

$where_clause = "rwsurvey.type = 'LIT' and ".$where_clause;


@groups = split('#',$group_clause); 
$group_clause = join(",",@groups);
        

use DBI;
$dbh = DBI->connect("DBI:mysql:reefwatch:xxxxxxxxxxxxxx", "xxxxxxxx", 
+"xxxxxxx");
if (!$dbh) {
    $dbh = DBI->connect("DBI:mysql:reefwatch:localhost", "root", "mysq
+l");
       die "Cannot connect: $D: $DBI::errstr\n"
       unless $dbh;
    print "<p>Connected locally</p>";
}



$linked_tables = "rwsurvey, rwlit, rworganism, rwRHSites, rwSeasons, r
+wlocation where rwlit.organism_id = rworganism.org_id and rwsurvey.su
+rvey_id = rwlit.survey_id and rwRHSites.site_id = rwsurvey.site_id an
+d rwRHSites.exposure = rwsurvey.exposure and rwlocation.site_id = rws
+urvey.site_id and season_id = if(month(date)>0,floor(month(date) % 12
+/3),-1) and ";

$output_counter= 0;

############################################################
#
# Get summary of surveys and metres for each group
#
############################################################
$stmt = "select $group_clause, count(distinct rwlit.survey_id), count(
+distinct CONCAT(rwsurvey.survey_id,'_',metre)) from $linked_tables $w
+here_clause GROUP BY $group_clause";

#print "<p>$stmt"; #com

$sth = $dbh->prepare($stmt) || print "Can't prepare $stmt: ".$dbh->err
+str()."\n";

$sth->execute || print "can't execute the query: ".$sth->errstr()."\n"
+;

while (@res = $sth->fetchrow)
{ 
    $line = join(',',@res);

    push (@group_val_list, $line);
}

#print @group_val_list; #com

############################################################
#
# Extract the data for each group
#
############################################################

foreach $line (@group_val_list)
{
    $output_counter++;

    print "A,$output_counter,$line<br>";

    @vals = split (/,/, $line);
    $last_id = $#vals - 2;
    @vals = @vals[0..$last_id];

    $extra_clause = "";
    for ($i=0; $i<=$#vals; $i++)
    {
    $extra_clause .= " and $groups[$i] = '$vals[$i]'";
    }

    ############################################################
    #
    # Type B: Raw data
    #
    ############################################################

    $stmt = "SELECT rwsurvey.survey_id, metre, transition, code from $
+linked_tables $where_clause $extra_clause order by rwsurvey.survey_id
+, metre, transition";

#    print "<p>$stmt</p>";

    $sth = $dbh->prepare($stmt) || print "Can't prepare $stmt: ".$dbh-
+>errstr()."\n";

    $sth->execute || print "can't execute the query: ".$sth->errstr().
+"\n";

    $last_survey_id = -1;
    $total_metres = 0;
    $total_surveys = 0;
    undef %cover;

    while (($survey_id, $metre, $transition, $code) = $sth->fetchrow) 
    {
    $output_counter++;
    print "B,$output_counter,$survey_id,$metre,$transition,$code<br>";

    if ($survey_id != $last_survey_id)
    {
        $total_surveys++;

        if ($last_survey_id != -1)
        {
        $total_metres += ($last_metre - $min_metre + 1);
        }
        $last = ($metre-1)*100;
        $min_metre = $metre;
        if (($transition != 0) or ($code != 'START')) 
        {
        $output_counter++;
        print "D,$output_counter,'Problem: $survey_id, $last_survey_id
+, $transition, $code'<br>";
        }

    }
    else
    {

        $current = ($metre-1) * 100 + $transition;
        $last_metre = $metre;
        $delta = $current - $last;
        #print "delta = $delta<br>";
        $cover{$code} += $delta;
        if ($delta < 0)
        {
        $output_counter++;
        print "D,$output_counter,'Problem: negative $current, $last, $
+metre, $transition, $code'<br>";
        }
        
        $last = $current;
    }

    $last_survey_id = $survey_id;

    }

    $total_metres += ($last_metre - $min_metre + 1);

    #print "<p>$total_surveys surveys (total of $total_metres metres)"
+;


    ############################################################
    #
    # Type C: Percentage cover
    #
    ############################################################

    while (($lifeform, $total_length) = each(%cover))
    {
    $percent_cover = sprintf("%0.1f",$total_length / $total_metres);
    $val_list = join(',', @vals);

    $output_counter++;
    print "C,$output_counter,$val_list,$lifeform,$total_length,$percen
+t_cover<br>";
    }
}