<?php
/*
* EXAMPLE 1 FILE FOR PTCQUERYBUILDER CLASS, PREPARING QUERIES FOR LATER USAGE
* ALL QUERIES THAT ARE PREPARED WITH THE QUERYBUILDER, NEED PLACE HOLDERS
* TO SEE THE QUERIES EXECUTED REFER TO PTCQUERYBUILDER-EX2.PHP EXAMPLE FILE
*/
require_once '../PtcQueryBuilder.php';
// including the query builder component
$qb = new PtcQueryBuilder();
// initializing the class
/* SELECT ALL RECORDS */
$query = $qb->table('test_table')->prepare();
/* SELECT WITH COLUMNS SPECIFIED */
$query_with_columns = $qb->table('test_table')->select('some_column as test')->prepare();
/* SELECT WITH A WHERE CLAUSE */
$query_where = $qb->table('test_table')->where('field1', '=', ':value')->prepare();
/* SELECT WITH WHERE OR WHERE CLAUSE */
$query_where1 = $qb->table('test_table')->where('field1', '=', ':value1')->or_where('field2', '=', ':value2')->prepare();
/* SELECT WITH LIMIT, GROUP BY, ORDER AND LIMIT */
$query_where2 = $qb->table('test_table')->order('field1', 'desc')->limit(':start', ':end')->prepare();
/* INSERT */
$fields = array('field1' => ':value1', 'field2' => ':value2', 'field3' => ':value3');
$query_insert = $qb->table('test_table')->insert($fields)->prepare();
/* UPDATE BASED ON ID */
$fields = array('field1' => ':value1', 'field2' => ':value2');
$query_update = $qb->table('test_table')->update($fields, ':id')->prepare();
/* UPDATE BASED ON A WHERE CLAUSE */
$fields = array('field1' => ':value1', 'field2' => ':value2');
$query_update1 = $qb->table('test_table')->where('field2', '=', ':value3')->update($fields)->prepare();
/* DELETE BASED ON ID */
print print_r($qb->run($query_between, $fields), true) . "</pre><br>";
/* USING WHERE IN */
$fields = array(':1' => 20, ':2' => 180, ':limit' => 10);
print "<b>prepared select where in query result:</b> <pre>";
print print_r($qb->run($query_in, $fields), true) . "</pre><br>";
/* DELETING DATA WITH PREVIOUSLY PREPARED STATEMENTS */
$qb->run($query_delete, array(':id' => $last_id));
print '<b>Number of affected rows by delete based on id query:</b> ';
print $qb->countRows() . '<br><br>';
$qb->run($query_delete1, array(':value' => 'somevalue'));
print '<b>Number of affected rows by delete based on where clause query:</b> ';
print $qb->countRows() . '<br><br>';
/* CREATE ONE MORE EXAMPLE TABLE FOR THE JOIN QUERY */
$qb->run("CREATE TABLE `test_table1` \n\t(\n\t\t`id` int NOT NULL AUTO_INCREMENT, \n\t\tPRIMARY KEY(`id`),\n\t\t`field4` varchar(255)\n\t)");
/* RUNNING QUERIES WITH RUN() INSTEAD OF PREPARE(), NO PLACE HOLDERS NEEDED! */
$qb->table('test_table1')->insert(array('field4' => 'somevalue'))->run();
/* GET LAST INSERTED ID */
$last_id = $qb->lastId();
print '<b>last inserted Id:</b> ' . $last_id . '<br><br>';
/* JOINING TABLES WITH PREVIOUSLY PREPARED QUERY,
REPLACE "left_" WITH THE TYPE OF JOIN YOUR ARE LOOKING FOR */
$qb->run($query_join);
/* RETRIEVEING ONLY ONE ROW */
print "<b>return only 1 row query result:</b> <pre>";
print print_r($qb->table('test_table1')->where('field4', '=', 'somevalue')->row(), true) . '</pre><br><br>';
/* RETRIEVEING ONLY ONE COLUMN VALUE */
print "<b>return only column value:</b> ";
print print_r($qb->table('test_table1')->where('field4', '=', 'somevalue')->row('field4'), true) . '<br><br>';
/* SELECTING A ROW BASED ON ID */
print "<b>return record with ->find(yourID) , shortcut for where('id' , '=' , yourID ):</b> <pre>";
print print_r($qb->table('test_table1')->find($last_id), true) . '</pre><br><br>';