THE ANALYSIS AND SOLUTIONS COMPANY Home Blog Advantages Databases Websites Software Tutorials Writings Presentations Resume Contact Address Standardization Calendar Credit Card Validation Form Layout Log Monitoring Simple Session SQL ST Parser Manual Requirements Functions Change Log FAQ Download License

SQL Solution -- Manual

Table of Contents

• Description
• Construction
• Setup and Use
  • Basic Concepts
  • Security
  • User input can't be trusted!
  • Escaping HTML and the Safe Markup Language
    • Valid HTML Constructs
      • Paired Elements
      • Empty Elements
      • Anchor Elements (Links)
      • Character References
      • Character Entities
      • URI's
    • Turning On and Off the HTML Escaping and/or Safe Markup Language
    • Customizing the Safe Markup Language
  • Differences Between Database Engines
  • Reserved Words
• User Classes and Constructor Methods
• Functions
  • Arguments for Standard Functions
  • Arguments for Result Display and Form Generation Functions
  • Error Handling Functions
    • KillQuery()
  • Connection Functions
    • Connect()
    • PersistentConnect()
    • ObtainHandle()
    • Disconnect()
  • Query Functions
    • RunQuery()
    • RunQuery_RowsNeeded()
    • RunQuery_NoDuplicates()
    • ReleaseRecordSet()
  • Field Definition Functions
    • FieldName()
    • FieldType()
    • FieldLength()
    • FieldLengthEnumArray()
    • FieldLengthAssocArray()
  • Record Data Functions
    • RecordAsAssocArray()
    • RecordAsEnumArray()
    • RecordIntoThis()
    • InsertID()
    • GoToRecord()
  • Result Display Functions
    • GetRecordSetAsList()
    • RecordSetAsList()
    • GetRecordSetAsTable()
    • RecordSetAsTable()
    • GetRecordSetAsXML()
    • RecordSetAsXML()
    • GetRecordSetAsTransform()
    • RecordSetAsTransform()
    • RecordAsTable()
  • Form Generation Functions
    • GetOptionListGenerator()
    • OptionListGenerator()
    • GetInputListGenerator()
    • InputListGenerator()
    • RecordAsInput()
    • GetRecordSetAsInput()
    • RecordSetAsInput()
  • Utility Functions
    • Escape()
    • TimestampToUnix()
    • DatetimeToUnix()
    • OverflowProtectionInSQL()
    • CopyObjectContentsIntoSQL()
• Credits

Description

SQL Solution uses PHP classes to simplify getting data into and out of MySQL (via PHP's mysql or mysqli extensions), PostgreSQL, SQLite (via PHP's sqlite or sqlite3 extensions), and ODBC databases. The program is a powerful, user friendly, platform independent API (Application Programming Interface). Creating hypertext interfaces to your databases and interactive websites is now a snap!

Output from the SQL Solution is XML compliant. XML is the emerging language for cross-platform content. The XML standard adhered to is the W3C's "Strict" Extensible HyperText Markup Language (XHTML) 1.0 DTD. In addition, there's a function which writes results out as regular XML.

When HTML tables are used to display results, they are constructed for ease of use by disabled persons via the W3C's HTML Techniques for Web Content Accessibility Guidelines 1.0.

We started writing the classes when PHP 3 was out. Changes have been made over the years. The system now requires the use of PHP 5. While the package has PHPUnit tests, if a bug has slipped through, please let us know via the contact link, above.

Construction

The SQL Solution offers five database drivers: MySQL, PostgreSQL, SQLite, SQLite3 and ODBC. Each Edition is comprised of five interdependent classes in four files. In addition, there's a third file containing items shared by all the versions. Here's a list in hierarchical order:

General.php

SQLSolution_General

General, platform independent, functions.

SQLSolution_ErrorHandler

Displays error messages and halts execution.

Customizations.php

SQLSolution_Customizations

Place customized functions you create in this class.

<DBMS>Specifics.php

SQLSolution_<DBMS>Specifics

Functions specific to a particular database type.

<DBMS>User.php

SQLSolution_MySQLUser, SQLSolution_MySQLiUser, SQLSolution_PostgreSQLUser, SQLSolution_SQLiteUser, SQLSolution_SQLite3User or SQLSolution_ODBCUser

• Contains the host name, database name, user name and password needed to actually use a particular database.
• Houses the "constructor" which gets run automatically when the class is created.

Breaking things up in this manner simplifies updates and porting to other database flavors.

 

---

Setup and Use

Basic Concepts

For those unfamiliar, or vaguely familiar, with PHP and/or classes, here's a quick set of instructions to get you started. Please note, before doing any of this, you'll need PHP installed and have access to a database either on your machine or some other server.

1. This package can downloaded either via our website or Git.

   Download and untar the file per the instructions on the download page.

   Git is nice because it permits easy integration of any enhancements we make with your settings. The current stable branch is 8. Development happens in master.

     git clone git://github.com/convissor/sql_solution.git
     cd sql_solution
     git checkout --track -b 8 origin/8
     

2. Copy the taasc_autoload.php file and the SQLSolution directory in your include directory.
3. The SQL Solution requires the use of an autoload function. The SQL Solution's files follow the PEAR naming convention, where the "_" in class names become "/" in the file paths. If you are already using an autoloader that follows this convention, great. If not adjust it as needed. See the example provided in `sql_solution/taasc_autoload.php`.
4. Use a text editor to open the <DBMS>User.php appropriate to the DBMS system you are using.
5. Set the properties (eg $sqlUserName, $sqlPassword, etc) to those which will get you into a database you've already created.
6. Save the changes and close the file.
7. You may need to change the permissions on all of the files to world readable via chmod 604 *.inc or Windows Explorer. In Windows Explorer on NT and 2K machines, you'd highlight the files in question, view Properties, Security Tab, Permissions button.
8. The autoload function needs to be available where the SQL Solution will be used. If the autoload function has not been declared yet, include it using something like this:

      require_once '../include/taasc_autoload.php';
      

