My Version of a DataBase Query Class
Posted: Wed Sep 03, 2003 1:57 pm
This example was coded to be used with IIS/MSSQL
However, it has been used with mySQL as well.
note: to use it with mysql the function refreneces will neeed to be changed to be in line wioth mysql not mssql.
Sorry, no abstraction layer yet
The Idea I had was to create a reusable array of information that could be used on my entire page, as well the class would be able to updates, inserts, queries, and transactions .. Transactions however would need to be sent as a tsql exec() function.
Error returns for transactions would have to be used as a
@@error <> 0
SET @TransError = 1
Then have a check at the bottom of your transaction to check for;
if @TransError <> 0
begin
commit trans
select 1 as Error
end
else
begin
rollback trans
select 0 as Error
end
So using the above query code if there was an error you would check for.
The only problem here is the inablity to store multiple error strings/codes and have them returned.
However, it has been used with mySQL as well.
note: to use it with mysql the function refreneces will neeed to be changed to be in line wioth mysql not mssql.
Sorry, no abstraction layer yet
Code: Select all
<?php
/*-----------------------------------------------------------------------------
Author: Jesse Harnett
Date: Dec:09/2002
Revision: v1.1a
Description: SQL class objects.
Notes: The return data type will always be a multi-dimensional assoc array.
------------------------------------------------------------------------------*/
//SQL Class Parent Object
class SQLParent
{
var $ConInfo;
var $QueryResult;
var $LinkID;
var $TempArray;
var $DataArray;
var $key;
//Writes errors to a log file.
function &ErrorWriteOut($QueryString)
{
$fh = fopen('c:\temp\SqlErrorLog.log',"a");
fwrite($fh,"QueryError!: $QueryString "."\n");
fclose($fh);
}
//Closes the Current SQL connection which matches the Link Identifier
function &CloseSQLConnection($LinkID)
{
mssql_close($LinkID);
}
//Opens a new SQL connection, and holds the Link Identifier for the
//duration of the query.
function &OpenSQLConnection()
{
$this->ConInfo = Array();
$ConInfo = array( 'Hostname' => "****",
'Username' => "****",
'Password' => "****",
'Database' => "****"
);
$this->LinkID = mssql_connect($ConInfo["Hostname"],$ConInfo["Username"],$ConInfo["Password"]) or die($this->ErrorWriteOut("Could not connect to server"));
mssql_select_db($ConInfo["Database"]) or die($this->ErrorWriteOut("Could not connect to database"));
}
/*-----------------------------------------------------------------------------
Function: Main Query to array function
Created: Dec:9/2002
Modified: Dec:11/2002, May.7.2003, Aug.15.2003
By: Jesse Harnett
Notes: Removed initial row check for array merge. Now does array merge
regardless, but still needed a check for count do to the data type
constraints for array_unique. (array not string).
Removed unique limitation on array, in order to return full information
from each row returned by query.
-----------------------------------------------------------------------------*/
function &QueryDatabase($QueryString)
{
if( isset($QueryString) )
{
$QueryResult = mssql_query($QueryString,$this->LinkID) or die($this->ErrorWriteOut($QueryString));
$NumRows = mssql_num_rows($QueryResult);
while( $DataArray = mssql_fetch_assoc($QueryResult) )
{
while (list ($key, $val) = each ($DataArray))
{
$TempArray[$key][] = $val;
}
}
if( $NumRows > 0 )
$TempArray["NumRows"] = $NumRows;
else
$TempArray["NumRows"] = 0;
}
else
$TempArray["NumRows"] = 0;
return $TempArray;
}
}
?>Code: Select all
<?php
$querystring = "
exec('
Do your transction here.
')
";
?>@@error <> 0
SET @TransError = 1
Then have a check at the bottom of your transaction to check for;
if @TransError <> 0
begin
commit trans
select 1 as Error
end
else
begin
rollback trans
select 0 as Error
end
So using the above query code if there was an error you would check for.
Code: Select all
<?php
$SqlQuery = new SqlQuery;
$Results = $SqlQuery->ExecuteQuery($Query_String);
unset($SqlUpdate);
echo $Results['Error'][0]
?>