Page 1 of 1

My Version of a DataBase Query Class

Posted: Wed Sep 03, 2003 1:57 pm
by NoReason
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 :)

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;
	}
}
?>
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.

Code: Select all

<?php
$querystring = "
exec('
Do your transction here.
')
";
?>
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.

Code: Select all

<?php
	 	$SqlQuery = new SqlQuery;
			$Results = $SqlQuery->ExecuteQuery($Query_String);
		unset($SqlUpdate);


echo $Results['Error'][0]
?>
The only problem here is the inablity to store multiple error strings/codes and have them returned.