9. Now, paste the following code in that file. This example uses MySQLi.

      <?php
      $sql = new SQLSolution_MySQLiUser;
      $sql->SQLQueryString = 'SHOW TABLES';
      $sql->RunQuery(__FILE__,__LINE__);
      $sql->RecordSetAsTable(__FILE__,__LINE__);
      ?>
     

   Those commands peform the following tasks:

   1. Creates an object named $sql using the class entitled SQLSolution_MySQLiUser.
   2. Sets the query string.
   3. Runs the query.
   4. Displays the query results as an HTML table.
10. Save the file on your web server.
11. Make sure the file is world readable via chmod or Windows Explorer.
12. Navigate your web browser over to the web page you just saved.
13. You should see a list of all tables in the database you specified in the SQLSolution_MySQLiUser class. If not, some potential problems include:
    1. A message saying something like "Forbidden. You don't have permission..." means you need to change the web page's permissions. On Unix type systems, use chmod. On Windows systems, use the Windows Explorer to alter the file's Properties.
    2. If you see the PHP code, the file didn't get parsed by the PHP program. Alter your web server's configuration files or use the appropriate AddType in your .htaccess files.
    3. If the browser shows "A Database Problem Occurred...", chances are there's a mistake in the variables you're using to get into the database. Check things like the host, database, DNS, user name, and password. If the problem persists, turn on the debug tool in the KillQuery() function to find out what's going on.

Security

It is advisable to place the SQL Solution's files, and all include files, in a secure location. The most secure location is off of the web server completely.

Another option is to place your web pages and scripts in separate directories at the same level in your web server's file system. Then, have your domain name, say www.example.org, point to the pages directory. For example, store pages in /htdocs/foo/pages and the include files in /htdocs/foo/includes.

See the PHP Manual for more information on system security.

User input can't be trusted!

All user input that reaches a query must be passed through our Escape() method.

Escaping HTML and the Safe Markup Language

Browsers and scripts can be tripped up by certain characters. Even worse, Java Scripts that do nasty things can be embedded into your HTML (CERT Advisory CA-2000-02). So, the SQL Solution's default setting converts all HTML special characters (< > & ") coming out of the database into their Character References (&lt; &gt; &amp; &quot;).

Using this important safety feature, unfortunately, eliminates the ability to store HTML content in your databases. To solve this problem, we've created a Safe Markup Language.

The basis of our Safe Markup Language is the "::" delimiter. Anything contained between two pairs of colons, say ::p:: for example, is evaluated as a potential HTML construct. If the construct is allowed, it's converted from Safe Markup Language into HTML.

In addition, the Safe Markup Language conversion process automatically turns all URI's into hyperlinks. So, http://www.analysisandsolutions.com/ would become http://www.analysisandsolutions.com/.

Valid HTML Constructs

• Paired Elements

  Paired Elements are those which utilize opening and closing tags. Only a few tags are permitted. Attributes can not be added to tags. The tags must be in lower case:

  p, ul, ol, li, dl, dt, dd, b, i, code, sup, pre, tt, em, blockquote

  Sample: ::p::Some text::/p:: becomes <p>Some text</p> in the HTML.

• Empty Elements

  Items that don't use closing tags are called Empty Elements. Again, only a few tags are tolerated, attributes can't be added, and they must be lower case:

  br, hr

  Sample: ::hr::Some text becomes <hr />Some text in the HTML.

• Anchor Elements (Links)

  Links use Anchor Elements. Here is how to compose them in Safe Markup: ::a::http://example.org/::a::Anchor Text::/a:: becomes <a href="http://example.org/">Anchor Text</a> in your HTML and then rendered as Anchor Text in browsers.

• Character References

  References utilize recognizable alphanumeric codes to represent special characters. The Safe Markup Language will convert 2-6 character strings between the "::" delimiters that haven't already been converted into an Element. So, you can use any Character Reference. Some references you may find helpful are:

  lt, gt, quot, amp, nbsp, copy, reg

  Sample: 7::lt::8 becomes 7&lt;8 in your HTML and then rendered as 7<8 in browsers.

• Character Entities

  Entities use ISO numeric codes to symbolize characters and icons. Any 2 to 4 digit number between the "::" delimiters will be turned into an Entity. Thus, any entity can be used. Some handy entities include:

  33=!, 35=#, 36=$, 37=%, 39=', 42=*, 44=,, 63=?, 8212=—, 8226=•, 8482=

  Sample: ::8212:: places &#8212; into your HTML, and shows up as — in the browser.

• URI's

  URI's starting with a known transport scheme are automatically converted to hyperlinks:

  http://, https://, ftp://, gopher://, news:, mailto:

  Note, the conversion is done automatically, so you do not need to place ::'s before and after text to be hyperlinked. This process properly recognizes URI's which use characters authorized by RFC 2396:

  A-Z, a-z, 0-9, !#$%&'()*+,-./:;=?@_~

  If illegal characters are found, the hyperlink stops at the preceding character. Also, while "." and "," are valid characters, when they appear at the end of a URI, we consider them punctuation rather than part of the URI. If you have a URI ending with such, escape them using "%46" or "%44".

Turning On and Off the HTML Escaping and/or Safe Markup Language

The default settings have HTML Escaping turned on and the Safe Markup Language turned off. These settings can be altered by you when creating the object and/or anywhere in your scripts after the SQL Solution object is created. Here are some examples

Changing settings upon object creation:

   # Turn HTML Escaping off and Safe Markup on.
   $sql = new SQLSolution_MySQLUser('N', 'Y');

   $sql->SQLQueryString = 'SELECT * FROM Bar';
   $sql->RunQuery(__FILE__,__LINE__);
   $sql->RecordSetAsTable(__FILE__,__LINE__);

