Download the PHP package mattsmithdev/pdo-crud-for-free-repositories without Composer

pdo-crud-for-free-repositories

Note - this is essentially an alternative approach to the pdo-crud-for-free package

This package provides a few classes to try to give programmers using PDO (with MySQL) in a simple way some instance CRUD (create-read-update-delete) methods, 'for free', simply by creating an entity repository sub-class of Mattsmithdev\PdoCrudRepo\DatabaseTableRepository.

All code is (intended :-) to follow PSR-1, PSR-12 coding standards. Classes are following the PSR-4 autoloading standard.

Example project using this library

There is an example project illustrating use of this library:

• https://github.com/dr-matt-smith/pdo-crud-for-free-repositories-example-project

Install

Via Composer

Usage

This example assumes you have a MySQL DB table named 'movie', with columns 'id' and 'title'. You need to write a corresponding class 'Movie' (note capitalization on the first letter - since this is a PHP class). Also you need to write a repository class to work between your PHP class and is corresponding table, in this example the repository class is named 'MovieRepository':

Finally, you need to have defined your DB connection credentials in a file .env as follows:

The named database schema will be created, if it does not already exist...

For more details see below. Also there is a full sample web application project on GitGub at: pdo-crud-for-free-repositories-example-project

More detailed usage instructions (and important assumptions)

ASSUMPTION 1: lowerCamelCase - DB table column names matching PHP Class properties

This tool assumes your database table column names, and their corresponding PHP private class properties are named consistently in 'lowerCamelCase' e.g.

id
title
category
price
vatRate
firstName
aLongVariableNameOfSeveralWords

ASSUMPTION 2: No constructor for your PHP classes.

due to the nature of PDO populating properties of objects when DB rows are converted into object instances do not have a constructor for the PHP classes that correspond to your DB tables

so you'd create a new object, and use the objects public 'setter' methods e.g.

ASSUMPTION 3: Each class has an integer, id property

Each Entity class should have an integer id property. This property should be an AUTO_INCREMENT primary key in the database table schema, e.g.

NOTE: Please don't name this anything else, not idMovie or movieId or ID etc. - just plain old id

ASSUMPTION 4: DB table name is singular and all lower case

This tool assumes your database table name is singular, all lower case. E.g.

• table name: movie

  • entity class name: Movie.php

• table name: moviecategory

  • entity class name: MovieCategory.php
• table name: alongtablename
  • entity class name: ALongTableName

Step 1: Create your DB tables.

You need a database schema

• if not present, a new one will be created

For each entity class you need a corresponding DB table (with integer 'id' field, primary key, auto-increment)

• you can create these in your DB management tool
• you can use the Repository method createTable()
  • which can attempt to infer column data types from yhour entity properties, or use your own provided SQL

Step 2: Create a corresponding PHP (entity) class

e.g.

        `

Step 3: Create a repository class mapping your DB table to your PHP entity class (that is a subclass from Mattsmithdev\PdoCrudRepo\DatabaseTableRepository)

e.g. create repository class MovieRepository mapping from table movie to PHP class Evote\Movie:

Note - personally I find it handy to add a method to create a new object and insert it into the DB - e.g.:

Step 4: Define your MySQL database credentials in a file .env

Define your DB connection credentials in a file .env as follows:

Step 5: Now use the 'magically appearing' DB CRUD methods.

e.g. to get an array of all movie records from table 'movie' just write:

NOTE: Can pass optional params to override defaults when creating Repository class:

• if Repository is in a different namespace, pass in name of namespace:

• class name - if not name before Repository

• tablename not lowercase version of entity class for repo:

->findAll()

this method returns an array of ALL objects for each row of the corresponding DB table e.g.

->find($id)

this method returns ONE object of class for the corresponding DB table record with the given 'id' (returns 'null' if no such record exists with that primary key id) e.g.

->delete($id)

this method deletes the record corresponding to the given 'id' returns true/false depending on success of the deletion e.g.

->deleteAll()

this method deletes ALL records for the associated database table e.g.

->insert($movie)

this method adds a new row to the database, based on the contents of the provided object (any 'id' in this object is ignored, since the table is auto-increment, so it's left to the DB to assign a new, unique 'id' for new records) returns the 'id' of the new record (or -1 if error when inserting) e.g.

->update($movie)

This method adds a UPDATES an existing row in the database, based on the contents of the provided object returns true/false depending on success of the deletion

e.g.

->searchByColumn($columnName, $searchText))

Perform an SQL '%' wildcard search on the given column with the given search text returns an array of objects that match an SQL 'LIKE' query

e.g.

->dropTable()

Deletes the associated database table and all its data

e.g.

->createTable()

(method 1) If no SQL parameter is provided, then the code looks for a constant CREATE_TABLE_SQL in the associated entity class, and will execute that SQL. (method 2) If no such constant is found, the repository class will attempt to infer DB datatypes based on the data types of your entity class properties.

Method 2 - automatic DB column type inference

A class with typed properties like this doesn't need you to provide any SQL for the metod to work

Method 1 - constant declaring your table create SQL

Here is an example where the class expliciity delares a constant CREATE_TABLE_SQL containing the table create SQL you want to use:

->createTable($sql)

As above, but the SQL to create the table can be provided as a string parameter to the method

->resetTable( $sql = null )

This runs the sequence drop / create / delete all:

any SQL provided as a parameter is passed through to createTable(...).

Then in our migration code (for example) we can drop the old table and create a new one as follows:

custom PDO methods

If the 'free' DB methods are insufficient, it's easy to add your own methods to your PHP classes that correspond to your DB tables.

Here is a method that could be added to a class Product allowing a custom search by 'id' and text within 'description':

and here is an example of its usage, in a controller function:

Migrations and fixtures

Here are examples of a simple scripts to update a table schema and insert some initial data.

Using a createAndInsert(...) Repository method

If we have added a createAndInsert(...) method to our Repository class then fresetting the databasde and inserting fixture data can be as simple as this:

Here is what that createAndInsert(...) method might look like:

Using accessor methods to create object data

If we don't have createAndInsert(...) method then we have to create each object and then insert it into the DB table using the insert(...) method:

OUTPUT:

Changelog

Please see CHANGELOG for more information what has changed recently.

Testing

Contributing

Please see CONDUCT for details.

Security

If you discover any security related issues, please email [email protected] instead of using the issue tracker.

Credits

• Matt Smith

License

The MIT License (MIT). Please see License File for more information.