<?
/*
 * DBlite and Dynamic Queries
 *
 * DBlite comes with Dynamic Query interface. This is a powerful method for
 * constructing queries on the fly. First implementation of something representing
 * dynamic queries were found in AModules2 in early 2004, but idea was improved
 * and bundled inside DBlite since it's first versions.
 *
 * DSQL extention comes as a loadable class (dsql.php). That means - if you are
 * not using it, it won't be parsed.
 *
 * DSQL is very effective inside large libraries and framework, where you might
 * want to adjust queries. In other words - if your query generated in more than
 * one place - you need dsql. If you are using control structures to build
 * query - you need dsql.
 *
 * Let's start with basic examples and you'll know right away what dsql exactly is.
 */

include '../../trunk/lib/DBlite.php';

$db=DBLite::tryConnect('mysql://hide@address.com/mysql');
if(is_string($db))$db=DBlite::connect('mysql://hide@address.com/mysql');
/*
 * Let's try see how many entries are there in the host table
 */
$q = $db->dsql();
$q->table('host');
$q->field('count(*)');
$query = $q->select();

echo "<p>query=$query</p>";

/*
 * That's basicly it! There are functions to add where clauses, group,
 * order and join parts of the query. Oh and you can reuse the same
 * query to add more fields to it, let's try
 */
$q->join('user','host.Host=user.Host');
$q->field('user.User');
$q->group('user.User');
$query = $q->select();

echo "<p>query=$query</p>";

/*
 * Next an interesting example demonstrating 
echo "<p>Structure of 'host' table:";
$db->query("desc host");
echo "<table border=0 cellspacing=0 cellpadding=3 style='border: 1px solid black'>";
while($row = $db->fetchRow()){
    echo "<tr>";
    foreach($row as $field){
        echo "<td>$field</td>";
    }
    echo "<td>".
        count($q->do_getAll())
        ."</td>";
    echo "</tr>\n";
}
echo "</table></p><hr/>";

/*
 * You can work with Hashes. Those are sometimes refered as associative arrays.
 * Instead of representing each row with array, it's now consists of
 *   'field_name'=>'value'
 * pairs. With those you don't have to worry about number of fields and their order.
 */
echo "<p>Some info from users table:";
$db->query("select * from user");
echo "<table border=0 cellspacing=0 cellpadding=3 style='border: 1px solid black'>";
while($row = $db->fetchHash()){
    echo "<tr>";
    echo "<td>{$row['User']}</td><td>{$row['Host']}</td>";
    echo "</tr>\n";
}
echo "</table></p><hr/>";
/*
 * Unfortunately even if mysql is case insensetive for fields, here you should know
 * exact case of your fields.
 */

/*
 * Error handling
 *
 * DBlite triggers default php errors unless you redefine error handler
 */
echo "<p>";
$db->query("select foo from bar");
echo "</p><hr/>";

/*
 * But you can also re-define error handler on your own. It can be function or method.
 * See manual for function is_callable() 
 */

function my_error_handler($string, $class){
    echo "MySQL error. Please try latter";
    // here we can send actuall error message to owner's email
}
$db->error_handler='my_error_handler';
echo "<p>";
$db->query("select foo from bar");
echo "</p><hr/>";

/*
 * This concludes basics of the DBlite module. See next lession for further information
 */

/*
 * There are actually 3 output modes:
 *  DB_FETCHMODE_ORDERED
 *  DB_FETCHMODE_ASSOC
 *  DB_FETCHMODE_IDROW
 * (constants are made compatible with PEAR::DB)
 *
 * You can pass fetchmode to some functions such as
 *
 * fetchRow (except IDROW)
 * getRow (except IDROW)
 * getAll (supports all 3 modes)
 *
 * You can also specify default mode when initializing (DBlite::connect), however
 * we do not advise you to do so. Those are only for compatibility, but if you
 * are writing your application, do not specify fetchmode when initializing and
 * use custom functions such as getHash, fetchHash and getAssoc
 *
 * getAssoc will return structure 2d array (similar to getAll) with IDKEY and ASSOC modes:
 * array(
 *  first_field_value=>array(2nd_field_name=>2nd_field_value, 3rd_field_name=>3rd_field_value),
 *  first_field_value=>array(2nd_field_name=>2nd_field_value, 3rd_field_name=>3rd_field_value),
 *  );
 *
 * Name of this function is missleading so it's better if you just pass desired
 * fetchmode as a second argument to getAll();
 */