Main
Download
Guide
FAQ
Contact
Other projects
- Chip

Wishlist/Bugs
Home

PHP Spawn MySQL/PostgreSQL Module

Or rather Spawn SQL

Despite the shortening of its name it is not a new database or variation of a query language. It is simply a module for PHP that allows for easy querying. Since it handles both PostgreSQL and MySQL, one does not have to worry about the differences in functions or slight variations in syntax. The module handles the difference, all you have to worry about is your own code. Thus, making this module portable from project to project.

Portability seems to be a plus these days. But before portability, I put neatness. Neater code is easier to read, and thus easier to program. For the sake of neat and portable code, we make modules like Spawn SQL.

Mike A. Leonetti

What it's designed to do

The Spawn SQL Module is designed to provide a set of functions that will work with both MySQL and PostgreSQL without having to deal with open connexions or any other specific functions. Since each database has different functions, porting code from one to the other would be rather a big pain. But by provided a common bond between the two of them, we can eliminate the confusion and extra work.

The Spawn SQL Module is also designed ease multiple database editing and to give table editing a new meaning.

Coding samples

Easy connecting and database selecting

As long as you know the username, password, and server (and in PostgreSQL the database name) you can connect with ease.
common.php
...

// Initialize the module
$sql = new Spawn( "mysql" );

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

...

or
common.php
...

// Initialize the module
$sql = new Spawn( "mysql", "username", "password", "database" );

$sql->connect();

...

Note: It is not recommended to set your username and password to "username" and "password" because those are just not very strong.

Database spawning: multiple database editing

Let us say we need to edit two databases. Our user is technically allowed to edit two databases, so why can't we edit them at the same time? Spawn SQL allows us to create a new class with the same information provided to it and allow for two databases to be open.
common.php
...

// Initialize the module
$db1 = new Spawn( "mysql" );

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

$db1->connect();

// Now create a new module with the first one
$db2 = $db1->get_database( "database2" );

...

Note: It is possible to connect to a database with a different use an password. Check the guide entry on get_database( ... ) for more information.

Table modifying functions

Well, of course without ways to get/add/modify table data there would be no meaning in writing such a module. However, Spawn SQL has a very simple way of modifying tables.
fetch.php
...

// Let's assume Spawn is already initialized

// Simple data retrieval
$data = $spawn->select( "test_table1" ); // Gets a single line from the front of the table
/*
Output:
Array
(
  [user_id] => 1
  [first_name] => Michael
  [last_name] => Leonetti
  [email] => infested@optonline.net
)
*/

...

Grabbing single lines is rather a boring thing, and might not be so widely used. Let's say we want to get more than one database entry and put it into an array. That's fine too. Just use mselect.
fetch_multiple.php
...

// Multiple line retrieval
$data = $spawn->mselect( "test_table1" ); // Gets the entire database

/*
Output:
Array
(
  [0] => Array
    (
    [user_id] => 1
    [first_name] => Michael
    [last_name] => Leonetti
    [email] => infested@optonline.net
  )

  [1] => Array
  (
    [user_id] => 2
    [first_name] => Dave
    [last_name] => Melnyk
    [email] => davey@daveland.com
  )
)
*/

...

Well, simple selecting is easy, but what if you wish to only get specific columns and only a certain line? Simple! With a little bit of array magic, we can make it all work.
fetch_me.php
...

// Get only my entry from the table
$data = $spawn->select( "test_table1", array("first_name","email"), array("user_id"=>1") );

/*
Output:
Array
(
  [first_name] => Michael
  [email] => infested@optonline.net
)
*/

...

Okay okay, selecting is good, but how do we get data into the database, update, and delete? We use the functions "insert", "update", and "delete". But with easier options.
insert.php
...

// Set up the data to insert
$insert_array = array(
    "first_name"=>"Paul",
    "last_time"=>"Jones",
    "email"=>"bigpaulj@pauljgoodies.net"
  );

$spawn->insert( "test_table1", $insert_array );

...

The rest of the functions are mostly the same.

Table spawning: launching table specific objects

With Spawn, when one is doing many operations on one table, one does not have to retype the name of the table many times. Spawn is built to "Spawn" little table classes that are made to use the same connexion resource but perform all operations on one table. Let's take the above select example.
table_spawn.php
...

// Spawn a table class
$table_spawn = $spawn->get_table( "test_table1" );
$data = $spawn->select(); // Gets a single line from "test_table1"

...