Function Reference

sql.php

AddRecord

Usage:

string AddRecord(array input, string table, string extra fields, string extra values)

Description:

AddRecord() writes a single record to a database.

When a user submits a form, the form data must first be turned into an associative array with AssignFormInputToArray(). AddRecord() then queries table for an array of fields. The function pieces together an Insert query using only those fields with submitted data. After the query is executed, the function returns the newly created index.

Assign AddRecord() to a string if you wish to capture the returned index. The function will still work with no string assignment.

Although extra fields and extra values allow writing to fields not specified in the form input, it's easier to keep these arguments unassigned and to append additional fields and values to the input array.

extra fields and extra values both require a leading comma before listing the appended strings, and both strings must list fields and values in parallel order.

The following syntax produces the same result.

$newID = AddRecord($form_input, $table_name, ", Field1, Field2", ", $Value1, $Value2";

$form_input["Field1"] = $Value1;
$form_input["Field2"] = $Value2;
$newID = AddRecord($form_input, $table_name, '', '');

BuildDeleteQuery

Usage:

string BuildDeleteQuery(string table, string condition)

Description:

BuildDeleteQuery() returns a deletion query strung together from the specified table and condition. This function is called from DeleteQuery(), which executes the actual deletion of a record.

BuildFormData

Usage:

array BuildFormData(string table, array input)

Description:

BuildFormData() maps an associative array of fields and values (input) to the fields of a database table (table). This function extracts values from input that are directly related to a database query, and it ignores any form field that does not exist in table.

This function is rarely used and may be deprecated the future.

BuildInsertData

Usage:

array BuildInsertData(array input, string table)

Description:

BuildInsertData() compares fields of a database table with input submitted through a form to determine which fields to fill when inserting a new record. The function then returns an associative array with fields containing new values.

The function queries the database for a single record, cycles through each field in that record, and matches it with a parallel cell in input.

BuildInsertQuery

Usage:

string BuildInsertQuery(string table, string fields, string values)

Description:

BuildInsertQuery() pieces together a query statement to insert a record into a database. fields and values are comma-delimited strings. Each value listed in values must be parallel to its corresponding field in fields.

This function is used in conjunction AddRecord() and is rarely used outside of it.

BuildUpdateData

Usage:

array BuildUpdateData(resource result, int row, array input)

Description:

BuildUpdateData() compares a record stored in database with input submitted through a form to determine which fields to update. The function then returns an associative array with fields containing new values.

The function cycles through each field in result, retrieves its parallel cell in input and compares the values with each other. If the new value is different from the current value, the field-value pair is saved to a new associative array. Empty strings are assigned null values, if the database field allows for it.

This function is used in conjunction with UpdateRecord() and is rarely used outside of it.

BuildUpdateQuery

Usage:

string BuildUpdateQuery(string table, array data, string condition)

Description:

BuildUpdateQuery() pieces together a query statement to update a single database record. The function cycles through each field in data and concatenates a string of assignments, placed after Set in the query statement. If set, condition is appended to the statement.

This function is used in conjunction with UpdateRecord() and is rarely used outside of it.

CatchMySQLError

Usage:

void CatchMySQLError()

Description:

CatchMySQLError() prints mysql_error() using DebugTrace() only when an error is available. This function is particularly useful when debugging errors.

CheckForTextField

Usage:

boolean CheckForTextField(string field type)

Description:

CheckForTextField() determines whether a string needs to be use MySQL escape quotes. PHP, for some reason, does not use the same terminology as MySQL when naming types of fields. String, blob, date and datetime types all require escaped quotes. Other fields types do not.

This function is used in conjunction with mysql_field_type(). When you pass the value from mysql_field_type() to CheckForTextField(), the function returns a boolean to indicate whether escape quotes are required.

CheckForTextField() is used in AddRecord() and UpdateRecord() and are rarely used outside of those functions.

DeleteRecord

Usage:

void DeleteRecord(string table, string condition)

Description:

DeleteRecord() deletes records from a database. Specify a condition to delete a specific record or set of records.

ExecuteQuery

Usage:

void ExecuteQuery(string query)

Description:

ExecuteQuery runs a database query. It serves as alias to mysql_query().

FetchRowObject

Usage:

object FetchRowObject(resource result)

Description:

FetchRowObject() fetches a row from a database query and returns the results as an object. It serves as an alias to mysql_fetch_object().

GetTableFieldNames

Usage:

array GetTableFieldNames(string table)

Description:

GetTableFieldNames queries the database table for a single record, then cycles through each field to create an associative array, assigning the field's type with the field's name serving as the array's key.

This function is used in conjunction with AddRecord() and is rarely used outside of it.

ProcessSQLLikeString

Usage:

string ProcessSQLLikeString(string data, string prefix, string suffix)

Description:

ProcessSQLLikeString() performs the same string handling functions as ProcessSQLString but concatenates prefix or suffix wildcards to Like clauses in queries.

ProcessSQLString

Usage:

string ProcessSQLString(string data)

Description:

ProcessSQLString() prepares strings for SQL queries by striping PHP slashes and escaping single quotes.

RunSelectQuery

Usage:

mixed RunSelectQuery(string query, string resource type)

Description:

RunSelectQuery() executes query and returns either a resource or an object, depending upon resource type, which accepts the following settings:

• "row" — Returns a resource.
• "rs" — Returns the database result with mysql_fetch_object().

This function is originally intended to run complex Select queries, but it may also run Update and Delete queries. You will still need to set resource type, even though it will be ignored for non-Select queries.

SelectRecord

Usage:

mixed SelectRecord(string query, string where clause, string resource type)

Description:

SelectRecord() executes a Select query on table with the clause where and returns a resource or object, depending upon resource type:

• "row" — Returns a resource.
• "rs" — Returns the database result with mysql_fetch_object().

CAUTION: SelectRecord() executes a query with an asterisk(*). This function does not support querying for specific fields. Use RunSelectQuery instead.

CAUTION: Omit the word "Where" in your clause. The function provides it already.

SelectAllRecords

Usage:

mixed SelectAllRecords(string table, string condition, string resource type)

Description:

SelectAllRecords() returns all records in table as either a resource or object, depending upon resource type:

• "row" — Returns a resource.
• "rs" — Returns the database result with mysql_fetch_object().

You may use condition to specify an Order clause.

SelectJoinedRecords

Usage:

mixed SelectJoinedRecords(string table, string dataset, string condition, string resource type)

Description:

SelectJoinedRecords() pieces together a Select query on table, retrieving dataset with the clause condition. The function returns a resource or an object, depending upon resource type:

• "row" — Returns a resource.
• "rs" — Returns the database result with mysql_fetch_object().

SQLConnect

Usage:

resource SQLConnect(string host, string user, string password, string databse)

Description:

SQLConnect() connects to the databse host with the login user and password and selects database.

UpdateRecord

Usage:

void UpdateRecord(resource query result, int result offset, array data, string table, string condition)

Description:

UpdateRecord() updates a single record in a databse.

When a user submits a form, the form data must first be turned into an associative array with AssignFormInputToArray(). UpdateRecord() then queries table for an existing record found with condition. The function compares the form data with the retrieved recordset and pieces together an Update query using only those fields with changed data.