Getting Started with FluentPDO

Say goodbye to boring SQL query! Simplify database operations with FluentPDO

Are you tired of writing SQL queries too? Especially when time is tight. If you are like me, today we will learn a very cool tool: FluentPDO. If you are not familiar with the term "PDO", don't worry. It is a very simple concept: in the PHP world, PDO stands for Persistent Data Object, which helps you abstract some basic database operations (such as insertion, update, delete, etc.). It is a layer of abstraction between you and the database.

What's the result? No more need to write SQL queries! This may not be the first such project you have ever seen: there are many similar projects on the market, each with its key features. The key feature of FluentPDO is its powerful JOIN query builder.

Key Points

• FluentPDO is a PHP SQL query builder that abstracts basic database operations without writing SQL queries. Its key feature is a powerful JOIN query builder.
• The installation of FluentPDO is done through Composer without any additional configuration. Instantiate the PDO object by passing it as a parameter to the constructor of the FluentPDO object.
• FluentPDO provides simple and easy-to-read syntax for basic SELECT operations, including methods to set tables, filter results, and specify comparison operators. It also allows selecting specific fields, setting LIMIT and OFFSET parameters, and using the HAVING, GROUP BY, and ORDER BY instructions.
• FluentPDO also supports data operations using INSERT, UPDATE, and DELETE operation classes. These operations require the execute() method to run the query and change the database.
• Advanced features of FluentPDO include the JOIN query builder (the code simplifies JOIN query) and a built-in debugger system (for testing and checking queries).

Our FluentPDO Test Project

First, we need a sample project to do it. Let's think about it...how about a simple multi-user wish list?

There will be many users, and each user has their own favorite product. For each user, we will store their first name, last name and registration date. For each item, we will store its name, brand, price and related user ID.

I will use a simple MySQL database. Here is our data structure:

The following is a SQL dump (including some dummy data):

