我有一个SQL Server表,表中有部门名称(即Admissions & Registration
、Women's Softball coach
),当您单击页面上的链接时,它会将该部门下的所有员工拉入,但当您拉入Women's Softball coach
时,我会得到如下错误:
PHP警告:mssql_query()[function.msql-query]:>消息:第1行:"s"附近的语法不正确。(严重性15)在>C:\Inetpub\wwwroot\DACC\directory\dept.php的179 行
PHP警告:mssql_query()[函数.msql查询]:>消息:字符串")ORDER BY Lastname"之前的引号未闭合。>>(严重性15)在C:\Inetpub\wwwroot\DACC\directory\dept.php的179 行
PHP警告:mssql_query()[function.msql-query]:>第179行C:\Inetpub\wwwroot\DACC\directory\dept.PHP中的查询失败
PHP警告:mssql_query()[function.msql-query]:消息:第5行:"s"附近的语法不正确。(严重性15)在>C:\Inetpub\wwwroot\DACC\directory\dept.php的第195行
PHP警告:mssql_query()[function.msql-query]:>消息:字符串'之前的引号未闭合ORDER BY目录。姓氏'。(严重性15)在第195行的C:\Inetpub\wwwroot\DACC\directory\dept.hp>中
我知道这是一个转义特殊字符的问题,但有没有办法在查询中做到这一点,或者我必须在表中这样做?
上面引用的代码在这里--->
$department = $_GET['dept'];
// This will evaluate to TRUE so the text will be printed.
if (isset($department)) {
// Send a select query to MSSQL
$query = mssql_query("SELECT * FROM directory WHERE department IN (SELECT id FROM departments WHERE name='$department') ORDER BY Lastname");
以下是查询的执行方式:
function listDepts() {
$query = "SELECT DISTINCT name FROM departments ORDER BY name";
$result = mssql_query($query);
echo "<h3>Please select a department:</h3>n";
echo "<ul>n";
for ($i=0; $i<mssql_num_rows($result); $i++) {
$info = mssql_fetch_assoc($result);
echo "<li><a href="dept.php?dept=$info[name]">$info[name]</a></li>n";
}
echo "</ul>nn";
}
这是生成部门列表的代码。
function listDepts() {
$query = "SELECT DISTINCT name FROM departments ORDER BY name";
$result = mssql_query($query);
echo "<h3>Please select a department:</h3>n";
echo "<ul>n";
for ($i=0; $i<mssql_num_rows($result); $i++) {
$info = mssql_fetch_assoc($result);
echo "<li><a href="dept.php?dept=$info[name]">$info[name]</a></li>n";
}
echo "</ul>nn";
}
我强烈建议您使用prepared语句,然后使用变量执行它
$stmt = $dbh->prepare("SELECT * FROM directory WHERE department IN (SELECT id FROM departments WHERE name=?) ORDER BY Lastname");
if ($stmt->execute(array("Women's Softball coach"))) {
while ($row = $stmt->fetch()) {
print_r($row);
}
}
有关更多信息,请参阅有关prepared语句的PHP文档。
在你的具体情况下,你会有这样的东西:
$stmt = $dbh->prepare("SELECT * FROM directory WHERE department IN (SELECT id FROM departments WHERE name=?) ORDER BY Lastname");
for ($i=0; $i<mssql_num_rows($result); $i++) {
if ($stmt->execute(array($result))) {
$info = $stmt->fetch();
...
}