<!DOCTYPE HTML> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>MYSQL (QUERY BUILDER)</title> </head> <body> <?php
// TABLE USED IN THE EXAMPLE
/*
CREATE TABLE `table` ( `id` int(10) NOT NULL AUTO_INCREMENT, `fname` varchar(200) NOT NULL, `lname` varchar(200) NOT NULL, `birth` date NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
*/
// 1. HOW TO INSTANCE THE CLASS?
include("lib/config.php"); include("lib/mysql.class.php"); $db = new mysql();
// 2. INSERTS
// nextid([table],[key]) // insert([table],[values],[key],[key_value])
// 2.1. INSERT WITH THE NEXT ID IN A VARIABLE
// define the values of the query in a array or object $values["fname"] = "Rodrigo"; $values["lname"] = "Brandão"; $values["birth"] = "19851210"; // use this function to get the next id from a table $id = $db->nextid("table","id"); // execute the insert query $db->insert("table",$values,"id",$id);
// 2.2. INSERT INTO A TABLE WITH AUTO-INCREMENTO ID // define the values of the query in a array or object $values["fname"] = "Rodrigo"; $values["lname"] = "Brandão"; $values["birth"] = "19851210"; // execute the insert query (the return will be the id added) $id = $db->insert("table",$values); // 3. UPDATES
// 3.1 SIMPLE UPDATES
// simple_update([table],[values],[key],[key_value]) // sets the id of the row that will be updated $id = 1; // define the values of the query in a array or object $values = (object)NULL; $values->fname = "Rodrigo"; $values->lname = "Brandão"; $values->birth = "19851210"; // execute the update query $db->simple_update("table",$values,"id",$id);
// 3.2 UPDATES
// update([table],[values],[where]) // define the values of the query in a array or object $values = NULL; $values['fname'] = "Rodrigo"; $values['lname'] = "Brandão"; $values['birth'] = "20120101"; // execute the update query $db->update("table",$values,"id >= 10"); // 4. DELETES // delete([table],[key],[key_value]) // sets the id of the row that will be deleted $id = 99; // execute the delete query $db->delete("table","id",$id);
// 5. SELECTS
// sselect([table],[cols],[key],[key_value],[order],[limit_ini],[limit_end]) // select([table],[cols],[where],[order],[limit_ini],[limit_end])
// 5.1. EXECUTE A SIMPLE SELECT QUERY
// sets the id of the row that will be selected $id = 1; // execute the simple select query $sselect = $db->sselect("table","*","id",$id); // show the return var_dump($sselect); // 5.2. EXECUTE A SELECT QUERY WITH THE 'WHERE' ARE ADDED MANUALLY
// execute the select query $select = $db->select("table","id,lname",'(`id` > 1) AND (`id` < 5)',"lname",0,5); // show the return var_dump($select);
// 6. SEARCH QUERY
// search([table],[cols],[where],[is],[order],[limit_ini],[limit_end]) // execute a search query $search = $db->search("table","*","lname","Rodrigo%"); // show the return var_dump($search);
// 7. EXECUTE A QUERY
// secure([value]) // query([sql]) // calls the function which protects the variable from sql injection $fname = $db->secure("Rodrigo'; drop table example; --"); $lname = $db->secure("Brandão"); // execute a complex query $complex = $db->query("SELECT * FROM `table` WHERE `fname`='".$fname."' AND `lname`='".$lname."'"); // show the return var_dump($complex); // 8. HOW USE ANOTHER DATABASE IN THE SAME TIME? $host = "localhost"; $user = "root"; $pass = "root"; $name = "database"; $char = "utf8"; $db2 = new mysql($host,$user,$pass,$name,$char);
// 9. HOW TO ACTIVATE DEBUGGING PROCESS?
//config.php // define("DB_DEBUG",true);
|