CREATE TABLE IF NOT EXISTS items (   
        id int(11) NOT NULL AUTO_INCREMENT,   
        name varchar(100) NOT NULL,   
        brand varchar(100) NOT NULL,   
        price decimal(10,2) NOT NULL,   
        user_id int(10) unsigned NOT NULL,   
        PRIMARY KEY (id),   
        KEY user_id (user_id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

    INSERT INTO items (id, name, brand, price, user_id) 
    VALUES 
    (1, 'Last Awesome Phone', 'Awesome Brand', '550.00', 1), 
    (2, 'Last Awesome TV', 'Awesome Brand', '1200.00', 1), 
    (3, 'Fantastic E-Car', 'E-Cars Inc.', '80000.00', 2), 
    (4, 'Fantastic E-Bike', 'E-Bikes Co. Ltd.', '16000.00', 2);

    CREATE TABLE IF NOT EXISTS users (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,   
        first_name varchar(100) NOT NULL,   
        last_name varchar(100) NOT NULL,   
        signup_date datetime NOT NULL,   
        PRIMARY KEY (id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

    INSERT INTO users (id, first_name, last_name, signup_date) 
    VALUES 
    (1, 'Francesco', 'Malatesta', '2014-06-29 13:00:00'), 
    (2, 'John', 'Foo Bar', '2014-06-20 11:16:39');

    ALTER TABLE items   ADD CONSTRAINT items_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

Note: As you can easily imagine, this is not a "complete" project. We're just trying FluentPDO, so we won't cover things like login, registration, or application structure.

Installation

You can install FluentPDO using Composer and include it as a dependency:

"require": {
        ...
        "lichtner/fluentpdo": "dev-master"  
    }

When you're done, you need to instantiate it like this:

CREATE TABLE IF NOT EXISTS items (   
        id int(11) NOT NULL AUTO_INCREMENT,   
        name varchar(100) NOT NULL,   
        brand varchar(100) NOT NULL,   
        price decimal(10,2) NOT NULL,   
        user_id int(10) unsigned NOT NULL,   
        PRIMARY KEY (id),   
        KEY user_id (user_id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

    INSERT INTO items (id, name, brand, price, user_id) 
    VALUES 
    (1, 'Last Awesome Phone', 'Awesome Brand', '550.00', 1), 
    (2, 'Last Awesome TV', 'Awesome Brand', '1200.00', 1), 
    (3, 'Fantastic E-Car', 'E-Cars Inc.', '80000.00', 2), 
    (4, 'Fantastic E-Bike', 'E-Bikes Co. Ltd.', '16000.00', 2);

    CREATE TABLE IF NOT EXISTS users (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,   
        first_name varchar(100) NOT NULL,   
        last_name varchar(100) NOT NULL,   
        signup_date datetime NOT NULL,   
        PRIMARY KEY (id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

    INSERT INTO users (id, first_name, last_name, signup_date) 
    VALUES 
    (1, 'Francesco', 'Malatesta', '2014-06-29 13:00:00'), 
    (2, 'John', 'Foo Bar', '2014-06-20 11:16:39');

    ALTER TABLE items   ADD CONSTRAINT items_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

You must specify your connection details in the PDO constructor method. In the first parameter, type your database name after the dbname= section, and write your username and password as the second and third parameters, respectively.

You then pass the PDO object as a parameter to the constructor of the FluentPDO object.

That's it, FluentPDO doesn't need anything else to work. No additional configuration required.

Basic SELECT operation

We already have some virtual data. Let's start with "Hello World" of SQL query. A simple SELECT with a WHERE clause and the user primary key ID as a parameter to retrieve the basic information.

"require": {
        ...
        "lichtner/fluentpdo": "dev-master"  
    }

Nothing is difficult to understand here. FluentPDO has a good and easy-to-read syntax, so it's easy to understand what we're doing.

The

from() method is used to set the correct table. The where() method is used to filter our results with the same name clause. By default, in the where() method you just specify the field name and value. "=" is implicit. Of course, you can also use different comparison operators. In this case, you have to write them after the field name.

$pdo = new PDO("mysql:dbname=wishlist", "root", "password");
    $fpdo = new FluentPDO($pdo);

Getting the results is very easy: they are stored in the $query object we just used. You can iterate over it using a foreach loop as shown in the example.

In this specific case (searching the item by primary key ID), we can also use shortcuts in the from() method:

$user_id = 1;

    $query = $fpdo->from('users')->where('id', $user_id);

    foreach($query as $row){
        echo 'Hello, ' . $row['first_name'] . ' ' . $row['last_name'] . '!';    
    }

Let's see something more complicated than this.

Select a specific field

If you want, you can use the select() method after from() to select. You just need to use an array to tell FluentPDO which fields you want to select.

This is an example:

$fpdo->from('items')->where('price >', 1000);

LIMIT and OFFSET

Setting the LIMIT and OFFSET parameters to retrieve only a certain number of rows from the database is very easy. You can use limit() and offset() methods like this.

$query = fpdo->from('users', $user_id);

    // 将与...相同
    $query = $fpdo->from('users')->where('id', $user_id);

The only argument to these two methods is an integer that specifies the required value (for limit(), it is the number of items; for offset(), it is the number of items to be skipped).

HAVING, GROUP BY and ORDER BY

Methods are also provided for the "HAVING", "GROUP BY" and "ORDER BY" instructions.

Let's take a look at them with some examples.

ORDER BY

The

orderBy() method is used to sort the results according to specific conditions. Let's give an example: Here's how to sort the results by price (from cheap to expensive).

$query = $fpdo->from('users')->select(array('first_name', 'last_name'))->where('id', $user_id);

If you want to reverse the order (get the result from the most expensive to the cheapest), just add "DESC" after the selected column.

// 选择前十个结果...
    $query = $fpdo->from('users')->where('id', $user_id)->limit(10)->offset(0);

HAVING

The

having() method has very simple syntax. In the example below, we filter every item that costs less than $2,000.

$query = $fpdo->from('items')->orderBy('price');

Very simple.

You can use any comparison operator you want.

GROUP BY

Use the groupBy() method, you can group the results using specific fields as conditions. Here we display the quantity of products for each brand.

CREATE TABLE IF NOT EXISTS items (   
        id int(11) NOT NULL AUTO_INCREMENT,   
        name varchar(100) NOT NULL,   
        brand varchar(100) NOT NULL,   
        price decimal(10,2) NOT NULL,   
        user_id int(10) unsigned NOT NULL,   
        PRIMARY KEY (id),   
        KEY user_id (user_id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

    INSERT INTO items (id, name, brand, price, user_id) 
    VALUES 
    (1, 'Last Awesome Phone', 'Awesome Brand', '550.00', 1), 
    (2, 'Last Awesome TV', 'Awesome Brand', '1200.00', 1), 
    (3, 'Fantastic E-Car', 'E-Cars Inc.', '80000.00', 2), 
    (4, 'Fantastic E-Bike', 'E-Bikes Co. Ltd.', '16000.00', 2);

    CREATE TABLE IF NOT EXISTS users (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,   
        first_name varchar(100) NOT NULL,   
        last_name varchar(100) NOT NULL,   
        signup_date datetime NOT NULL,   
        PRIMARY KEY (id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

    INSERT INTO users (id, first_name, last_name, signup_date) 
    VALUES 
    (1, 'Francesco', 'Malatesta', '2014-06-29 13:00:00'), 
    (2, 'John', 'Foo Bar', '2014-06-20 11:16:39');

    ALTER TABLE items   ADD CONSTRAINT items_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

Note: You can specify an alias for fields like in classic SQL.

Getting method

fetch

Using foreach is not the only way to get the result. What if we just want to retrieve the first result from the collection?

Simply use the fetch() method:

"require": {
        ...
        "lichtner/fluentpdo": "dev-master"  
    }

You can also get a single column by specifying its name as a parameter.

$pdo = new PDO("mysql:dbname=wishlist", "root", "password");
    $fpdo = new FluentPDO($pdo);

fetchPairs

Using fetchPairs() you can retrieve the results as an associative array. Use the following syntax:

$user_id = 1;

    $query = $fpdo->from('users')->where('id', $user_id);

    foreach($query as $row){
        echo 'Hello, ' . $row['first_name'] . ' ' . $row['last_name'] . '!';    
    }

You will get the following output:

$fpdo->from('items')->where('price >', 1000);

This is an example using a user-unique ID and name.

$query = fpdo->from('users', $user_id);

    // 将与...相同
    $query = $fpdo->from('users')->where('id', $user_id);

fetchAll

The last but not least is the fetchAll() method.

The following is the syntax:

$query = $fpdo->from('users')->select(array('first_name', 'last_name'))->where('id', $user_id);

Using fetchAll() we have complete control over what we get from the result. The first parameter $index is the field used as the index, and $selectOnly is used to specify the field to be selected.

This is an example:

// 选择前十个结果...
    $query = $fpdo->from('users')->where('id', $user_id)->limit(10)->offset(0);

Note: The column used as the index (id in this case) is also included in the final array.

Okay, it's enough about SELECT operations. Let's take a look at other CRUD operations.

INSERT, UPDATE and DELETE

FluentPDO is not just about choosing something. It also has classes that manipulate data in a simple way.

Let's start with the INSERT operation.

INSERT

$query = $fpdo->from('items')->orderBy('price');

The

insertInto() method is used to specify the table to be used for the operation. You then have to assign the required values ​​using the values() method (in this case, they are stored in the $values ​​associative array).

The last step will be the execute() method, which will return the primary key of the new record.

You can also use this shortcut if you want:

$query = $fpdo->from('items')->orderBy('price DESC');

UPDATE

UPDATE method is very similar. Let's look at an example.

$query = $fpdo->from('items')->having('price <', 2000);

Use the set() method, you can specify a new value for the UPDATE operation.

Use the where() method, we filter the affected rows. There is also a shortcut, as mentioned earlier.

DELETE

DELETE operation is easier. Here is a quick example.

$query = $fpdo->from('items')->select('brand, COUNT(*) AS c')->groupBy('brand');

If you want to delete a record that knows its primary key, you can use the deleteFrom() shortcut above.

Note: As you can see from the example here, you have to use the execute() method to run the DELETE query. If you don't do this, you won't change anything in the database. The same is true for INSERT and UPDATE. Keep this in mind.

Advanced Features

As I told you before, these types of projects have their own unique features. FluentPDO is no exception: we will analyze two of these features: the JOIN query builder and the debugger.

JOIN query builder

is probably the most important unique feature of FluentPDO. The builder is very useful if you want to simplify your work and write less code. Let's see how to use it.

We will start with the "classic" JOIN query written using FluentPDO.

Similar to this:

$query = $fpdo->from('users');
    $row = $query->fetch();

    var_dump($row);
    // 将输出：
    // array(4) { ["id"]=> string(1) "1" ["first_name"]=> string(9) "Francesco" ["last_name"]=> string(9) "Malatesta" ["signup_date"]=> string(19) "2014-06-29 13:00:00" }

OK: We use classic syntax in the special leftJoin() method. not bad.

But we can do better. If you use conventions in a table structure, you can use this code:

CREATE TABLE IF NOT EXISTS items (   
        id int(11) NOT NULL AUTO_INCREMENT,   
        name varchar(100) NOT NULL,   
        brand varchar(100) NOT NULL,   
        price decimal(10,2) NOT NULL,   
        user_id int(10) unsigned NOT NULL,   
        PRIMARY KEY (id),   
        KEY user_id (user_id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

    INSERT INTO items (id, name, brand, price, user_id) 
    VALUES 
    (1, 'Last Awesome Phone', 'Awesome Brand', '550.00', 1), 
    (2, 'Last Awesome TV', 'Awesome Brand', '1200.00', 1), 
    (3, 'Fantastic E-Car', 'E-Cars Inc.', '80000.00', 2), 
    (4, 'Fantastic E-Bike', 'E-Bikes Co. Ltd.', '16000.00', 2);

    CREATE TABLE IF NOT EXISTS users (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,   
        first_name varchar(100) NOT NULL,   
        last_name varchar(100) NOT NULL,   
        signup_date datetime NOT NULL,   
        PRIMARY KEY (id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

    INSERT INTO users (id, first_name, last_name, signup_date) 
    VALUES 
    (1, 'Francesco', 'Malatesta', '2014-06-29 13:00:00'), 
    (2, 'John', 'Foo Bar', '2014-06-20 11:16:39');

    ALTER TABLE items   ADD CONSTRAINT items_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

Great, right? OK, fast is really cool…but what about smart?

Look here:

"require": {
        ...
        "lichtner/fluentpdo": "dev-master"  
    }

It got better.

In fact, FluentPDO understands what you want to do and automatically builds the query using data you provide in the select() method with the table.fieldname format string.

You can read the final build query for the last example here:

$pdo = new PDO("mysql:dbname=wishlist", "root", "password");
    $fpdo = new FluentPDO($pdo);

This does look good.

Of course, you can create an alias for the field if you want:

$user_id = 1;

    $query = $fpdo->from('users')->where('id', $user_id);

    foreach($query as $row){
        echo 'Hello, ' . $row['first_name'] . ' ' . $row['last_name'] . '!';    
    }

Debugger

FluentPDO comes with a built-in debugger system that you can use to test queries and check them.

It uses a simple closure system. If you want to use debugging, just place this code after connecting the code.

$fpdo->from('items')->where('price >', 1000);

You can customize the closure as you want, just remember to take the $BaseQuery object as a parameter.

$BaseQuery object is an instance of the BaseQuery class.

Conclusion

FluentPDO is a small and simple project. It definitely doesn't fit every project and can be improved - especially if it's been dormant for six months - but for small/medium-sized applications it can be a good option in case you don't want to Introduce large frameworks in the game. It is a good tradeoff due to some of its features, such as the JOIN query builder.

FAQs about Getting Started with FluentPDO (FAQs)

What is FluentPDO and why should I use it?

FluentPDO is a PHP SQL query builder that uses PDO. It provides a simple and easy-to-use interface for creating SQL queries, making it easier for developers to interact with databases. FluentPDO is especially useful for those who are not used to writing raw SQL queries or want to speed up the development process. It supports all SQL functions and provides a safe way to prevent SQL injection attacks.

How to install FluentPDO?

FluentPDO can be installed using Composer (PHP's dependency manager). You can install it by running the command composer require envms/fluentpdo. After running this command, Composer will download and install FluentPDO and its dependencies into your project.

How to connect to a database using FluentPDO?

To connect to the database using FluentPDO, you need to create a new instance of the FluentPDO class. You can do this by passing a PDO instance to the FluentPDO constructor. Here is an example:

$query = fpdo->from('users', $user_id);

    // 将与...相同
    $query = $fpdo->from('users')->where('id', $user_id);

How to execute SELECT query using FluentPDO?

FluentPDO provides a simple interface to perform SELECT queries. You can specify a table using the from method and specify a column using the select method. Here is an example:

$query = $fpdo->from('users')->select(array('first_name', 'last_name'))->where('id', $user_id);

How to execute INSERT query using FluentPDO?

To perform an INSERT query, you can specify the table using the insertInto method and specify the value using the values method. Here is an example:

CREATE TABLE IF NOT EXISTS items (   
        id int(11) NOT NULL AUTO_INCREMENT,   
        name varchar(100) NOT NULL,   
        brand varchar(100) NOT NULL,   
        price decimal(10,2) NOT NULL,   
        user_id int(10) unsigned NOT NULL,   
        PRIMARY KEY (id),   
        KEY user_id (user_id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

    INSERT INTO items (id, name, brand, price, user_id) 
    VALUES 
    (1, 'Last Awesome Phone', 'Awesome Brand', '550.00', 1), 
    (2, 'Last Awesome TV', 'Awesome Brand', '1200.00', 1), 
    (3, 'Fantastic E-Car', 'E-Cars Inc.', '80000.00', 2), 
    (4, 'Fantastic E-Bike', 'E-Bikes Co. Ltd.', '16000.00', 2);

    CREATE TABLE IF NOT EXISTS users (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,   
        first_name varchar(100) NOT NULL,   
        last_name varchar(100) NOT NULL,   
        signup_date datetime NOT NULL,   
        PRIMARY KEY (id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

    INSERT INTO users (id, first_name, last_name, signup_date) 
    VALUES 
    (1, 'Francesco', 'Malatesta', '2014-06-29 13:00:00'), 
    (2, 'John', 'Foo Bar', '2014-06-20 11:16:39');

    ALTER TABLE items   ADD CONSTRAINT items_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

How to execute UPDATE query using FluentPDO?

To perform a UPDATE query, you can specify the table using the update method, specify the new value using the set method, and specify the conditions using the where method. Here is an example:

"require": {
        ...
        "lichtner/fluentpdo": "dev-master"  
    }

How to perform DELETE query using FluentPDO?

To perform a DELETE query, you can specify the table using the deleteFrom method and specify the conditions using the where method. Here is an example:

$pdo = new PDO("mysql:dbname=wishlist", "root", "password");
    $fpdo = new FluentPDO($pdo);

How to deal with errors in FluentPDO?

When an error occurs, FluentPDO will throw an exception. You can use the try-catch block to catch these exceptions and handle them accordingly. Here is an example:

$user_id = 1;

    $query = $fpdo->from('users')->where('id', $user_id);

    foreach($query as $row){
        echo 'Hello, ' . $row['first_name'] . ' ' . $row['last_name'] . '!';    
    }

How to use transactions in FluentPDO?

FluentPDO provides methods to start, commit and roll back transactions. You can use beginTransaction, commit and rollBack methods respectively. Here is an example:

$fpdo->from('items')->where('price >', 1000);

How to use FluentPDO to join tables?

FluentPDO provides a simple interface to connect tables. You can use the join method to specify tables and conditions. Here is an example:

$query = fpdo->from('users', $user_id);

    // 将与...相同
    $query = $fpdo->from('users')->where('id', $user_id);

The above is the detailed content of Getting Started with FluentPDO. For more information, please follow other related articles on the PHP Chinese website!