Changing settings in the middle of your code:

   # Defaults are used (Escape on, Safe off).
   $sql = new SQLSolution_MySQLUser;

   $sql->SQLQueryString = 'SELECT * FROM Foo';
   $sql->RunQuery(__FILE__,__LINE__);
   $sql->RecordSetAsTable(__FILE__,__LINE__);

   $sql->SQLEscapeHTML = 'N';  # HTML Escaping doesn't happen.
   $sql->SQLSafeMarkup = 'Y';  # Safe Markup conversion happens.

   $sql->SQLQueryString = 'SELECT * FROM Bar';
   $sql->RunQuery(__FILE__,__LINE__);
   $sql->RecordSetAsTable(__FILE__,__LINE__);

Customizing the Safe Markup Language

The conversions are performed in the SQLSolution_General::ParseSafeMarkup() method. The behavior can be changed by creating a ParseSafeMarkup() method in the SQLSolution_Customizations class.

Differences Between Database Engines

Query String formatting requirements vary between database engines. So, changing between different Editions of the SQL Solution may necessitate rewriting the queries in your applications. Such behavior has to do with the database engine itself, not the SQL Solution code.

The $sql->SQLRecordSetFieldCount and $sql->SQLRecordSetRowCount variables act differently under different database types. See the Query Functions section for more information.

 

---

User Classes and Constructor Methods

This class contains the variables needed to connect to your databases. It's the one you should call from your applications. You'll find this class stored in the <DBMS>User.php files.

The __construct() method has two optional arguments, $Escape and $Safe. These arguments control the behavior of HTML Escaping and Safe Markup Language processes as info from a database is being prepared for display. For more information on how to use these arguments, see the Turning On and Off the HTML Escaping and/or Safe Markup Language section of the manual.

 

---

Functions

All functions start out with the following required arguments: $FileName and $FileLine. These arguments hold the name and location of the script which called the function. This makes debugging your code much easier. The best thing to do is call functions like this: $sql->SomeFunction(__FILE__,__LINE__);.

---

Arguments for Standard Functions

Some functions have additional arguments. When they do, this manual will describe such in an Arguments Table:

Arguments for SomeFunction()

Arguments	Type	Default	Description
Foo	string	required	Header above boingie tables.
Fluff	string		Description of fluffs.
Slough	int	1	Number of cells sloughed off by boingie tables.

Things to note:

• This doesn't apply to Result Display or Form Generation Functions. Please see their argument structure, below.
• Arguments are listed in the order they need to be used.
• Type is the data type expected.
• "required" in the Default column means the argument is required.
• Blank cells in the Default column means the default is an empty string. When an empty string appears, the argument will be ignored by the functions.

So, in this case, you'd call the function like this. If you wanted to set the Fluff and Slough argument manually:

   $sql->SomeFunction(__FILE__,__LINE__, 'Sediment', 'Cats', 33);

If need to set Slough but you don't want a Fluff to appear in the resulting display, do this:

   $sql->SomeFunction(__FILE__,__LINE__, 'Sediment', '', 33);

Otherwise, you can make life easier by letting Fluff go unused and Slough be the default:

   $sql->SomeFunction(__FILE__,__LINE__, 'Sediment');

---

Arguments for Result Display and Form Generation Functions

Result Display Functions and Form Generation Functions have a different argument structure. While both types of procedures have the standard $FileName and $FileLine arguments, there are potentially two additional optional arguments. Each of these functions has an Options Argument. Then, in addition, some have a Columns Argument.

First, the Options Argument is an associative array. The manual will show an Options Table for each function, describing which options are available for that procedure.

Take note: all values passed into an Options Argument array (except those in the where and default keys) are passed through PHP's htmlspecialchars() function to ensure the resulting HTML is clean.

Second, the Columns Argument operates exactly the same in every function that uses them. So, we'll describe them once, here.

Columns Argument

The Columns Argument is a multidimensional associative array. The first index is the name of the field you want to apply an attribute to. The second index is the name of the attribute you want to set. This table lists the names of the second index and what attributes they are responsible for:

Columns Argument

Attribute Name	Type	Default	Description
hide			Fields which should not be displayed.
keyfield	string		Name of fields containing the data to be placed at the end of linkurl's.
linkurl	string		Base URI the link should go to. Include the beginning of the URI query string.

   $sql->SQLQueryString = 'SELECT ID, State, Governor FROM States';
   $sql->RunQuery(__FILE__,__LINE__);

   $Col = array();
   $Col['State']['keyfield'] = 'ID';
   $Col['State']['linkurl']  = 'state.htm?ID=';
   $Col['ID']['hide']        = '';

   $sql->RecordSetAsTable(__FILE__,__LINE__, '', $Col);

 

---

Error Handling Functions

KillQuery()

Gracefully handles errors. Called automatically when a problem arises.

Inside this function is a debugging tool.

   // debug tool -> //   echo "<p>File: ...

It displays the name and line of your script that caused the problem, plus the descriptive error message returned by the database. The debug tool is is turned off by default. To turn it on, move the echo statement down to the next line:

   // debug tool -> //
echo "<p>File: ...

It's advisable to keep the debugger off during normal operations, so, in the event your code causes an error, users can't readily learn ways to exploit your system.

Arguments for KillQuery()

Arguments	Type	Default	Description
Message	string	required	Error message to be shown in debug mode. Generally, the error text generated by the database server.

   $sql->KillQuery(__FILE__,__LINE__, 'Explain the error.');

 

---

Connection Functions

Connect()

Establishes a connection to the database server using the variables set in SQLSolution_(MySQL|PostgreSQL|SQLite|ODBC)User. Before doing so, turns the track_errors php.ini setting on.

This function is called automatically the first time your program executes a query. Under normal operation, you don't need to call it yourself. But, here are some situations where you may want to call connection functions manually.

There are several options for establishing different connections in the middle of your script. One way is to create two copies of the class using two different constructor classes.

   $sql1 = new SQLSolution_Main;
   $sql2 = new SQLSolution_Remote;

Or, you can use the same constructor class, but modify the variable(s) as necessary, then establish the connection.

   $sql1 = new SQLSolution_Main;
   $sql2 = new SQLSolution_Main;
   $sql2->SQLHost = 'mysql.remote.com';
   $sql2->Connect(__FILE__,__LINE__);

