Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

Christoph Rumpel:
GroupBy multiple levels in Laravel
Jan 29, 2018 @ 09:35:01

In a post to his site Christoph Rumpel shows the Laravel (well, Eloquent) users out there how to use groupBy at multiple levels for customizing and sorting the results of your database queries.

Since Laravel v5.5.29 you can group collections by multiple levels. Let's see what this means and how this works.

The post starts with some of the current functionality, showing how the groupBy could be used before with a simple collection to group student data by skill level. It then gets into the new functionality, modifying the example to group by both the skill level and by the "teacher" value. The result is a sorted set of results where the top level is the skill level and, inside of each, the teachers with students under that. The post finishes by "going crazy" and showing the grouping by event more criteria, adding "name" and "email" columns to the list.

tagged: groupby group database multiple level laravel eloquent tutorial

Link: https://christoph-rumpel.com/2018/01/groupby-multiple-levels-in-laravel

Geoff Wozniak:
What ORMs have taught me: just learn SQL
Dec 20, 2017 @ 13:51:49

Geoff Wozniak has written up a post on the "Curried lambda" site sharing his opinion on ORMs (object relational mappers) for working with databases and how, after using them in his own development work, that they're a good side benefit but shouldn't replace knowing SQL.

I've come to the conclusion that, for me, ORMs are more detriment than benefit. In short, they can be used to nicely augment working with SQL in a program, but they should not replace it.

[...] Neward, in his well known essay, lays out many cogent reasons why ORMs turn into quagmires. In my experience, I've had to deal directly with a fair number of them: entity identity issues, dual-schema problem, data retrieval mechanism concern, and the partial-object problem. I want to talk briefly about my experiences with these issues and add one of my own.

He breaks the rest of the article up into several sections, for each sharing some of his own experiences with the feature and how it could be resolved using other query methods:

  • Partial objects, attribute creep, and foreign keys
  • Data retrieval
  • Dual schema dangers
  • Identities
  • Transactions

He ends the post with a look forward, thinking about where he'll end up, mentioning stored procedures, queries as APIs and how "easy" isn't always best when it comes to ORMs.

tagged: orm mapper database layer sql opinion issues experience

Link: http://woz.posthaven.com/what-orms-have-taught-me-just-learn-sql

Romans Malinovskis:
Pragmatic approach to reinventing ORM
Dec 19, 2017 @ 13:19:57

Romans Malinovskis has a post on his Medium.com site sharing what he calls a "pragmatic approach to reinventing ORM" with his thoughts about a different approach to this commonly used database interface.

It’s been over a year, since I posted my first highly debated post on Reddit: Reinventing the faulty ORM concept. Gathered information helped me design and implement an alternative pattern to ORM with many important advantages and that has reinforced my belief that ORM is faulty.

He's broken the article up into a few sections with details and thoughts for each along the way:

  • Why ORM is important?
  • Why ORM is broken?
  • How Agile Data differ in approach?
  • How Agile Data qualify production/enterprise use?
  • Notes on architectural decisions in Agile Data (OOP vs Decoupling)

That final section focuses mostly on the decoupling aspect and the Agile Data library (a Data Mapper) that can be used and solves some of the common ORM problems he mentioned in the earlier sections.

tagged: orm database interface datamapper agiledata broken opinion package

Link: https://medium.com/@romaninsh/pragmatic-approach-to-reinventing-orm-d9e1bdc336e3

Zend Framework Blog:
A new release of zend-db
Dec 07, 2017 @ 10:46:48

On the Zend Framework blog today Enrico Zimuel has posted an announcement about the zend-db component including a look at the new release and the future of the component.

Today, we released zend-db 2.9.0! This is our first new feature release in over 18 months, and contains 7 bug fixes, 6 new features, numerous unit test additions, and many documentation improvements.

zend-db is an important component of many PHP projects, and we know that its support is crucial for many people. As such, we allocated a number of weeks to triaging the various open issues and patches (more than 50) to ensure we would provide a stable release.

