An example of a basic ODBC (MSSQL Server/DSN-Less) query using PHP.
Example of Result Set Returning One Row
1 2 3 4 5 6 7 8 9 | $szDBConn="DRIVER={SQL Server};SERVER=SQLServerNameHere;DATABASE=DatabaseNameHere"; $szDBUsername="UsernameHere"; $szDBPswd="PasswordHere"; $szDBQuery="SELECT FooBar, Foo_Bar, Foo_ID FROM FoobarSubscribers WHERE FooID=777"; $rDBConnect = odbc_connect($szDBConn, $szDBUsername, $szDBPswd); $rDBRes = odbc_exec($rDBConnect, $szDBQuery); $szLastCheck = odbc_result($rDBRes, "LastCheck"); odbc_free_result($rDBRes); odbc_close($rDBConnect); |
Example of Result Set Returning Multiple Rows in an Object
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $szDBConn="DRIVER={SQL Server};SERVER=SQLServerNameHere;DATABASE=DatabaseNameHere"; $szDBUsername="UsernameHere"; $szDBPswd="PasswordHere"; $szDBQuery="SELECT FooBar, Foo_Bar, Foo_ID FROM FoobarSubscribers"; $rDBConnect = odbc_connect($szDBConn, $szDBUsername, $szDBPswd); $rDBRes = odbc_exec($rDBConnect, $szDBQuery); while($obRows = odbc_fetch_object($rDBRes)) { print $obRows->ColumnName1."<br />"; print $obRows->ColumnName2."<br />"; print $obRows->ColumnName3."<br />"; } odbc_free_result($rDBRes); odbc_close($rDBConnect); |
Example of Result Set Returning Multiple Rows in an Array
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $szDBConn="DRIVER={SQL Server};SERVER=SQLServerNameHere;DATABASE=DatabaseNameHere"; $szDBUsername="UsernameHere"; $szDBPswd="PasswordHere"; $szDBQuery="SELECT FooBar, Foo_Bar, Foo_ID FROM FoobarSubscribers"; $rDBConnect = odbc_connect($szDBConn, $szDBUsername, $szDBPswd); $rDBRes = odbc_exec($rDBConnect, $szDBQuery); while($saRows = odbc_fetch_array($rDBRes)) { print $saRows['ColumnName1']; print $saRows['ColumnName2']; print $saRows['ColumnName3']; } odbc_free_result($rDBRes); odbc_close($rDBConnect); |
Comments/description of Example
- Line #1
- Database connection string to define the driver, server host name and database name.
- The “server host name” is the server name or IP address of your database server. If your host has multiple instances the “host” value would be formatted like so “foo\bar”. If your using SQL Server Express the “host” name locally would be “.\SQLEXPRESS”.
- Line #2-#3
- Database login credentilas; username and password.
- Line #4
- SQL query.
- Line #5
- Connect to the database defined by Line #1, Line #2 and Line #3.
- Line #6
- Prepare and execute an SQL statement.
- Line #7
- Fetch results returned back from the SQL statement. I use a while loop to enumerate through each row of results returned. If you know only one row is going to be returned the while loop is not necessary.
- odbc_free_result()
- Close database connection.
- odbc_close()
- Close database connection.
This is awesome.
P.S with microsoft sql server, the server name is not MSSQLSERVER but localhost. when we use this odbc. I think this will save debugging time of a lot of developers.
Great post though