Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

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.

Re: Trouble updating value of variable for a placeholder
by BUU (Prior) on Jun 14, 2002 at 17:40 UTC
    im fairly certain you cannot use a placeholder in limit statement..
      I've tried using
      my $sth = $dbh->prepare('SELECT * FROM applicant WHERE date_col = ? OR +DER BY job_position LIMIT ?'); $sth->execute($date,10) or die $sth->errstr;

      and this worked. I've even tried using placeholders for both LIMIT values.

        stajich Thanks for the help. I've tried printing $limit. It returns '0' when I first call the sub, which is what I want. LIMIT 0,10 returns rows 1-10. The second time I call it it returns 10, which is also what I want. LIMIT 10,10 returns rows 11-20. But for some reason it bugs out.

        Still getting the error:

        You have an error in your SQL syntax near ''10',10' at line 1 at /usr/local/etc/httpd/cgi-bin/ line 590.

        Which is where LIMIT is used.



Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://174614]
Approved by Kanji
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (4)
As of 2018-03-20 01:05 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (247 votes). Check out past polls.