Another possibility is to use only one instance of the class, but alter the required variable(s) and establish a new connection.

   $sql = new SQLSolution_Main;

   ... perform various tasks, then change hosts ...

   $sql->SQLHost = 'mysql.otherhost.com';
   $sql->Connect(__FILE__,__LINE__);

PersistentConnect()

Establish a persistent connection to the database server.

Queries run through SQL Solution automatically establish regular connections. To establish persistent connections, call this function at the beginning of your script. All subsequent queries will automatically use the persistent connection.

Before doing so, turns the track_errors php.ini setting on.

   $sql->PersistentConnect(__FILE__,__LINE__);

ObtainHandle()

Link to a database.

This function is automatically called when a script runs its first query. You don't need to call it yourself.

If you need to switch databases in the middle of your programs, utilize the concepts demonstrated for the Connect() function, above.

   $sql->ObtainHandle(__FILE__,__LINE__);

Disconnect()

Unlink from the current database.

   $sql->Disconnect(__FILE__,__LINE__);

 

---

Query Functions

When calling any of these query functions, the variable SQLQueryString must be set before calling a query function.

Once a query is run, the $sql->SQLRecordSetFieldCount and $sql->SQLRecordSetRowCount variables can be used in your scripts as needed. These variables have funny characteristics in the MySQL Edition:

• The quantities shown in these variables are only meaningful after running SELECT queries.
• SHOW, DESCRIBE and EXPLAIN queries return values, but they only relate to the layout of the descriptions, not the underlying table or query data.
• All other queries return 0.
• RunQuery_NoDuplicates() returns 0 for both of these variables, regardless of which type of query was run.

 

RunQuery()

Passes Query String to the database server. If an error happens, a message is generated and the program ceases.

   $sql->SQLQueryString = 'SELECT * FROM Foo';
   $sql->RunQuery(__FILE__,__LINE__);

RunQuery_RowsNeeded()

Runs query and returns 1 if number of rows needed equals number of rows produced by the query, returns 0 if they are not equal. If a connection or query error arises, the program stops and an error message is printed on the screen.

Arguments for RunQuery_RowsNeeded()

Arguments	Type	Default	Description
RowsNeeded	int	1	How many records the result should have.

   $sql->SQLQueryString = 'SELECT * FROM Fluffy';
   if ( $sql->RunQuery_RowsNeeded(__FILE__,__LINE__, 3) ) {
      ... do some commands ...
   }

RunQuery_NoDuplicates()

Runs the query and returns 1 if record gets inserted without problems. If the record doesn't get inserted, but doesn't encounter an error, 0 is returned. If the connection or query generate an error, the error is trapped and the script is halted.

   $sql->SQLQueryString = "INSERT INTO Foo VALUES ($ID, $Quant)";
   while ( ! $sql->RunQuery_NoDuplicates(__FILE__,__LINE__) ) {
      ... do some commands ...
   }

For a more thorough example, see the OverflowProtectionInSQL() function, below.

 

ReleaseRecordSet()

Release the query results from memory.

   $sql->ReleaseRecordSet(__FILE__,__LINE__);

 

---

Field Definition Functions

One of the Query Functions must be run before any of the field definition functions.

 

FieldName()

Returns the name of the field at the specified column in the current record set.

Arguments for FieldName()

Arguments	Type	Default	Description
FieldNumber	int	required	Field you wish to examine. First column is 0.

   $sql->FieldName(__FILE__,__LINE__, $Counter);

FieldType()

Returns the data type of the field at the specified column in the current record set.

Arguments for FieldType()

Arguments	Type	Default	Description
FieldNumber	int	required	Field you wish to examine. First column is 0.

   $sql->FieldType(__FILE__,__LINE__, $Counter);

FieldLength()

Returns the size of the field according to the table definition at the specified column in the current record set.

In the MySQL Edition, under cases where two columns are concatenated, the defined size of concatenated columns are added. If text is concatenated into the query column, the length of that string is added to the sum. It does not represent the size of the current record's data or the largest size of data returned by the query.

In the ODBC Edition, under cases where two columns are concatenated, the size reported may come back as 255, regardless of the field sizes.

Arguments for FieldLength()

Arguments	Type	Default	Description
FieldNumber	int	required	Field you wish to examine. First column is 0.

   $sql->FieldLength(__FILE__,__LINE__, $Counter);

FieldLengthEnumArray()

Returns an enumerated array containing the size of each column in the current record set.

Column index starts at 0.

   $sql->FieldLengthEnumArray(__FILE__,__LINE__);

FieldLengthAssocArray()

Returns an associative array containing the size of each column in the record set.

   $sql->FieldLengthAssocArray(__FILE__,__LINE__);

 

---

Record Data Functions

These functions place the contents of a record into an array or object. Before being placed there, the output gets passed through PHP's htmlspecialchars() function so the HTML doesn't get screwed up or call nasty scripts. Output can also be passed through the Safe Markup Language converter if you so desire.

The first time one of these functions is run, it gets the data from the first record. Each subsequent call obtains data from the next record.

One of the Query Functions must be run before any of the Record Data functions.

 

RecordAsAssocArray()

Places the next record's data into an associative array. Field names are the array's keys and the field values are the array's values.

Arguments for RecordAsAssocArray()

Arguments	Type	Default	Description
SkipSafeMarkup	array	array()	A list of the fields that should not be passed to ParseSafeMarkup(). Parsing Safe Markup is an expensive operation; skipping fields can significantly improve performance.

   $sql->SQLQueryString = 'SELECT First, Last, Phone FROM People';
   $sql->RunQuery(__FILE__,__LINE__);
   $Skip = array('First', 'Phone');
   while ( $Array = $sql->RecordAsAssocArray(__FILE__,__LINE__, $Skip) ) {
      echo '<br />' . $Array['First'] . '\'s number is ' . $Array['Phone'];
   }

RecordAsEnumArray()

Places the next record's data into an enumerated array. Field locations are the array's keys. Field values are the array's values. The location/key index starts at 0.

