Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Trouble updating value of variable for a placeholder

by peppiv (Curate)
on Jun 14, 2002 at 16:29 UTC ( #174614=perlquestion: print w/replies, xml ) Need Help??

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

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=""><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 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.


Thanks for all the fish.......

Replies are listed 'Best First'.
Re: Trouble updating value of variable for a placeholder
by stajich (Chaplain) on Jun 14, 2002 at 16:50 UTC
    Improper SQL there is tripping you up - pretty sure you don't want the ... LIMIT > ?, 10 in your code - you just want the placeholder so chg to  ... LIMIT > ?.
      Thanks for the help. But LIMIT ?,10 should work.

      The first value in LIMIT is the offset - which result to start returning from (starts at 0) And the second value is the amount of rows to return.

      If the placeholder value equaled 2 then the statement LIMIT 2,10 would yield results 3 - 13. I've put a static value in the LIMIT and it worked correctly.

        Clearly I was being dumb, sorry that I posted without checking. Lesson learned about how LIMIT should work now.

        There must be something wrong somewhere else as I was actually able to make the following work just fine on mysql with prepare and execute stmts. Try printing out your '$limit' string and/or doing a validate if( $limit !~ /^\d+$/) { die("limit was '$limit' which is NaN"); }

        "SELECT * FROM tablea WHERE id > ? LIMIT ?,?" "SELECT * FROM tablea LIMIT ?,?" "SELECT * FROM tablea LIMIT ?,10" "SELECT id from tablea LIMIT ?,10" "SELECT * from tablea ORDER BY id LIMIT ?,10"
Re: Trouble updating value of variable for a placeholder
by thor (Priest) on Jun 16, 2002 at 04:02 UTC
    well, if it comes down to it, you may have to prepare your SQL with the limit already in it.
    my $sth = $dbh->prepare('SELECT * FROM applicant WHERE date_col=? ORDE +R BY job_position LIMIT $limit,10'); $sth->execute($date) or die $sth->errstr;
    Of course, you will have to prepare it every time, so use this as a last resort. Also, notice how I typed the first placeholder. Some DBDs require this (I was frustrated as hell until I discovered this was the case for Sybase).



      Workaround Solution

      I found a bug report on the web and a workaround solution. Thought some people might want to know this.

      my $sth = $dbh->prepare('SELECT * FROM applicant WHERE date_col = ? OR +DER BY job_position LIMIT ?,10'); $sth->bind_param(2, $limit, DBI::SQL_INTEGER); $sth->execute($date,$limit) or die $sth->errstr;

      Sometimes when using placeholders, especially for LIMIT, the DBD gets a little confused on what type of value it's being given. Line 2 binds the string $limit to an integer value. The "2" in the parenthesis is telling it the second string in the 'execute'. If you needed it for the first string it would be sth->bind_param(1, $date, DBI::SQL_INTEGER);
      Hey it worked for me. Maybe it can help someone else out.

A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://174614]
Approved by Kanji
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2021-06-19 12:53 GMT
Find Nodes?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)

    Results (92 votes). Check out past polls.