Download the PHP package popphp/pop-db without Composer

On this page you can find all versions of the php package popphp/pop-db. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.

FAQ

After the download, you have to make one include require_once('vendor/autoload.php');. After that you have to import the classes with use statements.

Example:
If you use only one package a project is not needed. But if you use more then one package, without a project it is not possible to import the classes with use statements.

In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
Some PHP packages are not free to download and because of that hosted in private repositories. In this case some credentials are needed to access such packages. Please use the auth.json textarea to insert credentials, if a package is coming from a private repository. You can look here for more information.

  • Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
  • To use Composer is sometimes complicated. Especially for beginners.
  • Composer needs much resources. Sometimes they are not available on a simple webspace.
  • If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
  • Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Please rate this library. Is it a good library?

Example code of popphp/pop-db

Informations about the package pop-db

pop-db

Build Status Coverage Status

Join the chat at https://popphp.slack.com Join the chat at https://discord.gg/TZjgT74U7E

Overview

pop-db is a robust database ORM-style component that provides a wide range of features and functionality to easily interface with databases. Those features include:

pop-dbis a component of the Pop PHP Framework.

Top

Install

Install pop-db using Composer.

composer require popphp/pop-db

Or, require it in your composer.json file

"require": {
    "popphp/pop-db" : "^6.0.0"
}

Top

Quickstart

Connect to a database

You can connect to a database using the Pop\Db\Db::connect() method:

Or, alternatively, there are shorthand methods for each database connection type:

If no host value is given, it will default to localhost.

Top

Query a database

Once you have a database object that represents a database connection, you can use it to query the database. There is an API to support making a query and returning the result:

The above methods supports SQL queries as well as prepared statements with parameters.

If there are any user records in the users table, the result will be:

An INSERT Example

The more verbose way to make a query would be:

Top

Table Class

Part of the benefit of using an ORM-style database library like pop-db is to abstract away the layer of SQL required so that you only have to concern yourself with interacting with objects in PHP and not writing SQL. The ORM does it for you. An example of this is using a table class that represents the active record pattern (which will be explored more in-depth below.)

In the above example, a database object is created and passed to the Pop\Db\Record class. This is so that any classes that extend Pop\Db\Record will be aware of and have access to the database object.

Then, a table class that represents the users table in the database extends the Pop\Db\Record class and inherits all of its built-in functionality. From there, methods can be called to fetch data out of the users table or save new data to the users table.

Fetch users

Fetch user ID 1

Edit user ID 1

Create new user

Delete user ID 1

Top

Adapters

The basics of connecting to a database with an adapter was outlined in the quickstart section. In this section, we'll go over the basics of each database adapter. Each of them have slightly different connection parameters, but once the different adapter objects are created, they all share a common interface to interact with the database.

Top

MySQL

The supported options to create a MySQL database adapter and connect with a MySQL database are:

The Pop\Db\Adapter\Mysql object that is returned utilizes the mysqli class available with the mysqli PHP extension.

Top

PostgreSQL

The supported options to create a PostgreSQL database adapter and connect with a PostgreSQL database are:

The Pop\Db\Adapter\Pgsql object that is returned utilizes the pg_* functions available with the pgsql PHP extension.

Top

SQLite

The supported options to create a SQLite database adapter and connect with a SQLite database are:

The Pop\Db\Adapter\Sqlite object that is returned utilizes the Sqlite3 class available with the sqlite3 PHP extension.

NOTE: It is important to make sure the database file has the appropriate permissions for the database adapter to be able to access and modify it.

Top

SQL Server

The supported options to create a SQL Server database adapter and connect with a SQL Server database are:

The Pop\Db\Adapter\Sqlsrv object that is returned utilizes the sqlsrv_* functions available with the sqlsrv PHP extension.

Top

PDO

The PDO adapter works with the popular PDO extension available with PHP. This encompasses multiple database drivers that PDO supports. They provide an alternate to the other native drivers.

The supported options to create a PDO database adapter and connect with a PDO-supported database are:

The Pop\Db\Adapter\Pdo object that is returned utilizes the classes and functions made available by the PDO extension and its various available drivers.

Top

ORM

