BOFH Hunter

Building off yesterday’s entry, today we’re going to add php into the mix. Mostly to add php, you’ll need to go through all of the previously mentioned steps, and a couple more for php. I’m going to assume that you have isql odbc queries working and that you’ve installed php-odbc. We’ll move on from there.

Since the Data Source Names, or DSN’s are user specific, and apache is a system account, we have to change a few things to make this work. You’ll need to create a system wide DSN, and to do this, we’re going to need to edit /etc/odbc.ini.

Open up /etc/odbc.ini as root, and add an entry similar to the data template we added yesterday. It should look like the one below.

[mymssql]
Driver = FreeTDS
Description = Sample Database
Trace = No
Server = my.mssql.server
Port = 1433

With this in place, you can go ahead and get your php ready. Sample code is below:


<?php
echo "How many users logged in last week";
//Connect to the database
$connect = odbc_connect("mymssql", "statuser", "statpass") or die("Could not connect to the database");
//Basic query, salt to taste
$query = "SELECT COUNT(user_id) from USERS";
// actually run the query
$result = odbc_exec($connect, $query);
//iterate through the results to test
while(odbc_fetch_row($result)) {
odbc_result_all($result);
};
odbc_close($connect);
?>

That’s it. That’s all it takes to make php work with Microsoft SQL Server via odbc. There are some issues that you may run into. For example, counting the results on certain versions of MSSQL will always return a value of -1, which is less than useful. You can either code around this yourself, or you can use adodb to communicate with your database and continue to simplify things.