Introduction

Zoop has it's own database abstraction layer, built upon the excellent pear::DB class. Zoop makes it easy to connect to multiple databases, of different types in the same application and is quite powerful. This is only meant to be an introduction into the database component of Zoop.

Configuration

Settings for the db component are defined in app_dir/config/db.php.
The file should look like:

define('db_RDBMS', 'mysql');
define('db_Username', '');
define('db_Password', '');
define('db_Server', 'localhost');
define('db_Port', '3306');
define('db_Database', '');

Using a Database in Hello World

Lets extend the hello World application to use a database. I am going to assume you have a working knowledge of database administration and php's integration with databases.

I am most familar with MySQL so I will use that for this tutorial, but there is no reason you need to.. The sql I use will be close enough so you can tweak it to your database if you are using something else. So if you aren't using mysql, this tutorial won't be copy paste, but will inform you just the same.

Create a database and make sure to config the application as shown above.

Lets setup a table within that database to house the users of our mock application.

I created a new zone to house this in, but you could do it in zoneDefault as well.

I made a new page function called pageInitDB and put the following into it.

function pageDBinit($inPath)
{
   sql_query("DROP TABLE IF EXISTS users");
   sql_query("CREATE TABLE users2 (
      	userid int(25) NOT NULL auto_increment,
	first_name varchar(25) NOT NULL default '',
	last_name varchar(25) NOT NULL default '',
	email_address varchar(55) NOT NULL default '',
	username varchar(25) NOT NULL default '',
	password varchar(255) NOT NULL default '',
	info text NOT NULL,
	last_login datetime NOT NULL default '0000-00-00 00:00:00',
	activated enum('0','1') NOT NULL default '0',
	PRIMARY KEY  (userid)
     ) TYPE=MyISAM COMMENT='Membership Information'
   ");
 
   $id = sql_insert("INSERT into users2 values ('',
			'Steve',
			'Tester',
			'test@testing.com',
			'Tester',
			'IloveTesting3',
			'I am L33t',
			'',
			'0')
		      ");
   $id2 = db_insert_array(
	array(
		'first_name' => "John", 
		"last_name" => "Tester", 
		'email_address' => "testingrocks@testing.com", 
		"username" => "test4me", 
		"password" => "TestingIsInMYBlood", 
		"activated" => "1"),
	"users2");
}

For the rest of this section we will show you different database functions in Zoop and how they work. We will continue to use this record set we setup here. This is not intended to be an all incompasing guide, but rather an introduction to the more commonly used functions.

The sql_ functions

In general there are two types of database functions in Zoop, sql_ and db_. sql_ functions are passed sql statements and do different things with the result of those statements. These functions are database independent, however the sql that they pass to the database is not, so your code won't necessarily be completly portable. Using our record set setup below, here are some of the more commonly used functions.

Basic sql_ functions:

sql_query($sql)

Takes a sql statement and passes it to the database.. Returns Boolean depending on success, nothing to see here.

sql_insert("sql_insert_statement")

Returns the primary_key id of the created record.

sql_check("select * from users where id = '1'")

Boolean return, if it finds anything, it will return true.

sql_fetch_ Functions:

For these I will show you the call and then the echo'ed result.

sql_fetch_one("select * from users limit 1")

Only expects one record to be found.

Array
(
    [userid] => 1
    [first_name] => Steve
    [last_name] => Tester
    [email_address] => test@testing.com
    [username] => Tester
    [password] => IloveTesting3
    [info] => I am L33t
    [last_login] => 0000-00-00 00:00:00
    [activated] => 0
)

sql_fetch_one_cell("select username from users where userid = '1'")

Expects only one cell to be returned.
Tester

sql_fetch_rows("select * from users")

Array
(
    [0] => Array
        (
            [userid] => 1
            [first_name] => Steve
            [last_name] => Tester
            [email_address] => test@testing.com
            [username] => Tester
            [password] => IloveTesting3
            [info] => I am L33t
            [last_login] => 0000-00-00 00:00:00
            [activated] => 0
        )
 
    [1] => Array
        (
            [userid] => 2
            [first_name] => John
            [last_name] => Tester
            [email_address] => testingrocks@testing.com
            [username] => test4me
            [password] => TestingIsInMYBlood
            [info] => 
            [last_login] => 0000-00-00 00:00:00
            [activated] => 1
        )
 
)

sql_fetch_column("select username from users")

Expects one field to be selected

Array
(
    [0] => Tester
    [1] => test4me
)

sql_fetch_assoc("select * from users")

Similar to fetch rows, except the key matches the primary key id

Array
(
    [1] => Array
        (
            [first_name] => Steve
            [last_name] => Tester
            [email_address] => test@testing.com
            [username] => Tester
            [password] => IloveTesting3
            [info] => I am L33t
            [last_login] => 0000-00-00 00:00:00
            [activated] => 0
        )
 
    [2] => Array
        (
            [first_name] => John
            [last_name] => Tester
            [email_address] => testingrocks@testing.com
            [username] => test4me
            [password] => TestingIsInMYBlood
            [info] => 
            [last_login] => 0000-00-00 00:00:00
            [activated] => 1
        )
 
)

sql_fetch_map("select * from users", "username")

makes the value of the field passed in the key of the array.

Array
(
    [Tester] => Array
        (
            [userid] => 1
            [first_name] => Steve
            [last_name] => Tester
            [email_address] => test@testing.com
            [username] => Tester
            [password] => IloveTesting3
            [info] => I am L33t
            [last_login] => 0000-00-00 00:00:00
            [activated] => 0
        )
 
    [test4me] => Array
        (
            [userid] => 2
            [first_name] => John
            [last_name] => Tester
            [email_address] => testingrocks@testing.com
            [username] => test4me
            [password] => TestingIsInMYBlood
            [info] => 
            [last_login] => 0000-00-00 00:00:00
            [activated] => 1
        )
 
)

The db_ functions

The db functions do not accept sql as parameters (in general). They are a newer addition to Zoop (starting with 1.2).

The following functions are expected to take their inputted values unescaped!! They are designed to interoperate with the sql_fetch_* functions. If you have are using them with post data and have magic_quotes_gpc on in your php.ini file please make sure to strip_slashes() before using these functions.

db_insert_array(array(), "table")

The Input array should look like the result of sql_fetch_one.

db_update_array(array(), "table", "primarykeyfield", "primarykeyvalue")

The Input array should look like the result of sql_fetch_one. Updates instead of inserting.

db_save_array(array(), "table", "primarykeyfield", "primarykeyvalue")

This one will update if the primary key is != "new", if it is new, it will insert a new record.