The main concept of the pop-db component is that of ORM - object relational mapping. This means that all of complex things that make databases work - connections, SQL queries, etc. - are abstracted away so the developer only has to worry about interacting with objects in PHP. The rest is handled for you, under the hood, in a secure and efficient manner.

Of course, if you prefer to directly work with those concepts that have been abstracted away, you can still do that with the pop-db component. It provides the flexibility for both styles of database interaction.

Top

Active Record

Central to the ORM-style of pop-db is its use of the active record pattern, which is built into the Pop\Db\Record class. As hinted at in the quickstart section, the main concept is to write "table" classes that represent tables in the database and that extend the Pop\Db\Record class.

Registering the database

In the above example, a users table class has been created that inherits all of the functionality of Pop\Db\Record. The database adapter has been registered with the Pop\Db\Record class, which means any table class that extends it will have access to that database adapter.

If you need to add specific database adapters to specific table classes, you can do that as well:

Table configuration

A few things are configured by default:

However, you can override that through table properties:

Once a table class is configured, there is a basic set of static methods to get the database adapter or other objects or info:

Fetch a record

The basic way to use the table class is to fetch individual record objects from the database. All of the following examples return an instance of Users.

By default, findLatest() will use the primary key, like id. However, you can pass it another field to sort by:

Find API

These are available static methods to find a record or records in the database table:

These are available static magic helper methods to find a record or records in the database table, based on certain conditions:

Modify a record

Once a record has been fetched, you can then modify it and save it:

or even delete it:

Other methods are available to modify an existing record:

Dirty records

If a record has been modified, the changes are stored and you can get them like this:

This is useful for application components that track and log changes to data in the database.

Top

Encoded Record

The Pop\Db\Record\Encoded class extends the Pop\Db\Record and provides the functionality to manage fields in the database record that require encoding, serialization, encryption or hashing of some kind. The supported types are:

The benefit of this class is that it handles the encoding and decoding for you. To use it, you would configure your class like this below, defining the fields that need to be encoded/decoded:

The above example means that any time you save to those fields, the proper encoding of the field data will take place and the correct encoded data will be stored in the database. Then, when you fetch the record and retrieve those fields, the proper decoding will take place, giving you the original decoded data.

1-Way Hashing

Using a password hash field would be an advanced example that would require more configuration:

This configuration will use the defined algorithm and options to safely create and store the one-way hash value in the database. Then, when needed, you can use the verify() method and check an attempted password against that stored hash.

2-Way Encryption

An even more advanced example would be using an 2-way encrypted field, which uses the Open SSL library extension. It requires a few more table properties to be configured:

You have to create an IV value and base64 encode it to set it as the $iv property.

This configuration will allow you to store the encrypted value in the database and securely extract it when you fetch the record.

Top

Table Gateway

The Pop\Db\Record class actually has functionality that allows you to fetch multiple records, or rows, at a time, much like a table data gateway. The default value returned from most of these calls is a Pop\Db\Record\Collection, which provides functionality to perform array-like operations on the rows or data. By default, each object in the collection is an instance of the table class that extends Pop\Db\Record, which allows you to work directly with those objects and modify or delete them.

Find records

You can use the toArray() method to convert the collection object into a plain array:

Or, in most methods, there is an $asArray parameter that will do the same:

Get count of records

If you just need to get a count of records, you can do that like this:

Top

Options

In most of the methods described above, there is an available $options array that allows you to really tailor the query. These are the supported options:

Select Columns

Pass an array of the fields you want to select with the query with the select key. This can help cut the amount of unwanted data that's returned, or help define data to select across multiple joined tables. If this option is not used, it will default to table_name.*

Offset

The start offset of the returned set of data. Used typically with pagination

Limit

The value by which to limit the results

Order

The field or fields by which to order the results

Group

The field or fields by which to group the results

Join

The join option allows you to define multiple tables to use in a JOIN query.

Basic Options Example

Options Example Using Join

Assume there is another table called Roles and the users table contains a role_id foreign key:

The join option defines the table to join with as well as which columns to join by. Notice that the select option was used to craft the required fields - in this case, all of user fields and just the role field from the roles table.