Arguments for RecordAsAssocEnum()

Arguments	Type	Default	Description
SkipSafeMarkup	array	array()	A list of the fields that should not be passed to ParseSafeMarkup(). Parsing Safe Markup is an expensive operation; skipping fields can significantly improve performance.

   $sql->SQLQueryString = 'SELECT First, Last, Phone FROM People';
   $sql->RunQuery(__FILE__,__LINE__);
   $Skip = array('First', 'Phone');
   while ( $Array = $sql->RecordAsEnumArray(__FILE__,__LINE__, $Skip) ) {
      echo "<br />$Array[0]'s number is $Array[2]";
   }

RecordIntoThis()

Places the next record's data into variables within the calling object. Field names become the variables' names and field values are the variables' values. Returns 1 on success. But, if the most recent query has no results or the internal row pointer moves beyond the last record, the variables are cleared and nothing is returned.

   $sql->SQLQueryString = 'SELECT First, Last, Phone FROM People';
   $sql->RunQuery(__FILE__,__LINE__);
   while ( $sql->RecordIntoThis(__FILE__,__LINE__) ) { 
      echo "<br />$sql->First's number is $sql->Phone";
   }

InsertID()

Returns the auto increment ID from the last record inserted. The operation of this function was changed in SQLSolution_EditionSpecifics Version 4.07. The function did nothing in the ODBC Edition prior to that point.

In the MySQL Edition, the arguments do nothing because MySQL's mysql_insert_id function, rather than a subquery, is used to obtain the record id. The arguments are optional in order to maintain compatibility with scripts users have already created. It is advisable to begin using these arguments to improve portability of your scripts.

Arguments for InsertID()

Arguments	Type	Default	Description
Table	string	required	Name of table the Insert ID is being sought from.
Field	string	required	Field containing the automatically incremented record id.
Where	string	required	A valid WHERE clause for an SQL statement which will uniquely identify the record you inserted. The actual word "WHERE" is prepended automatically, so you don't need to write it yourself.
Sequence	string	optional	The name of the sequence to be used. Required when dealing with PostgreSQL.

   # Example uses the following table structure:
   # TransID   Auto Increment
   # When      Date
   # Vol       Integer

   $sql->SQLQueryString = "INSERT INTO Foo VALUES (NULL, '$Date', $Quant)";
   $sql->RunQuery(__FILE__,__LINE__);
   $ID = $sql->InsertID(__FILE__,__LINE__, 'Foo', 'TransID',
         "When='$Date' AND Quant=$Vol");

GoToRecod()

Moves the internal pointer to the specified row in a result set. Accomplished via mysql_data_seek or odbc_fetch_row().

Arguments for GoToRecord()

Arguments	Type	Default	Description
Row	int	0	Row of the record set to go to. The first record is considered row 0. Default takes you to the beginning of the set.

   $sql->SQLQueryString = 'SELECT ID, State, Governor FROM States';
   $sql->RunQuery(__FILE__,__LINE__);
   $sql->RecordAsTable(__FILE__,__LINE__);
   $sql->GoToRecord(__FILE__,__LINE__);
   $sql->RecordIntoThis(__FILE__,__LINE__);

 

---

Result Display Functions

RecordAsTable()

Displays contents of the next record as an HTML table and returns 1. Calling the function again displays data from the next record. If the most recent query has no results, displays message saying no records were found and returns 0. If the result set's internal row pointer has moved beyond the last record, nothing is displayed and nothing is returned.

Options for RecordAsTable()

Option	Type	Default	Description
align	left | center | right		How the table should be aligned on the page.
border	int	1	Table border size. "0" = none.
caption	string		HTML Caption that can go above or below the table.
captionalign	top | bottom		Where the caption should go.
cellpadding	int		Amount of padding in cells.
cellspacing	int		Spacing between cells.
class	string		Class HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
id	string		ID HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
nohead			If this option is set, column headers will not be displayed.
summary	string		Description of the table that can get put into the <table> tag.
width	numeric string		Table width. Can be in pixels or percent (eg: "75" or "75%").
wrap	Y | N	Y	Should text in cells wrap?

   $sql->SQLQueryString = 'SELECT ID, State, Governor FROM States';
   $sql->RunQuery(__FILE__,__LINE__);
   $Opt = array(
       'align' => 'right'
   );
   $sql->RecordAsTable(__FILE__,__LINE__, $Opt);

GetRecordSetAsList()

Returns the output of RecordSetAsList().

RecordSetAsList()

Displays an entire Record Set as an unordered or ordered list. Fields of your choosing can be a hyperlinks. If the query produces no records, a messages says such and the program continues.

Options for RecordSetAsList()

Option	Type	Default	Description
class	string		Class HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
delimiter	string	", "	String that divides each field.
id	string		ID HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
list	ul | ol	ul	Kind of list.
start	int		For ordered lists. Which number/letter should be list start at.
type	string		Type of bullets/numbers that should be used (eg: "I", "a", "disc").

This function accepts a Columns Argument.

   $sql->SQLQueryString = 'SELECT ID, State, Governor FROM States';
   $sql->RunQuery(__FILE__,__LINE__);

   $Opt = array(
       'delimiter' => ': '
   );
   $Col = array();
   $Col['State']['keyfield'] = 'ID';
   $Col['State']['linkurl']  = 'state.htm?ID=';
   $Col['ID']['hide']        = '';

   $sql->RecordSetAsList(__FILE__,__LINE__, $Opt, $Col);

GetRecordSetAsTable()

Returns the output of RecordSetAsTable().

RecordSetAsTable()

Displays an entire Record Set as an HTML table. Field of your choosing can be a hyperlink. If the query produces no records, a messages says such and the program continues.

Options for RecordSetAsTable()

