Chick and Ruth's Delly Annapolis, Maryland Delicatessen

165 Main Street · Annapolis, MD 21401 · (410) 269-6737

Dignitaries

\n";*/ } public static function getInstance () { if (!self::$instance) { $class = __CLASS__; self::$instance = new $class; self::connect(); } return self::$instance; } private static function connect() { self::$link = mysql_connect(self::$dbHostAddress, self::$username, self::$password); if (!self::$link) { die ("Could not connect"); } $success = mysql_select_db(self::$database, self::$link); if (!$success) { die ("Could not select database"); } } /** * Close database connection * */ /*public function __destruct() { //needs a test here before the close mysql_close(self::$link); }*/ /** * Prevent cloning * */ public function __clone() { echo "Cloning is forbidden"; exit; } /** * Select a database to use * * @param string $database */ public function chooseDatabase($database) { $success = mysql_select_db($database,self::$link); if (!$success) { die ("Could not select database"); } } /** * return a 2d associative array with the db "table.field" name as the first element * * @param string $sql * @return array $arrData on success or -1 on failure */ public function returnArray2dAssocTF($sql) {//returnArray2dAssociative1stElementTableDotField2ndElementOrdinal would be a better name //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnArray2dAssocTF", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $intNumColumns = mysql_num_fields($result) - 1; //make zero base $intNumRecords = mysql_num_rows($result) - 1; //make zero base //Get the table-column names for ($i=0; $i <= $intNumColumns; $i++) { $arrTableFieldStructure[$i][0] = mysql_field_table($result, $i) . "." . mysql_field_name($result, $i); } if ($intNumRecords > -1) { //Put the data into the array //$arrData[0][0]; //Error preventor $intRecord = -1; //initialize row counter while (($row = mysql_fetch_array($result, MYSQL_NUM))) { $intRecord++; //increment the counter for ($intColumn = 0;$intColumn <= $intNumColumns;$intColumn++) { $arrData[$arrTableFieldStructure[$intColumn][0]][$intRecord] = $row[$intColumn]; } } return $arrData; } else { return -1; } } /** * Execute SQL with number of rows affected returned * * @param string $sql * @return integer indicating the number of records affected by the update */ public function executeSql($sql) {//executeSqlReturnRowsAffected would be a better name //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (!$result || mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("executeSql", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $intNumRowsAffected = mysql_affected_rows(self::$link); // Returns -1 if the query fails // Returns the number of records actually affected and not the total matched, i.e. if a // record matches the WHERE criteria but the updated fields have not chnaged then those // records are not counted in $intNumRowsAffected return $intNumRowsAffected; } /** * Execute SQL with id of last insert returned * * @param string $sql * @return integer indicating the id field of the newly inserted record */ public function executeSqlInsertID($sql) {//executeSqlReturnInsertID would be a better name // note: the id field in the database must be of type int, not bigint or any other type // also, this only works for simple insert queries, not other types //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (!$result || mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("executeSqlInsertID", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } //echo "sql: _".$sql."_
\n"; $newId = mysql_insert_id(self::$link); //echo "newId: _".$newId."_
\n"; //exit(); // returns false if no connection made to mysql, 0 if no auto-id was generated or the new auto-id on success return $newId; } /** * return a one based integer of how many records a sql query will return * * @param string $sqlTemp * @return integer $intNumRecords indicating the number of records returned by the query */ public function returnRecordCount($sql) { if ($this->debug == true) { echo $sql . "
\n"; } //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnRecordCount", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $intNumRecords = mysql_num_rows($result); // One based return $intNumRecords; } /** * this accepts a select query for two columns only and returns a 1 dimensional * array with the value of column one as the array key and the value of column two as the array value * An example of where this is useful would be a table called "settings" * id name value * 1 site_title Amazing Website * 2 contact_email amazing@website.com * * @param string $sql statement returning only two columns. Extra columns will be ignored. * @return array $arrData on success or -1 on failure. */ public function returnArray1dKeyValuePairs($sql) {//returnArray1stElementFieldAsKey2ndElementFieldAsValue would be a better name // accepts a sql statement that returns only two columns // column one is returned as the array key and column two as the array value // if more than two coulmns are returned everything but the first two are ignored if ($this->debug == true) { echo $sql . "
\n"; } //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnArray1dKeyValuePairs", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $intNumRecords = mysql_num_rows($result); if ($intNumRecords > 0) { //Put the data into the array $arrData = array(); //Error preventor while (($row = mysql_fetch_array($result, MYSQL_NUM))) { $arrData[$row[0]] = $row[1]; } return $arrData; } else { return -1; } } /** * return a 2d array with the fisrt dimension the record ordinal and 2nd * dimension an array with "table.field" equals value * * @param string $sql * @return array $arrData on success or -1 on failure */ public function returnArray2dAssocTFRecordFirst($sql) {//returnArray2dAssociative1stElementOrdinal2ndElementTableDotField would be a better name //echo "TFR1 ".microtime()."
\n"; //$this->logSql($sql); $result = mysql_query ($sql,self::$link); //these echos were to demonstrate a 3-9 second delay in this mysql call //when the same query is run directly on the server in phpMyAdmin it takes only .1 seconds //echo "TFR2 ".microtime()."
\n"; if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnArray2dAssocTFRecordFirst", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $intNumColumns = mysql_num_fields($result) - 1; //make zero base $intNumRecords = mysql_num_rows($result) - 1; //make zero base //Get the table-column names for ($i=0; $i <= $intNumColumns; $i++) { $arrTableFieldStructure[$i][0] = mysql_field_table($result, $i) . "." . mysql_field_name($result, $i); } if ($intNumRecords > -1) { //Put the data into the array $intRecord = -1; //initialize row counter while (($row = mysql_fetch_array($result, MYSQL_NUM))) { $intRecord++; //increment the counter for ($intColumn = 0;$intColumn <= $intNumColumns;$intColumn++) { $arrData[$intRecord][$arrTableFieldStructure[$intColumn][0]] = $row[$intColumn]; } } return $arrData; } else { return -1; } } /** * return a 1d array of records from a single column in the db * if more than one column is request all columns after the first are ignored * * @param string $sql * @return array $returnArray on success or null on failure */ public function return1dArray($sql/*, $columnName="id"*/) { ////$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("return1dArray", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $returnArray = array(); while (($recordArray = mysql_fetch_array($result, MYSQL_NUM))) { //var_dump($recordArray); //exit; $returnArray[] = $recordArray[0];//$recordArray[$columnName];//$recordArray['id'];// } $returnArrayCount = count($returnArray); if ($returnArrayCount > 0) { return $returnArray; } else { return null; } } /** * return the MySQL result resource from mysql_query * that can be passed to one of the mysql_fetch_* functions * * @param string $sql * @return array $arrData on success or -1 on failure */ public function returnMysqlResult($sql/*,$php_mysql_function_name = ""*/) { //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnMysqlResult", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } return $result; } /** * return an array of mysql_fetch_object objects each representing a record from the db * * @param string $sql * @return array $arrData on success or -1 on failure */ public function returnArrayOfMysqlRecordObjects($sql) { //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnArrayOfMysqlRecordObjects", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $arrRecordObjects = array(); while (($recordObject = mysql_fetch_object($result))) { //var_dump($recordObject); $arrRecordObjects[] = $recordObject; } $arrayCount = count($arrRecordObjects); if ($arrayCount > 0) { return $arrRecordObjects; } else { return null; } } /** * Return an array of mysql_fetch_object objects of type $className * each representing a record from the db * * @param string $sql * @param string $className * @return array $arrData on success or -1 on failure */ public function returnArrayOfSpecificClassObjects($sql, $className) { //$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnArrayOfSpecifcClassObjects", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $arrClassObjects = array(); while (($recordObject = mysql_fetch_object($result, $className))) { //var_dump($recordObject); $arrClassObjects[] = $recordObject; } $arrayCount = count($arrClassObjects); if ($arrayCount > 0) { return $arrClassObjects; } else { return null; } } /** * return an array of mysql_fetch_assoc arrays each representing a record from the db * * @param string $sql * @return array $arrData on success or -1 on failure */ public function returnArrayOfMysqlAssociativeArrays($sql) { ////$this->logSql($sql); $result = mysql_query ($sql,self::$link); if (mysql_errno(self::$link) != 0) { $this->outputErrorToScreen("returnArrayOfMysqlAssociativeArrays", $sql, mysql_errno(self::$link), mysql_error(self::$link)); } $returnArray = array(); while (($recordArray = mysql_fetch_assoc($result))) { //var_dump($recordObject); $returnArray[] = $recordArray; } $returnArrayCount = count($returnArray); if ($returnArrayCount > 0) { return $returnArray; } else { return null; } } /** * Updates a single field in a given table based on a single if type field / value * * @param string $tableName The database table to be updated. * @param string $idFieldName The field to be searched on. Only one record will be updated but this should be a unique field. * @param string $idFieldValue The value to be searched for in the id field. * @param string $updateFieldName The name of the field to be updated. * @param string $updateFieldValue The value to set the field being updated to. * @return integer $recordsAffected The number of records affected by the update query. */ public function updateSingleField($tableName, $idFieldName, $idFieldValue, $updateFieldName, $updateFieldValue) { $sql = " UPDATE `".$this->escapeStringForSql($tableName)."` SET `".$tableName."`.`".$updateFieldName."` = '".$this->escapeStringForSql($updateFieldValue)."' WHERE `".$tableName."`.`".$idFieldName."` = '".$this->escapeStringForSql($idFieldValue)."' LIMIT 1"; $recordsAffected = $this->executeSql($sql); return $recordsAffected; } /** * Accepts a SQL string (along with some $_SERVER vars) and logs it * * @param string $sql * @return nothing */ public function logSql($sqlToLog) { // TODO Logger Class not implemented //require_once 'class.Logger.php'; $logger = new Logger(); $logger->logSql($sqlToLog); } /** * Outputs an error to the screen / browser when MySql returns an error * */ private function outputErrorToScreen($functionName, $sql, $errorNumber, $errorMessage) { global $developmentMode; //require_once 'class.ExceptionHandler.php'; if ($developmentMode) { //throw new ExceptionHandler(); //* echo $functionName . "
\n"; echo "
\n"; echo $sql . "
\n"; echo "
\n
\n" . $errorNumber . ": " . $errorMessage . "
\n"; exit; //*/ } } /** * Escapes a value to be used in an SQL query. * Currently an abstraction for mysql_real_escape_string. * * @param string $input A string to escape for use in an sql query * @return string $output THe escaped string */ public function escapeStringForSql($input) { $output = mysql_real_escape_string($input); return $output; } }