PHP通过PDO连接MSSQL数据库驱动问题求助

Hey Darren, I get how frustrating it is when something you thought would be straightforward turns into a headache—especially when you're new to PHP. Let's walk through fixing your MSSQL connection problem step by step.

First, Diagnose the Core Issues in Your Current Code #

Looking at your test code, I notice a few key gaps that might be driving those driver errors:

• You defined $mssqldriver but didn't actually use it in your PDO connection string
• There's no error-catching to show you exactly what's failing (critical for debugging generic "driver issues")
• We need to make sure your connection string aligns properly with the ODBC Driver 13 your host installed

Step 1: Add Error Catching to Reveal Exact Problems #

Before tweaking the connection, let's wrap your code in a try/catch block. This will spit out specific error messages (like driver mismatches, login failures, or port issues) instead of vague errors.

Step 2: Test Two Connection Methods (Aligned with Your Host's Setup) #

Your host confirmed ODBC Driver 13 is installed and pdo_sqlsrv is enabled for PHP 5.6. Let's test both the dedicated SQL Server driver and the ODBC fallback to find what works.

Option 1: Use the pdo_sqlsrv Driver (Host Says It's Enabled)

This is the native SQL Server driver for PHP. We'll explicitly reference the ODBC driver in the connection string (some environments require this to map correctly):

<html>
<head>
<title>Page Title</title>
</head>
<body>
<?php
// First, check if required extensions are loaded (critical for debugging!)
echo "Loaded extensions:<br>";
print_r(get_loaded_extensions());
echo "<br><br>";

$mssqldriver = '{ODBC Driver 13 for SQL Server}';
$hostname='my.ip,1433'; // Add port 1433 (default SQL Server port) unless your host uses a different one
$dbname='dbname';
$username='user';
$password='pass';

try {
    // Explicitly link the ODBC driver to the sqlsrv connection
    $conn = new PDO("sqlsrv:Server=$hostname;Database=$dbname;Driver=$mssqldriver", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 1);
    
    echo "Connection successful!<br><br>";
    
    $query = 'select * from salesdata';
    $stmt = $conn->query($query);
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['agent'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>
</body>
</html>

Option 2: Fallback to the ODBC PDO Driver

If the above fails, try using the ODBC driver directly (since your host has ODBC Driver 13 installed):

<html>
<head>
<title>Page Title</title>
</head>
<body>
<?php
echo "Loaded extensions:<br>";
print_r(get_loaded_extensions());
echo "<br><br>";

$mssqldriver = '{ODBC Driver 13 for SQL Server}';
$hostname='my.ip,1433';
$dbname='dbname';
$username='user';
$password='pass';

try {
    // ODBC-style connection string
    $conn = new PDO("odbc:Driver=$mssqldriver;Server=$hostname;Database=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Connection successful!<br><br>";
    
    $query = 'select * from salesdata';
    $stmt = $conn->query($query);
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['agent'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>
</body>
</html>

Key Checks to Verify #

• Port Number: If your SQL Server uses a non-default port, replace 1433 with the correct number (ask your host if unsure)
• Credentials: Double-check your username, password, database name, and server IP—typos are a super common culprit!
• Extension Status: Look at the "Loaded extensions" output. You should see either pdo_sqlsrv or pdo_odbc listed. If neither appears, follow up with a2hosting to confirm they enabled the right extension for your PHP 5.6.34 version.

If You Still Hit Snags #

Share the exact error message from the catch block, and we can narrow it down further. For example:

• "Could not find driver" means the extension isn't loaded properly
• "Login failed" points to credential/IP access issues

内容的提问来源于stack exchange，提问作者Darren Fletcher