Main
Download
Guide
FAQ
Contact
Other projects
- Chip

Wishlist/Bugs
Home

Spawn Reference Guide

  1. Overview
    1. Installation and set-up
    2. Spawning databases
    3. Spawning tables
  2. Core functions
    1. connect - Open a connexion
    2. set_database - Set/change the database
    3. get_database - Spawn a new class for a separate database
    4. disconnect - Closes a database connexion
    5. get_table - Spawns a table class for use on one table
    6. query - Executes a query on the selected database
  3. Table functions
    1. select - Uses SELECT syntax
    2. mselect - Multiple select, returns a larger array
    3. lselect - Long select, allows for manual result fetching
    4. insert - Inserts data into a table
    5. update - Updates table data
    6. delete - Deletes table data
    7. clear - Deletes all table data
    8. get_insert_id - Gets the primary ID of the last inserted row (MySQL only)
    9. get_affected_rows - Gets how many rows were deleted or modified

Overview

Installation and set-up

 Installation of the module should be easy enough firstly, pick your version from the download page. Extract the code into a place where it can be easily included. The same directory where the code you wish to include it into works fine. Alternately you can place it into a modules directory and set the PHP include path to include that directory.

Now all we have to do is a little bit of set up. Create the module with either the correct properties or define the properties after module creation. When creating the module, you can either set it to use "mysql" or "pg" protocol. Once a class instance has been set to either "mysql" or "pg" it cannot be changed. A new class instance has to be created with alternate options.

common.php
...

require_once( "spawn_sql.php" );

$sql = new Spawn( "pg" );

$sql->server = "localhost";
$sql->username = "username";
$sql->password = "password";
$sql->database = "database";

...

Or if you wish to use the constructor functions to initialize the class:
common.php
...

$sql = new Spawn( "pg", "username", "password", "database" );

...

All that is left to do next is connect() and you are all set.

Note: When using PostgreSQL mode ("pg") a database must be defined before connecting. When in MySQL mode ("mysql") a database does not need to be defined but can be set later. However, if a database is defined before connecting the database is set automatically.

Spawning databases

 The feature of Spawn SQL that gives it the name, is being able to create multiple classes from the parent class. However, to spawn more than one class to edit more than one database, or have more than one class on one database the parent class has to be set up with the correct username, password, server, and database (mandatory with PostgreSQL). Spawn does not need to be connected when spawning, but the newly spawned class will connect automatically.

As mentioned above, when a new Spawn class has been spawned it carries the same username, password, and server information by default. The spawning function takes different options as paramters that can be ignored. Spawning a new class will force open a new connexion to a database.

For more information see get_database().

Spawning tables

 Spawning tables just means making a dedicated class that will automatically assume that all table modification functions you call you want performed on the specified table. It inherits the connexion resource from the calling class, and shares the same query count. Once a table class is spawned all table functions are the same minus the first parameter (which is the parameter that defines the table).

Example:
Instead of calling $spawn->insert( "this_table", $insert_data );
You could simply just call $table->inert( $insert_data );

This works the same with "select", "insert", "update", "mselect", "delete", and "clear".

Core functions

connect

 function connect()

Parameters None

Open a new connection with either MySQL or PostgreSQL. When in MySQL mode and the "database" class variable is set the database is automatically set when this function is called. When in PostgreSQL mode, the "database" MUST be set. Without that option PostgreSQL will most certainly fail in connecting.

Example

main.php
...

$sql = new Spawn( "mysql" );

$sql->server = "localhost";
$sql->username = "username";
$sql->password = "password";
$sql->database = "database";

//Finally, connect
$sql->connect();

...

set_database

 function set_database( $database=null )

Parameters
$database(Optional, default: value in the class variable "database")The name of the database to set.

As mentioned in the function above, this function does not need to be called if "connect()" is called after the class variable "database" has been set. However, if a need arises to change the database, this function can successfully do so.

Example

database_set.php
...

// An optional way of setting the database with MySQL
$sql = new Spawn( "mysql" );

$sql->server = "localhost";
$sql->username = "username";
$sql->password = "password";