The post lists out the issues fixed and the features added, linking to the bugs that were files related to each. It then talks about the future of the component, noting that a v3.0 release is expected to come in 2018 and will include a lot of new features. This will help to make working with more modern features of the various databases easier directly from the code (without as much manual query creation or hackery). The post finishes with some thanks to those that contributed to this v2.9.0 release.

tagged: zendframework component zenddb database release future plan

Link: https://framework.zend.com/blog/2017-12-06-zend-db-2.9.0.html

SitePoint PHP Blog:
How to Optimize MySQL: Indexes, Slow Queries, Configuration
Oct 31, 2017 @ 10:46:11

On the SitePoint PHP blog Bruno Skvorc has a post that offers some helpful advice about optimizing your MySQL database through the use of indexes, monitoring slow queries and configuration options.

MySQL is still the world’s most popular relational database, and yet, it’s still the most unoptimized – many people leave it at default values, not bothering to investigate further. In this article, we’ll look at some MySQL optimization tips we’ve covered previously, and combine them with novelties that came out since.

He starts off with the configuration changes that can be used to optimize the database, tweaking settings for Innodb pool, handling variable inspection and using a tuning tool to determine the best settings. Next up comes the look at indexes covering the different kinds first: fulltext, descending, unique/primary and regular indexes. Finally he covers some of the usual bottlenecks seen in MySQL's use in web applications, showing how to monitor for them via the slow query log.

tagged: optimize mysql database application tutorial index slowquery query configuration

Link: https://www.sitepoint.com/optimize-mysql-indexes-slow-queries-configuration/

Ruslan Karimov:
Using custom types in Symfony & Doctrine
Sep 08, 2017 @ 11:30:26

Ruslan Karimov has written up a post for the 4xxi.com blog covering the use of custom types with Symfony and Doctrine to implement features specific to your database of choice.

ORMs are great. In theory, they insure you against potential changes in your RDBMS and offer an easy plug’n’play solution for integration your domain code with database. In practice, however, since ORMs have to be compatible with all major vendors, their possibilities are often limited. Perks and tricks of specific RDBMS are left out.

One of such perks is so-called range types in PostgreSQL. There are plenty of cases when range types are applicable, but people (in my experience) often stick to old solution with two separate DATETIME columns. Last time I asked a fellow developer about the reason behind it and they said: ‘I use Doctrine in my project, and Doctrine does not support it’. So here we are.

He starts with a brief introduction to a custom type in Doctrine and a ranged type in PostgreSQL. He then shows how to create a custom "DateReangeType" to work with the ranged types and how add it to your Symfony configuration as a custom type. He ends the post with an example of it in use in an entity and in the client code making the database request. Full code can be found in this Github repository.

tagged: custom types symfony doctrine tutorial database postgresql range type

Link: https://blog.4xxi.com/using-custom-types-in-symfony-doctrine-f865c7072757

Rob Allen:
Using PostgreSQL with PHP in Cloud Foundry
Sep 06, 2017 @ 09:45:10

Rob Allen has continued his look at running a PHP application on Bluemix via Cloud Foundry with a new post showing how to use a PostgreSQL database on the platform.

Having successfully deployed a PHP application to Cloud Foundry, I needed a PostgreSQL database for persistent storage. I found Lorna Mitchell's Connecting PHP to MySQL on Bluemix helpful and this article expands on that information.

I want to create a cloud-based PostgreSQL database and connect it to Laravel's Eloquent in a Cloud Foundry application. This is how to do it.

He starts off by showing how to create the database instance on the Cloud Foundry platform via their command-line tool. Next he shows how to bind the database service to the application instance and get the credentials you'll need to connect from your application. Finally he moves over to the PHP side and shows how to configure Eloquent to connect to the database service using these credentials and make a sample query.

tagged: tutorial database postgresql cloudfoundry bluemix series

Link: https://akrabat.com/using-postgresql-with-php-in-cloud-foundry/

SitePoint PHP Blog:
Introducing the Neo4j Symfony Bundle
Aug 07, 2017 @ 13:25:34

