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

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/

TutsPlus.com:
How to Program With Yii2: ActiveRecord
Mar 09, 2017 @ 12:07:53

In the latest tutorial in their "Programming with Yii2" series the TutsPlus.com site shows you how to work with the ActiveRecord functionality included with the framework.

In this Programming With Yii2 series, I'm guiding readers in use of the Yii2 Framework for PHP. In today's tutorial, I'll walk you through using Yii's object-relational mapping, known as ORM, for working with databases. It's called Active Record and is a key aspect of programming database applications efficiently in Yii.

Yii offers different ways to work with your database programmatically, such as direct queries and a query builder, but using Active Record offers a complete set of benefits for object-oriented database programming.

The article goes on from there and defines some of the basics around what Active Record is and how it works. It then starts on the code, showing how to create an ActiveRecord model and execute queries to:

  • locate single or multiple records
  • build queries
  • counting records
  • and accessing the records returned

They also talk about mass assignment, saving data via model instances, deleting records and creating relationships between the models.

tagged: yii2 framework series activerecord database tutorial introduction

Link: https://code.tutsplus.com/tutorials/how-to-program-with-yii2-active-record--cms-27434

TutsPlus.com:
Using Illuminate Database With Eloquent in Your PHP App Without Laravel
Jan 26, 2017 @ 09:51:05

The TutsPlus.com site has posted a new tutorial showing you how you can use the Eloquent ORM from Laravel without having to use Laravel itself. There's already functionality built into the popular database interface that makes it possible, it's just a matter of turning it on.

Illuminate is Laravel’s database engine minus Laravel. It comes bundled with the Eloquent ORM in Laravel. If you would like to build your PHP apps with ORMs and prefer not to use Laravel, this tutorial is for you.

In this tutorial, we are going to build the back end for a Q&A App with PHP, Illuminate Database, and the Eloquent ORM.

They start the post with a list of requirements and a brief outline of what functionality the end result will include. With that defined the tutorial starts in on the directory structure of the application and the Composer configuration to pull in Eloquent and its requirements. Next up is the code to define the connection credentials and (raw) SQL to create the tables in the database. The database "capsule" is created and the code is shared to create simple controllers and matching models for users, questions, answers and upvotes. They show how to use relationships to get the linked objects between tables and how to perform update and delete operations.

tagged: illuminate database eloquent laravel outside tutorial capsule

Link: https://code.tutsplus.com/tutorials/using-illuminate-database-with-eloquent-in-your-php-app-without-laravel--cms-27247

Toptal.com:
Don't Hate WordPress: 5 Common Biases Debunked
Dec 29, 2016 @ 12:10:30

On the Toptal.com site author Donald Mudenge has written up a post that wants to help debunk the top 5 WordPress myths that are still floating around about this popular and common tool.

In the early days, people used WordPress only as a blogging tool. However, today WordPress covers more than 50 percent of the market share for CMSs, supporting nearly 60 million websites worldwide.

As a commonly used platform for building websites and other online applications, misconceptions have spread like a forest fire, keeping people away from WordPress. In this article, I outline and explain the five most common WordPress taboos and myths, clarify them and offer solutions on how to overcome them.

The five myths he tries to dispel are:

  • WordPress is significantly more likely to be hacked.
  • WordPress is just blogging software.
  • WordPress professionals are designers.
  • WordPress isn’t an enterprise solution.
  • One WordPress requires one database.

For each item on the list he includes a brief summary of what's usually said about the myth and corrects it with his own description and links to other resources helping to prove his point.

tagged: wordpress myths debunk top5 common hacked blog enterprise database

Link: https://www.toptal.com/wordpress/debunking-wordpress-myths

Laravel News:
Quickly Dumping Laravel Queries
Nov 28, 2016 @ 12:49:36

The Laravel News site has a quick post today with a helpful hint for those trying to track down exactly what queries their Laravel application is performing on database calls. In this post they show two ways to get this information - one using the Debugbar and the other with a quick script.

When you are building an application and utilizing Eloquent it’s very easy to hit the N+1 problem. This is where you select a model and then later use lazy loading to fetch child records.

They give an example of selecting a user, grabbing it's list of "posts" and then looping through them causing queries to fire for each post individually. A fix for it is using the "with" method to pre-load them but determining this could be tricky. Enter their two solutions: the Laravel Debugbar package and this quick script shared by magkopian that listens for a "kernel.handled" event and outputs the results of the "getQueryLog" method with the Laravel "dd" helper.

tagged: laravel dump queries debugbar debugging sql database tip

Link: https://laravel-news.com/2016/11/quickly-dumping-laravel-queries/