$sql->connect();

// NOW set the database
$sql->set_database( "database" );

...

get_database

 function get_database( $database, $username=null, $password=null, $server=null )

Parameters
$databaseThe name of the database to create a new connexion to.
$username(Optional, default: parent class' value)Username to connect as.
$password(Optional, default: parent class' value)The password for that username.
$server(Optional, default: parent class' value)The server to connect to.

This function spawns a new class that forces a new connexion. Using this function, multiple database connexions can be open at once spannning server classes. Each new module has a different connexion resource, query count, query cache, etc. It basically saves you the trouble of setting up a new module from scratch.

Example

common.php
...

$db1 = new Spawn( "pg" );

$db1->server = "localhost";
$db1->username = "username";
$db1->password = "password";
$db1->database = "databasea";

$db2->database = $db1->get_database( "databaseb" );

// Now $db2 is ready to make function calls on "databaseb"

...

disconnect

 function disconnect()

Parameters None

Forces a disconnect. All of the spawned table classes will become useless because the parent connexion was closed. All spawned database classes will remain functional, however.

Note: As sort of a loose ends tie-up, PHP closes all database connexions at the end of script executions automatically making calling this funciton optional.

Example

useless.php
...

$sql = new Spawn( "mysql" );

$sql->server = "localhost";
$sql->username = "username";
$sql->password = "password";
$sql->database = "database";

$sql->connect();

// Get one thing from a table and then leave
$row = $sql->select( "customers", "*", array("customer_id"=>$customer_id) ); $sql->disconnect();

...

get_table

 function get_table( $table )

Parameters
$tableName of the table to edit.

Spawns a table class that will be used to edit only a specific table. For more information see: spawning tables.

Example

tables.php
...

$customers = $sql->get_table( "tbl_customers" );

// Now let's get all of the customers
$customer_list = $customers->mselect();

// $customer_list now contains an indexed array list of associative arrays

...

query

 function query( $query=null, $associative_return=false, $multiple_arrays=false )

Parameters
$query(Optional, default: value in class variable "query_cache") Query string to execute.
$associative_return(Optional, default: false) When true, the return value is in an associative array. Elsewise next_row() needs to be called to get returned table data.
$multiple_arrays(Optional, default: false) When true the associative array returned is an indexed array list of associative arrays, useful for multiple row selecting. When $associative_return is false this parameter is ignored.

Executes a literal query.

Example

query.php
...

// Identical to mselect(...)
$rows = $sql->query( "SELECT * FROM tbl_users", true, true );

...

Table functions

select

 function select( $table, $fields="*", $where=null, $extra=null, $associative_return=true, $multiple_arrays=false )

Parameters
$tableName of the table to perform the query on
$fields(Optional, default: "*") Fields to select. Can either be an array with each element being a field or a comma separated list of fields to be passed literally.
$where(Optional, default: NULL) The where parameter to narrow down the results. Can either be an associative array with keys that match up to values and/or literal expressions. In the case of an array the elements are logically ANDed. Elsewise this parameter can be a string to be placed literally in the query.
$extra(Optional, default: NULL) Literal string to place at the end of the query.
$associative_return(Optional, default: NULL) Functions much like the parameter from query.
$multiple_arrays(Optional, default: NULL) Functions much like the parameter from query.

The generic select query. When $associative_return is false, the return value must be fetched using next_row() and freed with free_result(). When it is true the the value will be returned in an array. However the results will only be limited to one row unless $multiple_arrays is set to true. Shortcuts for different functionality are lselect and mselect.

Example

fetch.php
...

$row = $sql->select( "db_sessions", "session_id,customer_id", array("browser"=>"firefox","last_action>{$five_minutes_ago}") );

...

mselect

 function mselect( $table, $fields="*", $where=null, $extra=null )

Parameters See select

This differs from select only in that the return result is always an array list of associative arrays of the SELECT query result. In effects, this calls select with $mutliple_arrays and $associative_return set to true.

Example

fetch.php
...

// Need to fetch all student's emails with the first name "Michael"
$students = $sql->mselect( "students", array("email"), array("name"=>"Michael") );

...

lselect

 function lselect( $table, $fields="*", $where=null, $extra=null )

Parameters See select

Calls "select" with the $associative_return set to false. Use next_row() to fetch the query results with this function.

Note: The query results are stored based on a stack system. That is, multiple query results can be opened at once. When a new one is opened, the old ones are pushed back. When one is closed they are brought forward again.

Example

dinner.php
...

// Let's get what foods we can make from the menu on Sunday
$sql->lselect( "food_list", array("course_name", "ingredients"), array("day"=>"sunday") );

// Now print the results
while( $row = $sql->next_row() )   print_r( $row );
// Make sure to free the query result to clean up properly
$sql->free_result();

...

menu.php
...

// Get main menu items
// array("parent"=>"NULL") becomes "parent IS NULL"
$sql->lselect( "menu_items", "*", array("parent"=>"NULL") );

while( $main_menu_item = $sql->next_row() )
{
  // Get sub-items
  $sql->lselect( "menu_items", "*", array("parent"=>$main_menu_items['menu_id']) );
  while( $sub_menu_items = $sql->next_row() )
  {
    // Display the item
  }
  $sql->free_result();
}

$sql->free_result();

...

insert

 function insert( $table, $insert_array )

Parameters
$tableName of the table to perform the query on.
$insert_arrayAssociative array of the values to insert. The keys are the columns and the values are the values.

Performs a standard INSERT query. In MySQL to get the primary ID of the last inserted row call get_insert_id().

Example

new_entry.php
...

$insert_data = array(
"name"=>$full_name,
"time_joined"=>time()
);

$sql->insert( "users", $insert_data );

...

update

 function update( $table, $update_array, $where=null, $extra=null )

Parameters
$tableName of the table to perform the query on.
$update_arrayAssociative array of the values to update. The keys are the columns and the values are the values.
$where(Optional, default: NULL) The where parameter to narrow down the results. Can either be an associative array with keys that match up to values and/or literal expressions. In the case of an array the elements are logically ANDed. Elsewise this parameter can be a string to be placed literally in the query.
$extra(Optional, default: NULL) Literal string to place at the end of the query.

Performs an UPDATE query and returns the amount of rows modified. Also see get_affected_rows().

Example

update_entries.php
...

// Change the last login time as the user logs on
$sql->update( "users", array("login_time"=>time()), array("user_id"=>$user_id) );

...

delete

 function delete( $table, $where=null, $extra=null )

Parameters
$tableName of the table to perform the query on.
$where(Optional, default: NULL) The where parameter to narrow down the results. Can either be an associative array with keys that match up to values and/or literal expressions. In the case of an array the elements are logically ANDed. Elsewise this parameter can be a string to be placed literally in the query.
$extra(Optional, default: NULL) Literal string to place at the end of the query.

Performs a DELETE query and returns the amount of rows deleted. Also see get_affected_rows().

Note: When deleting all entries in a table use clear().

Example

idle_users.php
...

// Delete users from the sessions table as they idle out
$time = time()-( 5*60 ); // Now minus five minutes
$sql->delete( "user_sessions", "last_action_time<{$time}" );

...

clear

 function clear( $table )

Parameters
$tableName of the table to perform the query on.

Performs a TRUNCATE query which deletes all entries from the table. This method is much faster and better than deleting everything with DELETE.

get_insert_id

 function get_insert_id()

Parameters None

Returns the last inserted entry's primary ID.

Warning: Only available in MySQL mode.

Example

user_add.php
...

$sql->insert( "users", array("username"=>$name, "email"=>$email") );
$user_id = $sql->get_insert_id();
// Log the user in

$sql->update( "sessions", array("user_id"=>$user_id), array("session_id"=>get_session_id()) );

// get_session_id() is a random custom function that returns the current user's session ID
// either from cookies, or some other method

...

get_affected_rows

 function get_affected_rows()

Parameters None

Valid when last query was a DELETE or an UPDATE. Returns the number of rows UPDATEd or DELETEd.

Note: Both delete and update call this function and return its result.