I have a SQL Server table with department names in it (I.e. Admissions & Registration
, Women's Softball coach
) and when you click a link on our page it pulls all employees under that department however when you pull the Women's Softball coach
I get an error as below:
PHP Warning: mssql_query() [function.mssql-query]: >message: Line 1: Incorrect syntax near 's'. (severity 15) in >C:\Inetpub\wwwroot\DACC\directory\dept.php on line 179
PHP Warning: mssql_query() [function.mssql-query]: >message: Unclosed quotation mark before the character string ') ORDER BY Lastname'. >>>(severity 15) in C:\Inetpub\wwwroot\DACC\directory\dept.php on line 179
PHP Warning: mssql_query() [function.mssql-query]: >Query failed in C:\Inetpub\wwwroot\DACC\directory\dept.php on line 179
PHP Warning: mssql_query() [function.mssql-query]: message: Line 5: Incorrect syntax near 's'. (severity 15) in >C:\Inetpub\wwwroot\DACC\directory\dept.php on line 195
PHP Warning: mssql_query() [function.mssql-query]: >message: Unclosed quotation mark before the character string ' ORDER BY directory.LastName'. (severity 15) in C:\Inetpub\wwwroot\DACC\directory\dept.php >on line 195
I know this is an issue with escaping special characters but is there a way to do that in the query or do I have to do it in the table?
The code referenced above is here--->
$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");
Here is how the query is executed:
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>\n\n";
}
Here is the code that generates the department list.
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>\n\n";
}
I would strongly suggest that you use prepared statement and then execute it using the variable:
$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);
}
}
See PHP documentation on prepared statement for more info.
In your specific case, you'd have something like this:
$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();
...
}