Option	Type	Default	Description
align	left | center | right		How the table should be aligned on the page.
border	int	1	Table border size. "0" = none.
caption	string		HTML Caption that can go above or below the table.
captionalign	top | bottom		Where the caption should go.
cellpadding	int		Amount of padding in cells.
cellspacing	int		Spacing between cells.
class	string		Class HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
id	string		ID HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
nohead			If this option is set, column headers will not be displayed.
summary	string		Description of the table that can get put into the <table> tag.
width	numeric string		Table width. Can be in pixels or percent (eg: "75" or "75%").
wrap	Y | N	Y	Should text in cells wrap?

This function accepts a Columns Argument.

You have the option of setting $sql->SQLCreditQueryString before calling this function. If you do, it will be set to blank after the table is printed. For more info on Credit Query Strings, see the RecordSetAsTransform() function.

   $sql->SQLQueryString = 'SELECT ID, State, Governor FROM States';
   $sql->RunQuery(__FILE__,__LINE__);

   $Opt = array(
       'border' => '2'
   );
   $Col = array();
   $Col['State']['keyfield'] = 'ID';
   $Col['State']['linkurl']  = 'state.htm?ID=';
   $Col['ID']['hide']        = '';

   $sql->RecordSetAsTable(__FILE__,__LINE__, $Opt, $Col);

GetRecordSetAsXML()

Returns the output of RecordSetAsXML().

RecordSetAsXML()

Turns your query results into XML output.

One small thing to look out for... If you have a field named "a" do not create hyperlinks. Confusion may arise between the field and the hyperlink because they're both called "a"

Options for RecordSetAsXML()

Option	Type	Default	Description
namespace	string		XML Namespace for the results.
prefix	string		XML Prefix appended to each tag.
recordtag	string	record	Tag name used to delimit each record.
settag	string	recordset	Tag name used to start and end the result set.

This function accepts a Columns Argument.

   $sql->SQLQueryString = 'SELECT ID, State, Governor FROM States';
   $sql->RunQuery(__FILE__,__LINE__);

   $Opt = array(
       'settag'    => 'StateList',
       'recordtag' => 'AState'
   );
   $Col = array();
   $Col['State']['keyfield'] = 'ID';
   $Col['State']['linkurl']  = 'state.htm?ID=';
   $Col['ID']['hide']        = '';

   $sql->RecordSetAsXML(__FILE__,__LINE__, $Opt, $Col);

GetRecordSetAsTransform()

Returns the output of RecordSetAsTransform().

RecordSetAsTransform()

Makes a standard normalized Record Set look like a spreadsheet in an HTML table. If the queries produce improper results, the program is halted.

Options for RecordSetAsTransform()

Option	Type	Default	Description
align	left | center | right		How the table should be aligned on the page.
background	string		Background to be stuck in blank cell in upper left corner of HTML table.
border	int	1	Table border size. "0" = none.
caption	string		HTML Caption that can go above or below the table.
captionalign	top | bottom		Where the caption should go.
cellpadding	int		Amount of padding in cells.
cellspacing	int		Spacing between cells.
class	string		Class HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
flip	Y | N	Y	Flip axes so larger dataset ends up as rows?
horizontallabel	string		Label defining default horizontal axis of HTML table. Input must be alpha-numeric.
id	string		ID HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
summary	string		Description of the table that can get put into the <table> tag.
title	string		Title to be displayed in top cell of HTML table and the table's title tag.
verticallabel	string		Label defining default vertical axis of HTML table. Input must be alpha-numeric.
width	numeric string		Table width. Can be in pixels or percent (eg: "75" or "75%").

The following variables must be defined before this function is called:

Variable Name	Description
SQLVerticalQueryString	Query string for row headings of HTML table.
SQLHorizontalQueryString	Query string for column headings of HTML table.
SQLQueryString	Query string for main data set.
SQLAlternateQueryString	Query string for main data set if the horizontal/vertical axes are transposed. If no alternate query string is composed, Flip will be set to No.
SQLCreditQueryString	Optional. Used to construct data placed in the bottom cell of the HTML table. The string will be set to blank once the table is generated.

NOTE! In order for this function to work correctly, the SQLQueryString and SQLAlternateQueryString must be constructed so it returns the same number of rows for each datapoint. For example, lets create a table containing population in the United States of America by state for the period of 1958 to 1960:
Hawaii became a state in 1959, so there is no population count for them in 1958. State Year Pop Maine 1958 486 Maine 1959 533 Maine 1960 552 Hawaii 1959 325 Hawaii 1960 356	So, you need to construct your queries or populate your tables so Hawaii's 1958 population is NULL or 0. State Year Pop Maine 1958 486 Maine 1959 533 Maine 1960 552 Hawaii 1958 NULL Hawaii 1959 325 Hawaii 1960 356

   $Opt = array(
       'title'           => 'Population by State and Year',
       'verticallabel'   => 'State',
       'horizontallabel' => 'Year',
       'background'      => 'someimage.gif'
   );

   $sql->SQLVerticalQueryString   = "SELECT State FROM Populations
                                     WHERE (Year BETWEEN '$Start' AND '$End')
                                     GROUP BY State ORDER BY State";
   $sql->SQLHorizontalQueryString = "SELECT Year FROM Populations
                                     WHERE (Year BETWEEN '$Start' AND '$End')
                                     GROUP BY Year ORDER BY Year";
   $sql->SQLQueryString           = "SELECT State, Population FROM Populations
                                     WHERE (Year BETWEEN '$Start' AND '$End')
                                     ORDER BY State, Year";
   $sql->SQLAlternateQueryString  = "SELECT Year, Population FROM Populations
                                     WHERE (Year BETWEEN '$Start' AND '$End')
                                     ORDER BY Year, State";
   $sql->SQLCreditQueryString     = "SELECT concat(min(Year), ' through ',
                                     max(Year), ': ', Publisher), DocName,
                                     DocDate  FROM Sources
                                     WHERE (Year BETWEEN '$Start' AND '$End')
                                     GROUP BY DocName, DocDate ORDER BY Year";
   $sql->RecordSetAsTransform(__FILE__,__LINE__, $Opt);

 

---

Form Generation Functions

You need to generate the opening and closing <form> tags yourself. You don't need to repeat the tags each time the function is used. Just start your form as normal, then call these form generation functions in the middle of your form as needed.

