PHP code example of maplephp / query

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

maplephp / query example snippets

use MaplePHP\Query\Connect;

$handler = new MySQLHandler(
    $server,
    $user,
    $password,
    $databaseName,
    $port = 3306
);

// Recommend: Set TABLE prefix. This will make your life easier
// MaplePHP will automatically prepend the prefix to the table names.
$handler->setPrefix("maple_");
$handler->setCharset("utf8mb4");
$connect = Connect::setHandler($handler);
$connect->execute();

use MaplePHP\Query\Connect;

$SQLiteHandler = new SQLiteHandler(__DIR__ . "/database.SQLite");
$SQLiteHandler->setPrefix("mp_");
$connect = Connect::setHandler($SQLiteHandler);
$connect->execute();

use MaplePHP\Query\Connect;

$postgreSQLHandler = new PostgreSQLHandler($server, $user, $password, $databaseName, $port = 5432);
$postgreSQLHandler->setPrefix("mp_");
$connect = Connect::setHandler($postgreSQLHandler);
$connect->execute();

use MaplePHP\Query\DB;

$select = DB::select("id,firstname,lastname", ["users", "aliasA"])->whereId(1)->where("status", 0, ">")->limit(1);
$select->join(["login", "aliasB"], "aliasB.user_id = aliasA.id");
$obj = $select->get(); // Get one row result as object

$select = DB::select("id,name,content", "pages")->whereStatusParent(1, 0);
$array = $select->fetch(); // Get all rows as an array

 
$select->where("id", 1); // id = '1'
$select->where("parent", 0, ">");  // parent > '1'

 
$select->whereRoleStatusParent(1, 1, 0);  
// role = '1' AND status = '1' AND Parent = 0
$select->compare(">")->whereStatus(0)->or()->whereRole(1);
// status > '0' OR role = '1'
$select->not()->whereId(1)->whereEmail("[email protected]");
// NOT id = '1' AND email = '[email protected]'

 
$select->whereBind(function($select) {
    $select
    ->where("start_date", "2023-01-01", ">=")
    ->where("end_date", "2023-01-14", "<=");
})->or()->whereStatus(1);
// (start_date >= '2023-01-01' AND end_date <= '2023-01-14') OR (status = '1')

 
$select->whereRaw("status = 1 AND visible = 1");  
// UNPROTECTED: status = 1 AND visible = 1
$select->whereRaw("status = %d AND visible = %d", [1, 1]);  
// PROTECTED: status = 1 AND visible = 1

 
$select->having("id", 1); // id = '1'
$select->having("parent", 0, ">");  // parent > '1'
$select->havingRaw("status = 1 AND visible = 1");  
$select->havingRaw("status = %d AND visible = %d", [1, 1]);  

 
$select->limit(1); // LIMIT 1
$select->offset(2); // OFFSET 2
$select->limit(10, 2); // LIMIT 10 OFFSET 2

 
$select->order("id"); 
// ORDER BY price ASC
$select->order("price", "DESC");
// ORDER BY price DESC
$select->order("id", "ASC")->order("parent", "DESC"); 
// ORDER BY id ASC, parent DESC
$select->orderRaw("id ASC, parent DESC"); 
// ORDER BY id ASC, parent DESC

 
$select->join(["login", "aliasB"], ["aliasB.user_id" => "aliasA.id"]); // PROTECTED INPUT

$select->join("login", ["user_id" => "id"]);
// user_id = id AND org_id = oid

// This will enclose and reset all protections
$slug = DB::withAttr("my-slug-value"); 
$select->join("login", [
    ["slug" => $slug],
    ["visible" => 1]
]);

$select->join("tableName", "b.user_id = '%d'", [872], "LEFT"); // PROTECTED INPUT
$select->join("tableName", "b.user_id = a.id"); // "UNPROTECTED" INPUT

$select->joinInner("tableName", ["b.user_id" => "a.id"]);
$select->joinLeft("tableName", ["b.user_id" => "a.id"]);
$select->joinRight("tableName", ["b.user_id" => "a.id"]);
$select->joinCross("tableName", ["b.user_id" => "a.id"]);

$select->pluck("a.name")->get();

 
$insert = DB::insert("pages")->set(["id" => 36, "name" => "About us", "slug" => "about-us"])->onDupKey();
$insert->execute(); // bool
$insertID = $select->insertID(); // Get AI ID

 
$insert->onDupKey(); 
// Will update all the columns in the method @set
$insert->onDupKey(["name" => "About us"]); 
// Will only update the column name

 
$update = DB::update("pages")->set(["name" => "About us", "slug" => "about-us"])->whereId(34)->limit(1);
$update->execute();

 
$delete = DB::delete("pages")->whereId(34)->limit(1);
$delete->execute();

 
$select->set("firstname", "John")->set("lastname", "Doe");
// Update/insert first- and last name
$select->set(["firstname" => "John", "lastname" => "Doe"])->set("lastname", "Doe"); 
// Same as above: Update/insert first- and last name
$select->setRaw("msg_id", "UUID()");
// UNPORTECTED and and will not be ENCLOSED!

 
echo $select->sql();

 
$idValue = DB::withAttr("1")
    ->prep(true)
    ->enclose(true)
    ->encode(true)
    ->jsonEncode(true);
    
$select->where("id",  $idValue);

 
public function prep(bool $prep): self;

 
public function enclose(bool $enclose): self;

 
public function encode(bool $encode): self;

 
public function jsonEncode(bool $jsonEncode): self;

use MaplePHP\Query\Connect;

// DB: 1
$handler = new MySQLHandler(
    $server,
    $user,
    $password,
    $databaseName,
    $port
);
$handler->setPrefix("maple_");
$handler->setCharset("utf8mb4");
$connect = Connect::setHandler($handler);
$connect->execute();

// DB: 2
$SQLiteHandler = new SQLiteHandler(__DIR__ . "/database.SQLite");
$SQLiteHandler->setPrefix("mp_");
$connect = Connect::setHandler($SQLiteHandler, "myConnKey");
$connect->execute();

// The connections have been made!
// Let's do some queries 

// DB-1 Query: Access the default MySQL database 
$select = DB::select("id,firstname,lastname", "users")->whereId(1)->limit(1);
$obj = $select->get();

// DB-2 Query: Access the SQLite database
$select = Connect::getInstance("myConnKey")::select("id,name,create_date", "tags")->whereId(1)->limit(1);
$obj = $select->get();