<?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>';