Retrieving records from MSSQL with PHP
This is the result of two solid days of work
<?php
$myServer = "WorkServer";
$myUser = "Administrator";
$myPass = "123456";
$myDB = "ImportantFinancialDatabase";
//est. a connection to the database
$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");
//create an instance of the ADO connection object
$connStr =
"PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
//define connection string, specify database driver
$conn->open($connStr); //Open the connection to the database
//1. Pull a single value
unset($single_value);
$rs = $conn->execute("SELECT ItemNo FROM Tbl_ItemMaster WHERE
ItemNo='100061'");
$single_value = $rs->Fields(0);
echo "Single Value: $single_value<br />";
//2. Pull multiple values
$rs = $conn->execute("SELECT ItemNo,Description FROM (SELECT *
FROM Tbl_ItemMaster WHERE (ItemType='MFG') OR (ItemType='PUR') OR
(ItemType='CAMO')) DERIVEDTBL WHERE (ComodityGroup<>'PCKG') AND
(ComodityGroup<>'PRNT') AND (ComodityGroup<>'SERV') AND
(ComodityGroup<>'METF');");
$num_rows = $rs->Fields->Count();
while (!$rs->EOF) //carry on looping through and writing while
there are records
{
$cm_itemno[] = trim($rs->Fields(0)->value);
$cm_desc[] = trim($rs->Fields(1)->value);
$rs->MoveNext(); //move on to the next record
} /* END looping through while there are records */
//print_r($cm_itemno);
//3. ATTEMPT to pull a value (value may not even exist)
//unset($single_value);
//$rs = $conn->execute("SELECT MatCst FROM Tbl_StndCostTable WHERE
ItemNo='900061'");
//$single_value = $rs->Fields(0);
//echo "Single Value: $single_value<br />";
/* Results in: Catchable fatal error: Object of class variant
could not be converted to string in C:\Documents and
Settings\shoppe\Desktop\retrieving records from MSSQL.php on line 35 */
/*
The above is not detectable by:
!== ''
is_null
isset
empty
etc. etc. etc.
*/
//4. How to pull non-existent values from a MSSQL database. The
query was successful but the value don't exist...
unset($single_value);
$rs = $conn->execute("SELECT MatCst FROM Tbl_StndCostTable WHERE
ItemNo = '900061';");
if ($rs->EOF or $rs->BOF) {
echo "Single Value: 0<br />";
} else {
$single_value = $rs->Fields(0);
echo "Single Value: $single_value<br />";
}
//close the connection and recordset objects freeing up resources
$rs->Close();
$conn->Close();
$rs = null;
$conn = null;
?>
