<?php
/*
 * This file contains some tests to check the sqlBuilder's functionality.
 * The statements maybe a bit stupid.
 *
 * $Id$
 */

 
// if you do not have PEAR installed on your webserver then use this two lines to include the 
// needed PEAR files, that you downloaded with tgcSqlBuilder
// make sure to set the includefolder correctly
// $pearIncludeFolder  =   './include/pear';
// ini_set('include_path', ini_get('include_path') . ':' . $pearIncludeFolder);

 
require_once 'PEAR.php';
require_once 'DB.php';
require_once 'include/tgcSqlBuilder.php';
require_once 'include/tgcSqlBuilder/Select.php';

error_reporting(E_ALL);

$dbc    =       DB::connect('mysql://root:offline@localhost/tgcNewsSystem');
$sql    =       &new tgcSqlBuilder_Select($dbc);

$sql->enableDistinct();

// FROM
$sql->addTable('users');
$sql->addTable('groups', 'g');
$sql->addTable('permissions', 'p');
// ---
$sql->removeTable('groups');


// add some select columns
$sql->addSelect('users', 'username');
$sql->addSelect('users', 'priority', 'prio');
$sql->addRawSelect('AVG(users.wage)', 'avg_wage');
// ---
$sql->removeSelect('prio');


// add WHERE statements
$sql->addWhere('users', 'username', 'max', SQLBUILDER_COMP_EQUAL, SQLBUILDER_LOGICAL_OR);
$sql->addWhere('users', 'username', 'max');
$sql->addWhere('users', 'username', 'carsten');
$sql->addRawWhere('users.userId = groups.userId');
// ---
$sql->removeWhere('users', 'username', SQLBUILDER_LOGICAL_AND);
$sql->removeRawWhere(SQLBUILDER_LOGICAL_OR);


// add some GROUPY BY statements
$sql->addGroupBy('users', 'username', SQLBUILDER_ORDER_ASC);
$sql->addGroupBy(2, null, SQLBUILDER_ORDER_DESC);


// add HAVING
$sql->addHaving('users', 'username', 'max', SQLBUILDER_COMP_EQUAL, SQLBUILDER_LOGICAL_OR);
$sql->addHaving('users', 'username', 'max');
$sql->addHaving('users', 'username', 'carsten');
$sql->addRawHaving('users.userId = groups.userId');
// ---
$sql->removeHaving('users', 'username', SQLBUILDER_LOGICAL_AND);
$sql->removeRawHaving(SQLBUILDER_LOGICAL_AND);


// ORDER BY
$sql->addOrderBy('users', 'username', SQLBUILDER_ORDER_ASC);
$sql->addOrderBy('alias', null, SQLBUILDER_ORDER_DESC);
$sql->removeOrderBy('alias');

// LIMIT
$sql->setLimit(15, 10);

// generate query
$q      =       $sql->generateQuery();
echo $q;
    
?>