<!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);
|
Download