The type of join defaults to a LEFT JOIN, but a type key can be added to define alternate join types. You can also define multiple joins at a time in a nested array.

Top

Shorthand Syntax

There is shorthand SQL syntax that is available and supported by the Pop\Db\Record class to help give even a more granular control over your queries without having write your own or use the query builder. Here's a list of what is supported and what it translates into:

Basic operators

LIKE and NOT LIKE

NULL and NOT NULL

IN and NOT IN

BETWEEN and NOT BETWEEN

Additionally, if you need use multiple conditions for your query, you can and they will be stitched together with AND:

which will be translated into:

If you need to use OR instead, you can specify it like this:

Notice the OR added as a suffix to the second condition's value. That will apply the OR to that part of the predicate like this:

Top

Execute Queries

If any of the available methods listed above don't provide what is needed, you can execute direct queries through the table class.

Query (no parameters)

This will return a Pop\Db\Record\Collection object:

Prepared statement (w/ parameters)

This will return a Pop\Db\Record\Collection object:

Top

Active Record Transactions

Transactions are available through the ORM active record class. There are a few ways to execute a transaction with the main record class. In the below example, the transaction is started by calling the startTransaction() method. Once that has been called, the subsequent save() will automatically call commitTransaction() on successful save or the rollback method will be called upon an exception being thrown.

A shorthand way of doing the same would be to call the static start() method, which combines the constructor and startTransaction calls:

If you need to execute a transaction consisting of multiple queries across multiple active record objects, you can do that as well:

A shorthand method to achieve the same thing would be to use the transaction method with a callable:

Nested transactions are supported as well:

Top

Relationships

Relationships allow for a simple way to select related data within the database. These relationships can be 1:1 or 1:many, and you can define them as methods in your table class. The primary methods being leveraged here from within the Pop\Db\Record class are:

Let's consider the following tables classes that represent tables in the database:

With those relationships defined, you can now call the related data like this:

Eager-Loading

In the above examples, the related data is "lazy-loaded", meaning the related data isn't available until those relationship methods are called. However, you can call those relationship methods at the same time as you call the primary record using the static with() method:

Multiple relationships can be passed as well:

And nested relationships are supported too. Assume there is a Posts class and a Comments class. Also, let's assume a user object owns posts and a posts object owns comments, and the proper relationships are set up in each table class. Then this call would be valid:

And would give you a user object with all of the user's posts and each of those post objects would have their comments attached as well.

Top

Querying

Instead of using the ORM-based components, you can directly query the database from the database adapter. The API helps make specific queries or execute prepared statements, while returning the results:

In the case of select(), it will return an array of any found results. In the case of the others, it will return the number of affected rows.

Using a query

Using a prepared statements with parameters

The more verbose way to query the database would be:

Top

Prepared Statements

Taking it a step further, you can execute prepared statements as well:

Top

Query Transactions

When using a database adapter directly, you can utilize transactions with it, like these examples below:

You can also call a set of queries in one transaction like this:

Nested transactions are supported as well:

Top

Query Builder

The query builder is available to build valid SQL queries that will work across the different database adapters. This is useful if the application being built may deploy to different environments with different database servers. When a prepared query statement requires placeholders for binding parameters, use the named parameter format (e.g., 'id = :id'). It will be translated to the correct placeholder value for the database adapter.

Select

The following SQL query is produced for the MySQL adapter:

Switching to the PostgeSQL adapter, the same code will produce:

And switching to the SQLite adapter, and the same code will produce:

And of course, the $sql builder object can be passed directly to the database adapter to directly execute the SQL query that has been created:

Top

Insert

Top

Update

Top

Delete

Top

Joins

The SQL Builder has an API to assist you in constructing complex SQL statements that use joins. Typically, the join methods take two parameters: the foreign table and an array with a 'key => value' of the two related columns across the two tables. Here's a SQL builder example using a LEFT JOIN:

Here's the available API for joins:

Top

Predicates

The SQL Builder also has an extensive API to assist you in constructing predicates with which to filter your SQL statements. Here's a list of some of the available methods to help you construct your predicate clauses:

AND WHERE

OR WHERE

There is even a more detailed and granular API that comes with the predicate objects.

Top

Nested Predicates

