>>> Programming >> PHP > PHP How to connect to MSSQL server with ADODB (This page has been seen 896 times)
PHP How to connect to MSSQL server with ADODB
This is an example on how to connect to MSSQL server with ADODB and
PHP.
First we open the connection to the server. The best way for you to do this would be to include a file in your page that has the connection parameters. That way you can easily change it later.
To connect do the following
$myServer = "localhost"; //This would be the ip address of your server running mssql
$myUser = "MyUser"; //This would be the username that you have assigned on your server with access rights to the database
$myPass = "MyPass"; //This would be the password for the user you have assigned on your server with access rights to the database
$myDB= "MyDatabase"; //This would by the Database Name
$conn = new COM ("ADODB.Connection")
or die("Cannot start ADO");
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr);
Now you have opened the connection. Lets say you want to select something, then you could do something like this
$select = "SELECT * FROM MyDatabaseTable";
$rs = $conn->execute($select);
if(!$rs->EOF){
while(!$rs->EOF){
echo $rs->Fields['MyField']->value."<br>";
$rs->MoveNext();
}
}
$rs->Close();
$rs = null;
Now when you have selected from the database you want to close the connection to the server to free up resources
$conn->Close();
$conn = null;
Remember allways to do a $rs->Close when you have selected something. This closes the recordset and then the $rs = null clears the recordset. This is important because you want to save resources. Also remember to do the $conn->Close() and $conn = null when you are done on a page, if you dont do that you will keep opening a new connection to the database which will cause it to lock you out eventually. You only have to $rs->Close a SELECT because a SELECT returns a recordset, you dont have to do it when you UPDATE, INSERT or anything else that doesnt return a recordset.
First we open the connection to the server. The best way for you to do this would be to include a file in your page that has the connection parameters. That way you can easily change it later.
To connect do the following
$myServer = "localhost"; //This would be the ip address of your server running mssql
$myUser = "MyUser"; //This would be the username that you have assigned on your server with access rights to the database
$myPass = "MyPass"; //This would be the password for the user you have assigned on your server with access rights to the database
$myDB= "MyDatabase"; //This would by the Database Name
$conn = new COM ("ADODB.Connection")
or die("Cannot start ADO");
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr);
Now you have opened the connection. Lets say you want to select something, then you could do something like this
$select = "SELECT * FROM MyDatabaseTable";
$rs = $conn->execute($select);
if(!$rs->EOF){
while(!$rs->EOF){
echo $rs->Fields['MyField']->value."<br>";
$rs->MoveNext();
}
}
$rs->Close();
$rs = null;
Now when you have selected from the database you want to close the connection to the server to free up resources
$conn->Close();
$conn = null;
Remember allways to do a $rs->Close when you have selected something. This closes the recordset and then the $rs = null clears the recordset. This is important because you want to save resources. Also remember to do the $conn->Close() and $conn = null when you are done on a page, if you dont do that you will keep opening a new connection to the database which will cause it to lock you out eventually. You only have to $rs->Close a SELECT because a SELECT returns a recordset, you dont have to do it when you UPDATE, INSERT or anything else that doesnt return a recordset.
Like
Dislike
Keywords for this article:
ADODB || SQLOLEDB || CONNECTION || RECORDSET
Advertisement by Google
Comment:
Code Language:
Code:
Here you can paste a code example. It will then be processed by SyntaxHighlighter and formatted for easier readability.
Please remember to select the correct Code Language in the select above so the SyntaxHighlighter can highlight the code properly.
Code:
Please enter the code you see above
What is 7 + 7 =