[TriLUG] LAmP (but with Microsoft SQL 2k) - suggestions?

[Bob Shepherd]

David,

Well, it turns out I don't have any HOWTO, but here's some help that should 
suffice:

1.  Installing FreeTDS & making it available to PHP:

http://www.peceny.de/misc/freetds.html

2.  PHP native extension for MSSQL:

http://www.phpbuilder.com/manual/ref.mssql.php

Some sample code:

## Include file with connection method

<?php
//conX-PHP.inc

$SQL_Error = '';

function db_connect() {

	$dbhost = "yourhost.yournet.net";
	$dbusername = "sa";
	$dbuserpassword = "";

	$cnxDB = mssql_connect($dbhost, $dbusername, $dbuserpassword);
	if(!$cnxDB) {
		$SQL_Error = "Connection failed to the host $dbhost.";
		return 0;
	}
	else {
	return $cnxDB;
	}
}

function sql_error() {
	return "db connection failed";
}

?>


## Read from db:

include "./conX-PHP.inc";

$cnxDB;	//Connection Object
$intQ;	//boolean for confirming query success
//our experience is without waiting for the intQ assignment, PHP/FreeTDS may 
silently continue without throwing an error
$ss = "";	 //TSQL String
$intQ = 0;
$intCt = 0; 	 //Query # records affected
$colResult = "";	 //Select Query Return (datareader)
$colRow = "";	//key-value pair array
$x = 0;

//Construct TSQL Querystring
$ss = "select  * from Products Order by ProductNo;";

//Connect to SQL Server
$cnxDB = db_connect(); //Connect to Sql Server
if(!$cnxDB) die (sql_error());

//Choose database 
$intQ = mssql_select_db("yourdb", $cnxDB);
if(!$intQ) die (sql_error());

//Send the query
$colResult = mssql_query($ss, $cnxDB);

//Get # of rows returned
$intCt = mssql_rows_affected($cnxDB);

for ($x = 0; ($x <= ($intCt - 1)); $x++) {
	//Fetch one row into a key-value array
	$colRow = mssql_fetch_array($colResult);
	$strProductNo[$x] = $colRow['ProductNo'];
	$strDescription1[$x] = $colRow['Description1'];
	//..etc.
        }
}
$intQ = mssql_close($cnxDB);

## Delete a record
	$ss = "Delete from test1 where text3 like 'BOB%'; ";
	$intQ = mssql_query($ss, $cnxDB);
	$intCt = mssql_rows_affected($cnxDB);

## Insert a record
	$ss = "insert into test1 (id, text1, text2, text3) values (";
	$ss .= "1, 'str1', 'str2', 'str3');";
	$intQ = mssql_query($ss, $cnxDB);

Hope this helps!

Bob Shepherd

On Wednesday 28 July 2004 1:19 pm, Turnpike Man wrote:
> In the past, I have managed with SuSE 7.1 to MSSQL 7.0 on PPC, but that was
> more than 2 years ago.  I keep seeing this unixODBC come up and now, so in
> a new environment in different job, I'm trying to do as the subject
> suggests. This is how I did it previously:
>
> http://www.turnpike420.net/linux2/Apache_PHP_FreeTDS_MSSQL7.txt
> Which I honestly can't stand this method and had to do it on SuSE at the
> time b/c standard installs were configured for it, and I don't think SuSE
> had a package thing like RPM, but if it did, doesn't matter b/c I didn't
> know.
>
> Anyway, I'm using FC1, I have php and php-odbc packages installed, unixODBC
> is installed, as well as Apache 2.x.  PHP works great with Apache right
> now, this is out of the box install, nothing config'd away from default.  I
> would like to think with this combination I can talk to the MSSQL 8.0
> (2000) database from a PHP web application.
>
> An alternate method I'm trying is using the FreeTDS tool that I used back
> in the first example.  FreeTDS now comes with 'tsql' tool which I can use
> and successfully connect to the MSSQL 2K db.  I don't know how to use tsql,
> so other than successful connect, I don't know how far I can go.  At least
> it proves I'm not firewalled off form the db.  Any tips or experience
> making this happen would be great.
>
> David M.
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail is new and improved - Check it out!
> http://promotions.yahoo.com/new_mail