我有一个网站正在从我的MSSQL Server中提取数据。我正在使用函数为报表构建表。这是我得到的:
function BeginTable($rowCount,$headings,$searchValue,$ReportName,$OneButton,$NewSearch)
{
try{
$StateSelectSQL = "select distinct State from pmdb.MaterialTracking where State is not null";
var_dump($StateSelectSQL);echo " What!<br>";
$getSelect = $conn->query($StateSelectSQL);
var_dump($getSelect);echo " When!<br>";
$StateSelectNames = $getSelect->fetchALL(PDO::FETCH_ASSOC);
var_dump($StateSelectNames);echo " Where!<br>";
}
catch(Exception $e)
{
echo "Something went wrong";
die(print_r($e->getMessage()));
}
我也试过这个:
try{
$StateSelectSQL = "select distinct State from pmdb.MaterialTracking where State is not null";
var_dump($StateSelectSQL);echo " What!<br>";
$getSelect = $conn->prepare($StateSelectSQL);
$getSelect->execute();
//$getSelect = $conn->query($StateSelectSQL);
//var_dump($getSelect);echo " When!<br>";
$StateSelectNames = $getSelect->fetchALL(PDO::FETCH_ASSOC);
var_dump($StateSelectNames);echo " Where!<br>";
}
catch(Exception $e)
{
echo "Something went wrong<br>";
die( print_r( $e->getMessage()));
}
第二个和第三个var_dump
从不显示任何内容,其余代码(此处未显示)也不会运行。如果我注释掉$getSelect
和$StateSelectNames
行(下面有var_dump
行),那么其他一切都可以。
这是我的DBConn.php文件,它位于Function
:之上的included
$conn = new PDO("sqlsrv:server=$servername;database=$dbname", $username,$password);
//set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 10);
$getSelect = $conn->query($StateSelectSQL);
这条线怎么了?我想不通。我稍后尝试在foreach
中使用它,如下所示:
foreach($conn->query($StateSelectSQL) as $StateName)
但这也不起作用。它又停在这条线上,不再往前走了。我唯一能想到的是我的SQL被搞砸了,但当我在SSMS中运行它时,它运行得很好!
怎么回事?
在使用fetchAll之前,请尝试准备并执行SQL。如果你还没有启用异常模式,也可以考虑启用异常模式并将你的语句包装在try-catch中——这应该标记出任何问题(例如,你的应用程序数据库用户没有访问模式的权限,或者语法错误等)
例外情况:
有关如何启用的信息,请参阅此堆栈溢出帖子
对于您的代码:
try {
$sql = "
SELECT DISTINCT State
FROM pmdb.MaterialTracking
WHERE State IS NOT NULL
";
$sth = $conn->prepare($sql);
$sth->execute();
$rowset = $sth->fetchAll(PDO::FETCH_ASSOC);
print_r($rowset);
} catch PDOException($err) {
echo "Something went wrong".
echo $err;
}
我拔了一整天的头发,终于想通了!我必须在function
中include
我的DBConn.php。在这之后,它起了作用。我不知道为什么这很重要,因为文件开头是included
。如果有人能解释为什么会这样,我将不胜感激!
现在看起来是这样的:
function BeginTable($rowCount,$headings,$searchValue,$ReportName,$OneButton,$NewSearch)
{
try{
include("DBConn.php");
$SelectSQL = "select distinct State from pmdb.MaterialTracking where State is not null order by State";
$getSelect = $conn->prepare($SelectSQL);
$getSelect->execute();
$StateSelectNames = $getSelect->fetchALL(PDO::FETCH_ASSOC);
}
catch(Exception $e)
{
echo "Something went wrong<br>";
die( print_r( $e->getMessage()));
}