Insert failing with SQLITE & PHP PDO - General error: 25 bind or column index out of range

Question

I'm attempting to insert some data into my database using a PHP PDO function within the Slim framework but when I'm posting with curl I'm getting the following error;

SQLSTATE[HY000]: General error: 25 bind or column index out of range

The curl command I'm using is;

curl -i -X POST -H 'Content-Type: application/json' -d '{"category_name":"cat3",     "item_name":"test"}' http://localhost/api/add

The PHP function in question is;

Erroring Function

function addContent() {
    $request = \Slim\Slim::getInstance()->request();
    $content = json_decode($request->getBody());
    $sql = "insert into category (category_name) values (:category_name);
    insert into item (category_id,item_name) values 
    ((select category_id from category where category_name = :category_name2),     :item_name);";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam("category_name", $content->category_name);
        $stmt->bindParam("category_name2", $content->category_name);
        $stmt->bindParam("item_name", $content->item_name);
        $stmt->execute();
        $db = null;
        echo json_encode($content);
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

The sql basically adds a new category into the category table and then adds a new item into the item table which references the newly created category. If I run the query within sqlite then it seems to add the required rows just fine, the query is;

insert into category(category_name) values ('cat2');
insert into item (category_id,item_name) values
((select category_id from category where category_name = 'cat2'),'item3');

I have something similar written for categories which already exists which seems to work fine;

Similar Working Function

function addContent() {
    $request = \Slim\Slim::getInstance()->request();
    $content = json_decode($request->getBody());
    $sql = "insert into item (category_id,item_name) values
((select category_id from category where category_name = :category_name), :item_name);";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam("category_name", $content->category_name);
        $stmt->bindParam("item_name", $content->item_name);
        $stmt->execute();
        $db = null;
        echo json_encode($content);
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

From reading around I believe I need to set the two :category_name references separately, which is why I've got :category_name & :category_name2. Otherwise, as far as my knowledge extends this should be working as expected. Any advice is greatly appreciated.

Working Function courtesy of IMSoP & jeroen's answers

This is now working as required although perhaps isn't the most pretty of solutions but does the job. Thanks for the advice guys.

function addContent() {
    $request = \Slim\Slim::getInstance()->request();
    $content = json_decode($request->getBody());
    $sql_category = "insert into category (category_name) values (:category_name);";
    $sql_item = "insert into item (category_id,item_name) values 
((select category_id from category where category_name = :category_name),     :item_name);";
    try {
        $db = getConnection();

        $stmt_category = $db->prepare($sql_category);
        $stmt_category->bindParam("category_name", $content->category_name);
        $stmt_category->execute();

        $stmt_item = $db->prepare($sql_item);
        $stmt_item->bindParam("category_name", $content->category_name);
        $stmt_item->bindParam("item_name", $content->item_name);
        $stmt_item->execute();

        $db = null;
        echo json_encode($content);
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

score 1 · Accepted Answer · answered Mar 13 '13 at 22:26

1

The SQL string you're trying to prepare contains 2 SQL statements. I don't know for sure, but it's likely SQLite (or the PDO driver) won't support this, so will only have prepared one of them, meaning the placeholders in the other are not defined.

Try separating it into two separate statements, each prepared and executed separately.

answered Mar 13 '13 at 22:26

IMSoP

89,526
13
117
169

Thanks, I've managed to get this working as required now, see the addition to the question. – chrisdwheatley Mar 14 '13 at 12:21

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

The problem can be that PDO cannot execute multiple sql statements in one query. To see if that is the problem, just split the queries into different calls.

~~You can also check here if your system meets the requirements to run multiple queries in PDO.~~

edited May 23 '17 at 10:32

Community

1
1

answered Mar 13 '13 at 22:24

jeroen

91,079
21
114
132

According to the tags, this is SQLite, not MySQL – IMSoP Mar 13 '13 at 22:28
@IMSoP You're right, the link / last paragraph does not apply. – jeroen Mar 13 '13 at 22:31

Insert failing with SQLITE & PHP PDO - General error: 25 bind or column index out of range

Erroring Function

Similar Working Function

Working Function courtesy of IMSoP & jeroen's answers

2 Answers2