GetOptionListGenerator()

Returns the output of OptionListGenerator().

OptionListGenerator()

Creates list boxes for use in forms.

Options for OptionListGenerator()

Option	Type	Default	Description
add	array		Additional items to append to the beginning of the list. Array key is value of item. Array value is the visible element for the item.
class	string		Class HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
default	array | variable | string		Default value(s) of the list. If using an array for the Default: • Associative or enumerated keys can be used. • The items to be selected go in the array's value fields. • The array should be sorted in the same order as the record set. • If multiple is 'N', only the first array element will be used.
id	string		ID HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
keyfield	string	required	Name of field containing the data to be placed in the value attribute of each item on the list.
multiple	Y | N	N	Should multiple selections be permitted? If set to 'Y', browsers may display the whole list by default if no size is set.
name	string	required	Text put into the name attribute of the list.
orderby	string	required	ORDER BY clause used in the query to sort the list of records.
size	int		Number of rows visible at one time.
table	string	required	Name of database table containing the stuff to list.
visiblefield	string	required	Name of field containing the data which becomes the visible text describing the item.
where	string	required	WHERE clause used in the query which generates the records to list.

   $Opt = array(
       'table'        => 'States',
       'where'        => '1=1',
       'orderby'      => 'State',
       'keyfield'     => 'ID',
       'visiblefield' => 'State',
       'name'         => 'StateID',
       'add'          => array('0' => 'All States')
   );

   echo '<form method="post" action="foo.htm">';

   $sql->OptionListGenerator(__FILE__,__LINE__, $Opt);

   echo '</form>';

If you would like the Default to be set from the values of a MySQL SET type column, you can run a query on the record, use PHP's explode() command on the field's result before calling this function, then use the array returned as the input for the default option.

GetInputListGenerator()

Returns the output of InputListGenerator().

InputListGenerator()

Creates lists of check boxes and radio buttons for use in forms.

Options for InputListGenerator()

Option	Type	Default	Description
add	array		Additional items to append to the beginning of the list. Array key is value of item. Array value is the visible element for the item.
all	Y | N	N	Should all items be checked by default?
border	int	2	Border size for table. 0 = no border.
class	string		Class HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
columns	int	2	Number of columns which the list should displayed in.
default	array | variable | string | int		Default value(s) of the list. If using an array for the Default: • Associative or enumerated keys can be used. • The items to be checked go in the array's value fields. • The array should be sorted in the same order as the record set. • If type is "radio", only the first array element will be used.
id	string		ID HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
keyfield	string	required	Name of field containing the data to be placed in the value attribute of each item on the list.
name	string	required	Text put into the name attribute of items on the list.
orderby	string	required	ORDER BY clause used in the query to sort the list of records.
table	string	required	Name of database table containing the stuff to list.
type	checkbox | radio	checkbox	Type of list.
visiblefield	string	required	Name of field containing the data which becomes the visible text describing the item.
where	string	required	WHERE clause used in the query which generates the records to list.
width	int | int%	100%	Table width. Can be in pixles or percent (eg: "75" or "75%").

   $Opt = array(
       'table'        => 'States',
       'where'        => '1=1',
       'orderby'      => 'State',
       'keyfield'     => 'ID',
       'visiblefield' => 'State',
       'name'         => 'StateID',
       'add'          => array('0' => 'All States')
   );

   echo '<form method="post" action="foo.htm">';

   $sql->InputListGenerator(__FILE__,__LINE__, $Opt);

   echo '</form>';

RecordAsInput()

Displays contents of the next record as input elements for an HTML form and returns 1. Calling the function again displays data from the next record. If the most recent query has no results, displays message saying no records were found and returns 0. If the result set's internal row pointer has moved beyond the last record, nothing is displayed and nothing is returned.

Please note, this is very crude. Data displayed may not be in the format of the initial database. For instance, MySQL fields of the "set" data type will show up as text.

Options for RecordAsInput()

Option	Type	Default	Description
align	left | center | right		How the table should be aligned on the page.
border	int	1	Table border size. "0" = none.
caption	string		HTML Caption that can go above or below the table.
captionalign	top | bottom		Where the caption should go.
cellpadding	int		Amount of padding in cells.
cellspacing	int		Spacing between cells.
class	string		Class HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
id	string		ID HTML element attribute that can be placed in each tag of the output. Helpful with formatting via Cascading Style Sheets.
nohead			If this option is set, column headers will not be displayed.
summary	string		Description of the table that can get put into the <table> tag.
width	numeric string		Table width. Can be in pixels or percent (eg: "75" or "75%").

   echo '<form method="post" action="foo.htm">';

   $sql->SQLQueryString = 'SELECT * FROM Foo';
   $sql->RunQuery(__FILE__,__LINE__);

   $Opt = array(
       'border' => '9'
   );
   $sql->RecordAsInput(__FILE__,__LINE__, $Opt);

   echo '</form>';

GetRecordSetAsInput()

Returns the output of RecordSetAsInput().

RecordSetAsInput()

Displays an entire Record Set as an HTML table with input fields for use on a form. The one field is available for input. That field, or another, can also be used as a hyperlink.

If the query returns no records, a message tells the viewer and resumes normal operation.

Options for RecordSetAsInput()

