I am trying to connect to an existing SQL Server database using PDO with the drivers provided by Microsoft.
I have seen examples using odbc, dblib, mssql, etc., however I believe the connection string with these drivers should use 'sqlsrv'?
Are there any good examples of how to properly do this? If I should be doing this via some other method please let me know. Thanks!
Well that's the best part about PDOs is that it's pretty easy to access any database. Provided you have installed those drivers, you should be able to just do:
$db = new PDO("sqlsrv:Server=YouAddress;Database=YourDatabase", "Username", "Password");
Mind you that in my experience and also of other (PHP - Why is new SQLSRV driver slower than the old mssql driver?) that using PDO_SQLSRV is way slower than through PDO_ODBC.
If you want to use the faster PDO_ODBC you can use:
//use any of these or check exact MSSQL ODBC drivername in "ODBC Data Source Administrator"
$mssqldriver = '{SQL Server}';
$mssqldriver = '{SQL Server Native Client 11.0}';
$mssqldriver = '{ODBC Driver 11 for SQL Server}';
$hostname='127.0.0.1';
$dbname='test';
$username='user';
$password='pw';
$dbDB = new PDO("odbc:Driver=$mssqldriver;Server=$hostname;Database=$dbname", $username, $password);
$mssqldriver = 'ODBC Driver 11 for SQL Server';
This works for me, and in this case was a remote connection: Note: The port was IMPORTANT for me
$dsn = "sqlsrv:Server=server.dyndns.biz,1433;Database=DBNAME";
$conn = new PDO($dsn, "root", "P4sw0rd");
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sql = "SELECT * FROM Table";
foreach ($conn->query($sql) as $row) {
print_r($row);
}
Figured this out. Pretty simple:
new PDO("sqlsrv:server=[sqlservername];Database=[sqlserverdbname]", "[username]", "[password]");
try
{
$conn = new PDO("sqlsrv:Server=$server_name;Database=$db_name;ConnectionPooling=0", "", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
$e->getMessage();
}
$servername = "";
$username = "";
$password = "";
$database = "";
$port = "1433";
try {
$conn = new PDO("sqlsrv:server=$servername,$port;Database=$database;ConnectionPooling=0", $username, $password,
array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
echo ("Error connecting to SQL Server: " . $e->getMessage());
}
Download the Microsoft Drivers for PHP for SQL Server from Microsoft: https://learn.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver16
Extract the following files based on the version of PHP you have installed. Mine was PHP 8.2.1
Copy these files to the php ext folder
Add the following extensions to the php.ini file:
Enable the following extensions:
Make the connection:
$conn = new PDO("sqlsrv:Server=localhost;Database=$DatabaseName", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo 'PDO SQL Server Connection successful</br>';
