MaplePHP - MySQL queries is a powerful yet user-friendly library for making safe database queries.
Contents
Connect to the database
Make queries
Attributes
Migrations (Coming soon)
Install the database
Install the database with the MakePHP cli.
php cli config install --type=mysql
Make queries
Provider
Recommended: Make database queries with the provider.
First access the provider:
$this->db =$this->provider->DB();
Then access the DB library:
$select = $this->db::select("id,firstname,lastname", ["users", "aliasA"])->whereId(1)->where("status", 0, ">")->limit(1);
$obj = $select->get(); // Get one row result as object
Manual
Or utilize the library directly.
Set the the namespace
useMaplePHP\Query\DB;
Select 1:
$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
Default alias will be the table name e.g. users and login if they were not overwritten.
Select 2:
$select =DB::select("id,name,content","pages")->whereStatusParent(1,0);$array = $select->fetch(); // Get all rows as an array
Where 1
$select->where("id",1); // id = '1'$select->where("parent",0,">"); // parent > '1'
Where 2
"compare", "or"/"and" and "not".
$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("john.doe@gmail.com");// NOT id = '1' AND email = 'john.doe@gmail.com'
Where 3
$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')
Where 4
$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
Having
Having command works the same as where command above with exception that you rename "where" method to "having" and that the method "havingBind" do not exist.
$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->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
Join
Note that no value in the join is, by default, enclosed. This means it will not add quotes to strings. This means it will attempt to add a database column by default if it is a string, and will return an error if the string column does not exist. If you want to enclose the value with quotes, use Attributes (see the section below).
$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"]);
Insert
$insert =DB::insert("pages")->set(["id"=>36,"name"=>"About us","slug"=>"about-us"])->onDupKey();$insert->execute(); // bool$insertID = $select->insertID(); // Get AI ID
Update on duplicate
Will update row if primary key exist else Insert
$insert->onDupKey(); // Will update all the columns in the method @set$insert->onDupKey(["name"=>"About us"]); // Will only update the column name
$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!
Preview SQL code before executing
echo $select->sql();
Attributes
Each value is automatically escaped by default in the most effective manner to ensure consequential and secure data storage, guarding against SQL injection vulnerabilities. While it's possible to exert complete control over SQL input using various Raw methods, such an approach is not advisable due to the potential for mistakes that could introduce vulnerabilities. A safer alternative is to leverage the Attr class. The Attr class offers comprehensive configuration capabilities for nearly every value in the DB library, as illustrated below:
The default values vary based on whether it is a table column, a condition in a WHERE clause, or a value to be set. For instance, if expecting a table columns, the default is not to enclose value with quotes, whereas for WHERE or SET inputs, it defaults is to enclose the values. Regardless, every value defaults to prep, encode and jsonEncode being set to true.