Option	Type	Default	Description
all	Y | N	N	Should all items be checked by default?
border	int	1	Border size for table. 0 = no border.
cellpadding	int		Amount of padding in cells.
cellspacing	int		Spacing between cells.
default	array | variable | string | int		Default value(s) of the list. If the type option is set to "text", you must use an associative array. String and variable defaults will have no impact. If using an array for the default: • It must be sorted in the same order as the record set. • The default values go in the array's value fields. • The kind of array that needs to be supplied depends upon which type of input list is being generated: Type Note text Array must use an associative index. The index is compared against the keyfield, when matches exist, the array element's value is placed in that item's input box. Enumerated arrays can cause nothing to be entered or, if your keyfield is numeric, can assign values to the wrong lines. checkbox Array can use an enumerated or associative index. radio Only the first array element will be used as the default. Array can use an enumerated or associative index.
inputheader	string	Input	Header that will be displayed above the input column.
keyfield	string	required	The data in this field becomes the value attribute in checkbox and radio inputs while it becomes the name attribute for text inputs. If this is empty, an error message is displayed and the program halts. Set it to the name of a field in your query results. The value set in the keyfield Option has no relation to the value of the keyfield Columns Argument.
maxlength	int	3	Maximum string length from text boxes.
name	string	Input	Text put into the name attribute of the input element for checkbox and radio inputs.
nohead			If this option is set, column headers will not be displayed.
size	int	3	Width of text input boxes.
type	text | checkbox | radio	checkbox	Type of input box that will be displayed.
width	int | int%		Table width. Can be in pixels or percent (eg: "75" or "75%").
wrap	Y | N	N	Should text in cells wrap?

This function accepts a Columns Argument.

We'll use two examples to demonstrate how this function works. Both examples will be based on the same starting point. It assumes we start with a table named "Cats":

CatID	CatName	Dept
02	Software	Computers
04	Hardware	Computers
26	Fax Machine	Office

First, print the opening form tag, make the query string and then run the query:

   echo '<form method="post" action="foo.htm">';

   $sql->SQLQueryString = 'SELECT CatID, CatName, Dept FROM Cats';
   $sql->RunQuery(__FILE__,__LINE__);

To get a checkbox list, this would be used:

   $Opt = array(
       'default'  => array('04'),
       'type'     => 'checkbox',
       'keyfield' => 'CatID',
       'name'     => 'CID'
   );
   $Col = array();
   $Col['CatName']['keyfield'] = 'CatID';
   $Col['CatName']['linkurl']  = 'category.htm?CatID=';
   $Col['CatID']['hide']       = '';

   $sql->RecordSetAsInput(__FILE__,__LINE__, $Opt, $Col);

which would produce, in part, the following...

   <input type="checkbox" name="CID[]" value="02" />
   <input type="checkbox" name="CID[]" value="04" checked />
   <input type="checkbox" name="CID[]" value="26" />
   keyfield shows up over here ---------------^^

But, to get a list of text input boxes, try this:

   $Opt = array(
       'default'  => array('04'),
       'type'     => 'text',
       'keyfield' => 'CatID',
       'name'     => 'CID'
   );
   $Col = array();
   $Col['CatName']['keyfield'] = 'CatID';
   $Col['CatName']['linkurl']  = 'category.htm?CatID=';
   $Col['CatID']['hide']       = '';

   $sql->RecordSetAsInput(__FILE__,__LINE__, $Opt, $Col);

to get something to look like this...

   <input type="text" name="CID[02]" value="" size="3" maxlength="3" />
   <input type="text" name="CID[04]" value="Salad" size="3" maxlength="3" />
   <input type="text" name="CID[26]" value="" size="3" maxlength="3" />
   keyfield shows up over here -^^

The last step is printing the closing form tag.

   echo '</form>';

 

---

Utility Functions

Escape()

Makes input safe for use as values in queries.

Arguments for Escape()

Arguments	Type	Default	Description
Value	mixed	required	the value to be escaped.

   $v = $sql->Escape(__FILE__,__LINE__, "somethin' to work on");

TimestampToUnix()

Inputting a database timestamp returns a Unix timestamp. Invalid input halts program and generates an error message.

Arguments for TimestampToUnix()

Arguments	Type	Default	Description
Time	yyyymmddhhmmss	required	Time to be converted.

   $t = $sql->TimestampToUnix(__FILE__,__LINE__, '20000509123015');

DatetimeToUnix()

Inputting a database datetime returns a Unix timestamp. Invalid input halts program and generates an error message.

Arguments for DatetimeToUnix()

Arguments	Type	Default	Description
Time	yyyy-mm-dd hh:mm:ss	required	Time to be converted.

   $t = $sql->DatetimeToUnix(__FILE__,__LINE__, '2000-05-09 12:30:15');

OverflowProtectionInSQL()

Keep track of a repeating processes. If it repeats more than the specified number of times, kill the script and generate an error message. Handy when trying to insert unique values using the RunQuery_NoDuplicates() function.

Arguments for OverflowProtectionInSQL()

Arguments	Type	Default	Description
Break	int	5	Number of attempts that should be made.

   $sql->SQLQueryString = "INSERT INTO Foo VALUES ($ID, $Quantity)";
   while ( ! $sql->RunQuery_NoDuplicates(__FILE__,__LINE__) ) {
      # Duplicate Key. Try again.

      # Make sure this doesn't go out of control.
      $sql->OverflowProtectionInSQL(__FILE__,__LINE__, 3);

      # Generate another Unique Number
      $ID = $ID * 2;

      # Construct a new query string to try.
      $sql->SQLQueryString = "INSERT INTO Foo VALUES ($ID, $Quantity)";
   }

CopyObjectContentsIntoSQL()

Copies the contents of another object into the present object.

Arguments for CopyObjectContentsIntoSQL()

Arguments	Type	Default	Description
From	string	required	Name of object you wish to copy from

To copy contents from the object named $Blah to the object named $sql, do this:

   $sql->CopyObjectContentsIntoSQL(__FILE__,__LINE__, 'Blah');

 

---

Credits

• Dieter Kneffel: Provided a good starting point for the regular expression that converts URI's into hyperlinks. http://marc.theaimsgroup.com/?l=php-general&m=94341182803814&w=2
• Ying Zhang: The idea of a Safe Markup Language. Though, my name for it and implementation are very different. http://www.phpbuilder.com/columns/ying20000718.php3
• Many thanks to everyone on the PHP Mailing list who've given all sorts of great general programming tips. http://marc.theaimsgroup.com/?l=php-general&w=2

---

The Analysis and Solutions Company.    More than just answers. Solutions. ^sm
https://www.analysisandsolutions.com/software/sql/sql-man.htm
© 2012.   Modified 2012-05-27 15:29.