|
iSQL --- Database Connection Object |
  
|
|
iSQL --- Database Connection Object |
|
$iSQL and $oSQL
The iSQL system is essentially a wrapper around the PHP Database system, hiding many of the complexities from you, creating a common set of objects/methods that work the same with any database, and assisting you in developing cleaner, easier to read and follow code. Use the high level iSQL methods whenever possible from within your application.
dsX will automatically create 2 iSQL objects for you at application start up. Although we cover how to connect to the database below, you'll never need to actually do this when writing dsX modules, plugins or extensions. These 2 objects are as follows:
$iSQL
This is the "main" database connection. In 99.9% of dsX installations, the end user has only one database to connect to. This global variable (object) represents the connection to that database. In cases where the end user has multiple database servers (ie: a master/slave setup or an olap/oltp setup), the $iSQL object represents the connection to the "master" database -- the one that can be written to (updates, deletes, inserts). In all cases, when programming using the dsX system, whenever you are writing queries or functions that UPDATE, DELETE, or INSERT data into the database, you must use the $iSQL object to do it. This allows the user to expand into a clustered (olap/oltp) environment at a later date, without you having to modify your code.
$oSQL
This is the "read-only" database connection object. In situations were the end users has clustered their database, and is using a master/slave (olap/oltp) type of setup, this represents the connection to the "Read Only" database. If your user does not have a master/slave setup, then $oSQL is simply a copy of the $iSQL master object, and may(must) still be used as designed. Whenever you're writing a function or a query that only SELECTS data from the database, use the $oSQL object to do it.
Note -- there are cases where you'll be writing a function that does some database manipulation (update/delete/insert) as well as some other plain-jane "select" statements. You may not reference both the $oSQL and $iSQL objects together in the same function -- its' got to be one or the other, or PHP will get confused on the connection IDs. In this case, use $iSQL, since it must be used for your database writes, and can also be used to do selects when needed.
iSQL - iWeb SQL Wrapper System
FUNCTIONS
new
Creates a new iSQL object, syntax shown above.
This will create a global, persistent database handle, and set
internal syntax flags for imported or derived classes to operate
under.
The following parameters are required when creating the object:
dbhost -> The hostname where the database server resides.
leave this blank if the database server is on the
same machine as the program.
dbname -> The name of the database
dbusername -> The username to use when logging into the database
dbpassword -> The users' password
Note that you do not need to specify what type of database you're
connecting to. dsX will detect that for you based on the admin
preferences and pre-load the correct driver for you. Each iSQL
driver contains the same methods for you to call, including "new"
which is the base connect method.
Sample:
$hostname = "localhost";
$dbname = "mydatabase";
$username = "myname";
$password = "somethingsecret";
$iSQL = new iSQL ( $hostname, $dbname, $username, $password );
run
A simple way to run a query, with logging, and error checking
The "run" method takes 2 paramaters. The first, is the actual SQL
statement to be run. The second, optional paramater, tells the run
method to override it's default error handling.
If you send a "1" as the second paramater, on error, the run method
will return control to you, with the error string available. If you
do not send the second paramater, the program will automatically
stop running and display the mySQL error to the browser.
if ( $iSQL->run("DELETE FROM some_table", 1) ) { echo "OK"; }
else { echo $iSQL->error; }
search
Performs a database search, returning the results to you in your choice of PHP variable types.
$iSQL->search( array("SQL"=>$SQL_Statement, "Return"=>"set") );
The arguments, sent in array format, that it can understand are:
1. SQL (required)
The SQL Statement that you wish to run
2. Start (optional)
The result number to start at, when sending a result set back
3. Limit (optional)
The max number of results to return
4. Return
The format to return results in. Valid values are:
"single"
Returns a single value (string) from a query where you're
only asking for one field, from one record.
"SELECT email FROM customers WHERE id = 15"
"array"
Returns a list of single depth values from a query where you
are asking for multiple records, but only a single field ...
"SELECT email FROM customers"
This would return an array like this:
array( "me@here.com", "you@there.com", "him@somewhere.com" )
"hash"
Returns data in a single record format (associative array).
Useful when you're pulling a single record from a table, ie:
"SELECT * FROM accounts WHERE id = 15"
This would give you data back like this:
array(
"name" => "Fred",
"email" => "fred@here.com"
"phone" => "555-1212"
)
"fullhash"
Returns a single associative array representing multiple
records from the database but in a single record-like format.
As an example, to get a simple keyed list of accounts and
their id's, run a query like this and ask for it in fullhash
format:
"SELECT id, full_name FROM accounts"
You'll get back data like this:
array (
"1" => "John Smith",
"2" => "Fred Jones",
"42" => "Mary Jane"
)
"set"
This is an array of hashes (a multi-dimensional associative
array). Use this when you are asking for many records, i.e.
"SELECT * FROM customers"
array(
array(
"name" => "Fred",
"email" => "fred@here.com"
"phone" => "555-1212"
),
array(
"name" => "John",
"email" => "john@there.com"
"phone" => "555-1111"
),
array(
"name" => "Mary",
"email" => "mary@overthere.com"
"phone" => "555-4444"
),
)
This help file was created with an unregistered evaluation copy of Help & Manual. © EC Software. All rights reserved. This message will not appear if you compile this help file with the registered version of Help & Manual.