Cannot figure out why SQL DELETE FROM statement returns an error of "SQLSTATE[HY000]: General error: 25 bind or column index out of range "

Question

So, here's the relevant code from my page. It connects to a sqlite3 database through PDO which I update through forms on the page. I have other sqlite statements, like INSERTS and UPDATES (that does use WHERE id=:id) that work no problem. This DELETE one does not, however. I do have all the code in a try catch block on my page (which is how I got the error, if you were wondeing) but I figured I can omit it here.

Thanks for the help!

<?php
    $db = new PDO("sqlite:osuat.sqlite3");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $id = $_POST['id'];
    $update = "DELETE FROM pages
                WHERE id=:id";
    $stmt = $db->prepare($update);
    $stmt->bindParam(':id', $id);
    $stmt->execute();
?>

are you sure `$_POST['id']` is set with an valid pk for this table? — Rufinus, Nov 18 '12 at 05:17
Rufinus: I'm not sure what valid pk means, but the same `$_POST['id']` works with my update statement: `UPDATE pages SET title=:title, content=:content, pub_date=:pub_date WHERE id=:id` || mu is too short: I tried that but I got the same error. — RBurgundy, Nov 18 '12 at 05:41

score 1 · Answer 1 · answered Nov 18 '12 at 06:51

1

Try adding PDO::PARAM_INT to the bind_param method, to make sure that the value being sent is an INT (which I'm assuming your ID field is) i.e.,

$stmt->bindParam(':id', $id, PDO::PARAM_INT);

answered Nov 18 '12 at 06:51

duellsy

8,497
2
36
60

I tried that but it still doesn't work. When I use the command line for the same statement (DELETE FROM pages WHERE id=1), it DOES work, so I'm not sure what the problem could be. – RBurgundy Nov 18 '12 at 16:51
Try logging the SQL so that you can see exactly what is being run, I'm thinking that the $id value may not actually be getting through as you are expecting it to. See this: http://stackoverflow.com/a/1813818/1613391 – duellsy Nov 19 '12 at 02:11

score 0 · Answer 2 · answered Nov 18 '12 at 21:22

0

Echo the actual sql statement and die prior to actually running it. Then inspect and run the statement directly. I suspect $_POST['id'] doesn't contain what you think it does.

answered Nov 18 '12 at 21:22

NotMe

87,343
27
171
245

score 0 · Accepted Answer · answered Nov 19 '12 at 14:09

I finally figured it out. In my actual page, I have a bunch of if-else constructs in order to construct the correct $update string. I (wrongly) figured that I could just use bindParam() at the end without paying heed to how many bindParam()s each update statement would need. So, for my DELETE FROM pages WHERE id=:id, it was being supplied a whole bunch of other parameters only used in other $update strings, not just :id.

Its my fault for not including the entire source, I'm sure someone here would have caught it right away, but many thanks to duellsy, he/she led me on the right path looking for ways to log the actual SQL statement. In the end, using stmt->debugDumpParams(); helped me figure what I was doing wrong.

score -1 · Answer 4 · answered Nov 18 '12 at 21:13

-1

Try writing the DELETE command in one line.

answered Nov 18 '12 at 21:13

CL.

173,858
17
217
259

Cannot figure out why SQL DELETE FROM statement returns an error of "SQLSTATE[HY000]: General error: 25 bind or column index out of range "

4 Answers4