使用ODBC将插入值PDO的ID返回到MSSQL [英] Return IDs of inserted values PDO to MSSQL using ODBC

问题描述

我需要在此函数中获取添加到数据库中的订户的ID数组:

function insert_test($pdo, $fullname, $email) {
if ($SQL = $pdo->prepare("INSERT INTO subscribers ([dateAdded],[dateUpdated],[fullname],[email],[isActive]) VALUES (GETDATE(), GETDATE(), :fullname, :email, 1)")) {
    $SQL->bindValue(':fullname', $fullname, PDO::PARAM_STR);
    $SQL->bindValue(':email', $email, PDO::PARAM_STR);
    $SQL->execute();

    return array('status'=> true);
} else {
    return array('status'=> false);
}
}

但是，我正在努力弄清楚如何获取值并将其返回到数组中，如下所示:

return array('status'=> true,'ids'=> $ids);

我已阅读并发现SCOPE_IDENTITY()可能是获取这些值的最可靠方法，但我不知道将其放在此函数中的位置以返回所需的值.数据库中的主键是id列.

会是这样吗?

if ($SQL = $pdo->prepare("INSERT INTO subscribers ([dateAdded],[dateUpdated],[fullname],[email],[isActive]) VALUES (GETDATE(), GETDATE(), :fullname, :email, 1) SELECT SCOPE_IDENTITY()"))

然后在一个名为$ ids的参数之后绑定一个参数，或者我是否会完全考虑这一点?

任何帮助将不胜感激！

编辑:我尝试使用与

此函数的返回结果为:

Array
(
    [status] => 1
    [id] => Array
        (
        ) 
)

因此，似乎输出未正常工作?这越来越陌生了...

解决方案

想通了！

closeCursor();方法的位置出现问题，该函数现在看起来像这样:

function insert_test($pdo, $fullname, $email) {
    if ($SQL = $pdo->prepare("INSERT INTO subscribers ([dateAdded],[dateUpdated],[fullname],[email],[isActive]) OUTPUT INSERTED.id VALUES (GETDATE(), GETDATE(), :fullname, :email, 1)")) {
        $SQL->bindValue(':fullname', $fullname, PDO::PARAM_STR);
        $SQL->bindValue(':email', $email, PDO::PARAM_STR);
        $SQL->execute();    
        $ids = $SQL->fetchAll(PDO::FETCH_ASSOC);
        $SQL->closeCursor();

        foreach ($ids as $id) {
            return $id['id'];
        }

    } else {
        $pdo = null;
        return false;
    }
}

因此，当insert_test($pdo, $fullname, $email);在每个包含要插入的数据的循环的a内时，该函数将根据需要返回每个id.

如果有人能看到与此相关的任何固有问题，请告诉我！

I need to get an array of id's of the subscribers added to the database within this function:

function insert_test($pdo, $fullname, $email) {
if ($SQL = $pdo->prepare("INSERT INTO subscribers ([dateAdded],[dateUpdated],[fullname],[email],[isActive]) VALUES (GETDATE(), GETDATE(), :fullname, :email, 1)")) {
    $SQL->bindValue(':fullname', $fullname, PDO::PARAM_STR);
    $SQL->bindValue(':email', $email, PDO::PARAM_STR);
    $SQL->execute();

    return array('status'=> true);
} else {
    return array('status'=> false);
}
}

However I am stuggling with figuring out how to get the values and return them within the array like so:

return array('status'=> true,'ids'=> $ids);

I have read up and found that SCOPE_IDENTITY() is probably the most reliable way of getting these values but I don't know where to put it in this function to return the values that I need. The Primary key in the database is the id column.

Would it be something like this:

if ($SQL = $pdo->prepare("INSERT INTO subscribers ([dateAdded],[dateUpdated],[fullname],[email],[isActive]) VALUES (GETDATE(), GETDATE(), :fullname, :email, 1) SELECT SCOPE_IDENTITY()"))

And then bind a parameter after that called $ids or am I over thinking this completely?

Any help would be greatly appreciated!

EDIT: I have tried using a similar function to the one in this question and was receiving an invalid cursor error so adapted it to look like this (notice the closeCursor was how to fix the invalid cursor error):

function insert_test($pdo, $fullname, $email) {
    if ($SQL = $pdo->prepare("INSERT INTO subscribers ([dateAdded],[dateUpdated],[fullname],[email],[isActive]) OUTPUT INSERTED.id VALUES (GETDATE(), GETDATE(), :fullname, :email, 1)")) {
        $SQL->bindValue(':fullname', $fullname, PDO::PARAM_STR);
        $SQL->bindValue(':email', $email, PDO::PARAM_STR);
        $SQL->execute();
        $SQL->closeCursor();
        $ids = $SQL->fetchAll(PDO::FETCH_ASSOC);

        return array('status'=> true, 'id' => $ids);
    } else {
        $pdo = null;
        return array('status'=> false);
    }
}

The return from this function is now this:

Array
(
    [status] => 1
    [id] => Array
        (
        ) 
)

So it seems the output is not working as it should? This is getting stranger and stranger...

解决方案

Figured it out guys!

There was a problem with the position of the closeCursor(); method the function now looks like this:

function insert_test($pdo, $fullname, $email) {
    if ($SQL = $pdo->prepare("INSERT INTO subscribers ([dateAdded],[dateUpdated],[fullname],[email],[isActive]) OUTPUT INSERTED.id VALUES (GETDATE(), GETDATE(), :fullname, :email, 1)")) {
        $SQL->bindValue(':fullname', $fullname, PDO::PARAM_STR);
        $SQL->bindValue(':email', $email, PDO::PARAM_STR);
        $SQL->execute();    
        $ids = $SQL->fetchAll(PDO::FETCH_ASSOC);
        $SQL->closeCursor();

        foreach ($ids as $id) {
            return $id['id'];
        }

    } else {
        $pdo = null;
        return false;
    }
}

So when insert_test($pdo, $fullname, $email); is within a for each loop containing the data being inserted the function returns each id as desired.

If anyone can see any inherent problems with this please let me know!

这篇关于使用ODBC将插入值PDO的ID返回到MSSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！