On the SitePoint PHP blog they've posted a tutorial from author Tobias Nyholm introducing the Neo4j bundle for Symfony-based application. This bundle provides functionality to work with Neo4j graph databases natively in the application.

There is no such thing as disconnected information, no matter where you look – people, events, places, things, documents, applications and the information about them is all heavily connected. As the volume of data grows, so does the number and dynamicity of its connections. And if you’ve tried in the past to store and query that highly connected, semi-structured data in any database, you probably experienced a lot of challenges.

The tutorial talks some about graph databases, how they work and what kind of data fits into them best. It also briefly covers the Cypher Query Language followed by an introduction to the bundle and what it has to offer. Code examples of putting the bundle to use for queries are provided including the models to handle the results, relationships and an example application you can use to start with a working example.

tagged: tutorial bundle symfony introduction neo4j database

Link: https://www.sitepoint.com/introducing-the-neo4j-symfony-bundle/

The Bakery:
Welcoming Phinx to the CakePHP family!
Jun 23, 2017 @ 09:54:02

On The Bakery (the CakePHP site) an official announcement has been posted welcoming Phinx to the CakePHP family. The Phinx library is a popular tool for framework-agnostic database migration handling.

We are very excited to announce that Phinx has joined the CakePHP team. The Github project has already been moved to the CakePHP organisation. The project itself will stay MIT-licensed but be gradually transformed into a Cake Software Foundation project. Other great news is that the current way to install and update Phinx remains unchanged.

As you are aware, CakePHP has been using Phinx since 3.0.0 for database migrations. The CakePHP Core team welcomes the opportunity to look after and maintain the project and will now start making changes to bring the code in line with the CakePHP (our) coding standards. As well as cleaning up issues and PR’s soon. We will be following up with our plans for the code and setting roadmaps in the coming weeks.

Rob Morgan, the original author of the library has also added some of his own commentary in a post to his site:

I’ve been busy lately. Juggling startups and open source work is no easy feat. I managed to do it for the past 5 years, but beyond 30 its proving to be more difficult. Phinx is not accelerating at the pace I’d like it to be. In fact so far this year we’ve only managed to ship 6 releases. I decided that the best strategy moving forwards is to find a new home for Phinx. One that has an active and loyal community and one that delivers great software. I’m pleased to announce that I’ve found the right fit.

He points out that the only real thing changing for now is the location of the repository. He looks back on the journey that got him and the project to where it is today and how much the support from the PHP community meant during that time.

tagged: cakephp phinx database migration robmorgan library project foundation

Link: https://bakery.cakephp.org/2017/06/23/welcoming-phinx-to-the-cakephp-family.html

SitePoint PHP Blog:
How to Search on Securely Encrypted Database Fields
Jun 02, 2017 @ 12:53:59

On the SitePoint PHP blog today they've reposted an article that was originally posted on the ParagonIE blog about searching encrypted information in database fields from author Scott Arciszewski.

This question shows up from time to time in open source encryption libraries’ bug trackers. This was one of the “weird problems” covered in my talk at B-Sides Orlando (titled Building Defensible Solutions to Weird Problems), and we’ve previously dedicated a small section to it in one of our white papers.

You know how to search database fields, but the question is, How do we securely encrypt database fields but still use these fields in search queries?

Our secure solution is rather straightforward, but the path between most teams asking that question and discovering our straightforward solution is fraught with peril: bad designs, academic research projects, misleading marketing, and poor threat modeling.

They start off with some of the examples of bad ways to perform the searching of encrypted information, mostly around either using poor encryption levels or custom created encryption solutions. With those out of the way, the tutorial moves on to their recommended method: using an authenticated encryption scheme (libsodium) and blind indexing. The key to the method is to use a secondary column for the actual searching process, encrypting the value provided and running the search against that, not the encrypted value itself. The article then covers two questions that need to be asked before putting this method to use. The article ends with a method to enhance the previous searching to allow for "fuzzier" searching through the generation of some additional index values in a joined table.

tagged: search security encryption database field tutorial libsodium

Link: https://www.sitepoint.com/how-to-search-on-securely-encrypted-database-fields/