erickfirmo / phpmodel
MySQL query builder class in PHP.
Requires
- php: >=7.4
Requires (Dev)
- phpunit/phpunit: ^5.0
README
MySQL Query Builder in PHP - by Erick Firmo - https://erickfirmo.dev
Requirements
- PHP >= 7.4
Install
Install with composer:
composer require erickfirmo/phpmodel
Namespace
<?php use ErickFirmo\Model;
Usage example
<?php // Requires composer autoloader require __DIR__ . '/vendor/autoload.php'; use ErickFirmo\Model; // Creating a class for the entity class Car extends Model { protected $table = 'cars'; protected $fillable = [ 'name', 'company', 'year', 'plate', 'color' ]; } // Insert register example, returns boolean $saved = (new Car())->insert([ 'name' => $name, 'company' => $company, 'year' => $year, 'plate' => $plate, 'color' => $color, ]); // Select register example, returns collection $cars = (new Car())->select() ->where('year', '=', $year) ->get();
Collections
{ "model": "App\\Models\\Car", "table": "cars", "attributes": [ "id", "name", "company", "year", "plate", "uf", "color", "price" ], "items": [ { "id": "12", "name": "Fusca", "company": "VW", "year": "1934", "plate": "ERX-8761", "uf": "SP", "color": "yellow", "price": "89000" }, { "id": "13", "name": "Uno", "company": "Fiat", "year": "1934", "plate": "ERX-8761", "uf": "SP", "color": "red", "price": "89000" }, { "id": "14", "name": "Chevette", "company": "Chevrolet", "year": "1934", "plate": "ERX-8761", "uf": "SP", "color": "black", "price": "89000" }, ], "pages": [ ], }
Query
Methods that facilitate the execution of mysql queries in the database:
Select
Select all columns from the table using the select
method. Use the get
method to perform a query and return a collection:
<?php // Returns all columns from `cars` table $cars = (new Car())->select() ->get();
Select specific columns from the table passing an array as parameter in select
method. Use the get
method to perform a query:
<?php // Returns specific columns from model table $cars = (new Car())->select(['name', 'company', 'year']) ->get();
Where
Adding where clause to query builder:
<?php $cars = (new Car())->select() ->where('company', '=', $company) ->get();
Adding multiple where clause to query builder:
<?php $cars = (new Car())->select() ->where('company', '=', $company) ->where('year', '=', $year) ->get();
Insert
Inserting record into database table:
<?php $saved = (new Car())->insert([ 'name' => $name, 'company' => $company, 'plate' => $plate, 'year' => $year, 'color' => $color, ]);
Update
Updating register into database table:
<?php $saved = (new Car())->update($id, [ 'plate' => $plate, 'color' => $color, ]);
Delete
Deleting register into database table:
<?php $saved = (new Car())->delete($id);
FindById
Searching register by id:
<?php $car = (new Car())->findById($id);
OrderBy
You can configure the ordering as ascending or descending using the words asc
or desc
as parameter in orderBy
method.
Ordering as ascending:
<?php $cars = (new Car())->select() ->orderBy('asc') ->get();
Ordering as descending:
<?php $cars = (new Car())->select() ->orderBy('desc') ->get();
Limit
Limiting number of records in the query:
<?php $cars = (new Car())->select() ->limit(50) ->get();
Pagination
We can paginate records using the paginate
method. We must pass the desired number of records per page as a parameter. This method has a value of 10 by default.
In this example, we have 100 records, and we'll display 25 per page:
<?php $cars = (new Car())->select() ->paginate(25);
By default, the pages
attribute of the collections will be an array with the number of pages:
"pages": [ 1, 2, 3, 4 ],
We can use this array to create our paging component. Simple example of page component in with php and bootstrap:
<nav aria-label="Page navigation example"> <ul class="pagination"> <?php foreach ($cars->pages as $key => $page) { ?> <li class="page-item <?php echo (!isset($_GET['page']) && $page == 1) || $_GET['page'] == $page ? 'active' : ''; ?>"> <a class="page-link" href="<?php echo 'pessoas?page='.$page; ?>"> <?php echo $page; ?> </a> </li> <?php } ?> </ul> </nav>