...

<?php

// $Id: example.php,v 1.24 2006/05/31 14:38:06 lsmith Exp $
    //
    // MDB2 and MDB2_Schema example script.
    //

ini_set('include_path', '../..'.PATH_SEPARATOR.ini_get('include_path'));

// require the MDB2 code
    require_once 'MDB2.php';

// define and set a PEAR error handler
 // will be called whenever an unexpected PEAR_Error occurs
 function handle_pear_error ($error_obj)
 {
 print '<pre>PEAR-Error ';
 echo $error_obj->getMessage().': '.$error_obj->getUserinfo();
 print '</pre>';
 }
 PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error');

// just for kicks you can mess up this part to see some pear error handling
 $user = 'root';
 $pass = '';
 $host = 'localhost';
 $mdb2_name = 'metapear_test_db';
 $mdb2_type = !empty($_GET['db_type']) ? $_GET['db_type'] : 'mysql';
 echo($mdb2_type.' ');

// Data Source Name: This is the universal connection string
    $dsn['username'] = $user;
    $dsn['password'] = $pass;
    $dsn['hostspec'] = $host;
    $dsn['phptype'] = $mdb2_type;
    // MDB2::factory will return a PEAR::MDB2 instance on success
    // or a Pear MDB2 error object on error
    // You can alternatively build a dsn here
    // $dsn = "$mdb2_type://$user:$pass@$host/$mdb2_name";
    Var_Dump($dsn);
    $mdb2 =& MDB2::factory($dsn);
    // With PEAR::isError you can differentiate between an error or
    // a valid connection.
    if (PEAR::isError($mdb2)) {
        die (__LINE__.$mdb2->getMessage());
    }

// this loads the MDB2_Schema manager
 // this is a separate package you must install
 require_once 'MDB2/Schema.php';
 // you can either pass a dsn string, a dsn array or an exisiting mdb2 connection
 $schema =& MDB2_Schema::factory($mdb2);
 $input_file = 'metapear_test_db.schema';
 // lets create the database using 'metapear_test_db.schema'
 // if you have allready run this script you should have 'metapear_test_db.schema.before'
 // in that case MDB2 will just compare the two schemas and make any
 // necessary modifications to the existing database
 Var_Dump($schema->updateDatabase($input_file, $input_file.'.before'));
 echo('updating database from xml schema file ');

