Funny anomaly I just can't see (or fix). I'm querying a MySQL database and getting a nice return. What I'm trying to add is the ability to query the DB using LIMIT to select the result set (say 1 - 10) and then with the click of a button return results 11 - 20. And so forth through the entire set.
jeffa most graciously helped me with the button incrementing the value of a variable yesterday. But for some reason when I try to run it in the script, the script fails on the second return.
Here's the code:
sub DoSearchDate {
my $dbh = DBI->connect('DBI:mysql:hr_db','usr','pword');
my $date_year = param("date_year");
my $date_month = param("date_month");
my $date_day = param("date_day");
my $date = ("$date_year-$date_month-$date_day");
my $types = ("\$types");
my $shade = ("\$shade");
my $default = '#E0E0E0';
my $default2 = '#FFFFFF';
my $fav = ("\$fav");
my $something = ("Top Candidate");
my $limit_3 = param("limit_3");
my $limit = param("limit");
$limit = ($limit_3 + $limit);
my $spread = ($limit + 1);
my $spread_2 = ($limit + 10);
my $sth = $dbh->prepare('SELECT COUNT(date_col) FROM applicant WH
+ERE date_col = ?');
$sth->execute($date) or die $sth->errstr;
while (my $types = $sth->fetchrow_array()) { if ($spread_2 >= $ty
+pes){$spread_2 = $types}
if ($spread >= $types){$spread = $types}
print qq(<p style="margin-left: 0; margin-top: 20"><font size="2"
+ face="Arial"><b>There are $types applications with $date listed as '
+Date Applied'.<br>Results $spread - $spread_2 shown.</b></font></p>);
}
$sth->finish();
print qq($table);
print qq($table_headers);
my $sth = $dbh->prepare('SELECT * FROM applicant WHERE date_col =
+ ? ORDER BY job_position LIMIT ?,10');
$sth->execute($date,$limit) or die $sth->errstr;
while (my @result = $sth->fetchrow_array()) {
print qq(<tr><td>$result[0], $result[1]</td></tr>\n);
}
print qq(<tr><td colspan="10"><center><form method="POST" acti
+on="return.pl"><input type="hidden" name="limit_3" value="10"><input
+type="hidden" name="limit" value="$limit"><input type="hidden" name="
+date_year" value="$date_year"><input type="hidden" name="date_month"
+value="$date_month"><input type="hidden" name="date_day" value="$date
+_day"><input type="submit" name="action" value="Next"></form></center
+></td></tr>);
print qq(</table>);
$sth->finish();
$dbh->disconnect;
}
The button calls the sub 'DoSearchDate'.
Here's the error:
DBD::mysql::st execute failed: You have an error in your SQL syntax near ''10'' at line 1 at return.pl line 588.
The variable $limit is incremented by 10 every time I push the next button. And I know that it's working. I don't understand why it doesn't work as the placeholder. I've tested the placeholder on it's own in LIMIT and it works. I just can't get the Placeholder to accept the new value when I update it.
Any help would be appreciated.
peppiv
Thanks for all the fish.......