PHP Classes

File: example.php

Recommend this page to a friend!
  Classes of Rodrigo Brandão   MySQL Query Builder   example.php   Download  
File: example.php
Role: Example script
Content type: text/plain
Description: How to use the class.
Class: MySQL Query Builder
Build and execute MySQL queries from parameters
Author: By
Last change: new features
Date: 11 years ago
Size: 3,720 bytes
 

Contents

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