echo('switching to database: '.$mdb2_name.' ');
 $mdb2->setDatabase($mdb2_name);
 // happy query
 $query ='SELECT * FROM test';
 echo('query for the following examples:'.$query.' ');
 // run the query and get a result handler
 $result = $mdb2->query($query);
 // lets just get row:0 and free the result
 $array = $result->fetchRow();
 $result->free();
 echo(' row: ');
 echo(Var_Dump($array).' ');
 $result = $mdb2->query($query);
 // lets just get row:0 and free the result
 $array = $result->fetchRow(MDB2_FETCHMODE_OBJECT);
 $result->free();
 echo(' row (object: ');
 echo(Var_Dump($array).' ');
 // run the query and get a result handler
 $result = $mdb2->query($query);
 // lets just get row:0 and free the result
 $array = $result->fetchRow();
 $result->free();
 echo(' row from object: ');
 echo(Var_Dump($array).' ');
 // run the query and get a result handler
 $result = $mdb2->query($query);
 // lets just get column:0 and free the result
 $array = $result->fetchCol(2);
 $result->free();
 echo(' get column #2 (counting from 0): ');
 echo(Var_Dump($array).' ');
 // run the query and get a result handler
 $result = $mdb2->query($query);
 Var_Dump($mdb2->loadModule('Reverse', null, true));
 echo('tableInfo: ');
 echo(Var_Dump($mdb2->reverse->tableInfo($result)).' ');
 $types = array('integer', 'text', 'timestamp');
 $result->setResultTypes($types);
 $array = $result->fetchAll(MDB2_FETCHMODE_FLIPPED);
 $result->free();
 echo(' all with result set flipped: ');
 echo(Var_Dump($array).' ');
 // save some time with this function
 // lets just get all and free the result
 $array = $mdb2->queryAll($query);
 echo(' all with just one call: ');
 echo(Var_Dump($array).' ');
 // run the query with the offset 1 and count 1 and get a result handler
 Var_Dump($mdb2->loadModule('Extended', null, false));
 $result = $mdb2->extended->limitQuery($query, null, 1, 1);
 // lets just get everything but with an associative array and free the result
 $array = $result->fetchAll(MDB2_FETCHMODE_ASSOC);
 echo(' associative array with offset 1 and count 1: ');
 echo(Var_Dump($array).' ');
 // lets create a sequence
 echo(Var_Dump($mdb2->loadModule('Manager', null, true)));
 echo(' create a new seq with start 3 name real_funky_id ');
 $err = $mdb2->manager->createSequence('real_funky_id', 3);
 if (PEAR::isError($err)) {
 echo(' could not create sequence again ');
 }
 echo(' get the next id: ');
 $value = $mdb2->nextId('real_funky_id');
 echo($value.' ');
 // lets try an prepare execute combo
 $alldata = array(
 array(1, 'one', 'un'),
 array(2, 'two', 'deux'),
 array(3, 'three', 'trois'),
 array(4, 'four', 'quatre')
 );
 $stmt = $mdb2->prepare('INSERT INTO numbers VALUES(?,?,?)', array('integer', 'text', 'text'), MDB2_PREPARE_MANIP);
 foreach ($alldata as $row) {
 echo('running execute ');
 $stmt->bindValueArray($row);
 $stmt->execute();
 }
 $array = array(4);
 echo(' see getOne in action: ');
 echo(Var_Dump($mdb2->extended->getOne('SELECT trans_en FROM numbers WHERE number = ?',null,$array,array('integer'))).' ');
 $mdb2->setFetchmode(MDB2_FETCHMODE_ASSOC);
 echo(' default fetchmode ist now MDB2_FETCHMODE_ASSOC ');
 echo(' see getRow in action: ');
 echo(Var_Dump($mdb2->extended->getRow('SELECT * FROM numbers WHERE number = ?',array('integer','text','text'),$array, array('integer'))));
 echo('default fetchmode ist now MDB2_FETCHMODE_ORDERED ');
 $mdb2->setFetchmode(MDB2_FETCHMODE_ORDERED);
 echo(' see getCol in action: ');
 echo(Var_Dump($mdb2->extended->getCol('SELECT * FROM numbers WHERE number != ?',null,$array,array('integer'), 1)).' ');
 echo(' see getAll in action: ');
 echo(Var_Dump($mdb2->extended->getAll('SELECT * FROM test WHERE test_id != ?',array('integer','text','text'), $array, array('integer'))).' ');
 echo(' see getAssoc in action: ');
 echo(Var_Dump($mdb2->extended->getAssoc('SELECT * FROM test WHERE test_id != ?',array('integer','text','text'), $array, array('integer'), MDB2_FETCHMODE_ASSOC)).' ');
 echo('tableInfo on a string: ');
 echo(Var_Dump($mdb2->reverse->tableInfo('numbers')).' ');
 echo(' just a simple update query: ');
 echo(' affected rows: ');
 echo(Var_Dump($mdb2->exec('UPDATE numbers set trans_en ='.$mdb2->quote(0, 'integer'))).' ');
 // subselect test
 $sub_select = $mdb2->subSelect('SELECT test_name from test WHERE test_name = '.$mdb2->quote('gummihuhn', 'text'), 'text');
 echo(Var_Dump($sub_select).' ');
 $query_with_subselect = 'SELECT * FROM test WHERE test_name IN ('.$sub_select.')';
 // run the query and get a result handler
 echo($query_with_subselect.' ');
 $result = $mdb2->query($query_with_subselect);
 $array = $result->fetchAll();
 $result->free();
 echo(' all with subselect: ');
 echo(' drop index (will fail if the index was never created): ');
 echo(Var_Dump($mdb2->manager->dropIndex('test', 'test_id_index')).' ');
 $index_def = array(
 'fields' => array(
 'test_id' => array(
 'sorting' => 'ascending'
 )
 )
 );
 echo(' create index: ');
 echo(Var_Dump($mdb2->manager->createIndex('test', 'test_id_index', $index_def)).' ');

if ($mdb2_type == 'mysql') {
 $schema->db->setOption('debug', true);
 $schema->db->setOption('log_line_break', ' ');
 // ok now lets create a new xml schema file from the existing DB
 $database_definition = $schema->getDefinitionFromDatabase();
 // we will not use the 'metapear_test_db.schema' for this
 // this feature is especially interesting for people that have an existing Db and want to move to MDB2's xml schema management
 // you can also try MDB2_MANAGER_DUMP_ALL and MDB2_MANAGER_DUMP_CONTENT
 echo(Var_Dump($schema->dumpDatabase(
 $database_definition,
 array(
 'output_mode' => 'file',
 'output' => $mdb2_name.'2.schema'
 ),
 MDB2_SCHEMA_DUMP_STRUCTURE
 )).' ');
 if ($schema->db->getOption('debug') === true) {
 echo($schema->db->getDebugOutput().' ');
 }
 // this is the database definition as an array
 echo(Var_Dump($database_definition).' ');
 }

echo(' just a simple delete query: ');
 echo(Var_Dump($mdb2->exec('DELETE FROM numbers')).' ');
 // You can disconnect from the database with:
 $mdb2->disconnect()
?>

.	Edit
..	Edit
example.php	Edit
example_php5.php	Edit
metapear_test_db.schema	Edit