If you need to nest a predicate, there are API methods to allow you to do that as well:

The output below shows the predicates for logins and failed are nested together:

Top

Sorting, Order, Limits

The SQL Builder also has methods to allow to further control your SQL statement's result set:

Top

Schema Builder

In addition to the query builder, there is also a schema builder to assist with database table structures and their management. In a similar fashion to the query builder, the schema builder has an API that mirrors the SQL that would be used to create, alter and drop tables in a database. It is also built to be portable and work across different environments that may have different chosen database adapters with which to work. And like the query builder, in order for it to function correctly, you need to pass it the database adapter your application is currently using so that it can properly build the SQL. The easiest way to do this is to just call the createSchema() method from the database adapter. It will inject itself into the Schema builder object being created.

The examples below show separate schema statements, but a single schema builder object can have multiple schema statements within one schema builder object's life cycle.

Top

Create Table

The above code would produce the following SQL:

Foreign Key Example

Here is an example of creating an additional user_info table that references the above users table with a foreign key:

The above code would produce the following SQL:

Top

Alter Table

which is the same as:

And would produce the following SQL:

Top

Drop Table

The above code would produce the following SQL:

Top

Execute Schema

You can execute the schema by using the execute() method within the schema builder object:

Top

Schema Builder API

In the above code samples, if you want to access the table object directly, you can like this:

Here's a list of common methods available with which to build your schema:

The following methods are shorthand methods for adding columns of various common types. Please note, if the selected column type isn't supported by the current database adapter, the column type is normalized to the closest type.

The following methods are all related to the creation of foreign key constraints and their relationships:

Top

Migrator

Database migrations are scripts that assist in implementing new changes to the database, as well rolling back any changes to a previous state. It works by storing a directory of migration class files and keeping track of the current state, or the last one that was processed. From that, you can write scripts to run the next migration state or rollback to the previous one. The state can be stored locally in the migration folder, or can be stored in its own table in the database. The pop-kettle component has this functionality built in to assist with managing database migrations for your application.

You can create a blank template migration class like this:

The code above will create a file that looks like migrations/20170225100742_my_new_migration.php and it will contain a blank class template:

From there, you can write your forward migration steps in the up() method, or your rollback steps in the down() method. Here's an example that creates a table when stepped forward, and drops that table when rolled back:

To step forward, you would call the migrator like this:

The above code would have created the table users with the defined columns. To roll back the migration, you would call the migrator like this:

And the above code here would have dropped the table users from the database.

Top

Seeder

Similar to migrations, you can create a database seed class to assist with populating some initial data into your database. This functionality is built into the pop-kettle component as well.

The code above will create a file that looks like seeds/20231105215257_my_first_seeder.php and it will contain a blank class template:

From there, you can write your seed queries steps in the run() method. You can interact with both the schema builder and the query builder.

Alternatively, you can use a plain SQL file as well and the seeder will parse it and execute the queries inside:

Either way, when you call the run() method on the seeder class, it will scan the folder for either seeder classes or SQL files and execute them:

Top

SQL Data

You can use the SQL data class to output large sets of data to a valid a SQL file.

The above example code would produce a users.sql file that contains:

If you have a larger set that you'd like divide out over fewer INSERT queries, you can set the divide parameter:

which would produce:

Top

Profiler

The profiler object works in conjunction with the pop-debug component to set up a query listener to monitor performance and record any potential issues.

With the debugger and query handler registered with the database profiler, any queries that are executed will get automatically logged with the debugger. The debugger log output from the above example might look like this:

If you'd like more control over when the debugger fires, you can manually save it as well:

In the above example, the query handler is returned from the listen() method call, which in turn can be registered with the stand-alone debugger. Once the final query runs on the user save() method, you can trigger the debugger save() method.

Top

All versions of pop-db with dependencies

PHP Build Version
Package Version
Requires php Version >=8.1.0
popphp/pop-debug Version ^2.0.0
popphp/pop-utils Version ^2.1.0
Composer command for our command line client (download client) This client runs in each environment. You don't need a specific PHP version etc. The first 20 API calls are free. Standard composer command

The package popphp/pop-db contains the following files

Loading the files please wait ....