从PDO到SQLSRV [英] From PDO to SQLSRV

问题描述

我使用PDO for MYSql之前，但现在我需要使用Microsoft SQL Server驱动程序PHP。

I used PDO for MYSql before but now i need to use Microsoft SQL Server Driver for PHP.

我找到了手册。
http://php.net/manual/en/book.sqlsrv.php

我使用示例＃2连接到新的SQL数据库：
http://www.php.net/manual/en/function.sqlsrv-connect.php

I use Example #2 to connect to the new SQL database: http://www.php.net/manual/en/function.sqlsrv-connect.php

如何'转换'以下（PDO mysql）使用sqlsrv：

How do I 'convert' the following (PDO mysql) to work with sqlsrv:

$username = 'test';

try {
$conn = new PDO('mysql:host=$host;dbname=$database', $username, password);
$stmt = $conn->prepare('SELECT username users WHERE username = :username LIMIT 1');
$stmt->execute(array('username' => $username));

if ($stmt->rowCount() > 0) {
$result = $stmt->fetch();

    echo $result['username'];

} else {
    echo 'Nothing found.';
die();
}
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}

推荐答案

解决连接问题后，您必须将使用MySQL SQL方言写入的SQL语句转换为T-SQL方言（由SQL Server使用）。

After solving the connections issues, you have to convert the SQL statement written using MySQL SQL dialect to T-SQL dialectal (used by SQL Server).

[1] SQL服务器没有 LIMIT 子句。更多，此子句不包括在SQL标准中。相反，您可以使用 ROW_NUMBER（）函数。但是，在这种情况下，您不必使用此函数，因为...

[1] SQL Server doesn't has the LIMIT clause. More, this clause is not included in SQL standard. Instead, you can use ROW_NUMBER() function. But, in this case, you don't have to use this function because ...

[2] ... < c $ c> username 列从 users 表应该只接受唯一的值（当然，它应该是强制性的）。因此，您必须确保在 username 列上有唯一约束或唯一索引。此脚本将检查您是否在 username 列上有唯一索引：

[2] ... the username column from users table should accept only unique values (and, off course, it should be mandatory). So, you have to be sure that you have an unique constraint or an unique index on username column. This script will check if you have an unique index on username column:

DECLARE @TableName SYSNAME=N'dbo.users';
DECLARE @ColumnName SYSNAME=N'username';
SELECT  *
FROM    sys.indexes i
WHERE   i.object_id=OBJECT_ID(@TableName)
AND     i.is_unique=1
AND     EXISTS (
    SELECT  *
    FROM    sys.index_columns ic 
    INNER JOIN sys.columns c ON c.object_id=ic.object_id
    AND     c.column_id=ic.column_id
    WHERE   i.object_id=ic.object_id
    AND     i.index_id=ic.index_id
    AND     ic.is_included_column=0
    AND     c.name=@ColumnName
)
AND (
    SELECT  COUNT(*)
    FROM    sys.index_columns ic 
    INNER JOIN sys.columns c ON c.object_id=ic.object_id
    AND     c.column_id=ic.column_id
    WHERE   i.object_id=ic.object_id
    AND     i.index_id=ic.index_id
    AND     ic.is_included_column=0
)=1;

[3] c> username 列，那么您不需要使用 LIMIT 1 子句（或 ROW_NUMBER c $ c> function），因为SQL语句将总是返回零（最小）或一个（最大）记录。

[3] If you have an unique index on username column then you don't need to use the LIMIT 1 clause (or ROW_NUMBER() function) because the SQL statement will return always zero(min.) or one(max.) record(s).

[4] SQL语句应为 SELECT username FROM users WHERE username =：param 。

这篇关于从PDO到SQLSRV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！