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

Delicious Brains:
SQL Query Optimization for Faster Sites
Nov 07, 2017 @ 09:53:47

On the Delicious Brains site they've posted a tutorial that provides some helpful tips and tricks for optimizing your SQL queries on your WordPress sites with the help of some basic techniques and software to analyze your current use.

With dynamic database-driven websites like WordPress, you might still have one problem on your hands: database queries slowing down your site.

In this post, I’ll take you through how to identify the queries causing bottlenecks, how to understand the problems with them, along with quick fixes and other approaches to speed things up. I’ll be using an actual query we recently tackled that was slowing things down on the customer portal of deliciousbrains.com.

They then walk through some of the methods for locating the queries that could be causing issues including the use of the QueryMonitor plugin and the MySQL slow query log. They provide some tips for understanding what makes the query slow and how to use the MySQL Workbench tool to determine the structure of the database and look for optimizations. Finally they get into some of the things they did to solve their own issue including adding caching, better understanding the need of the query and "thinking outside the box" with indexes and normalization.

tagged: sql query optimization performance mysqlworkbench slowquerylog mysql wordpress

Link: https://deliciousbrains.com/sql-query-optimization/

Mohamed Said:
Laravel/MySQL JSON documents faster lookup using generated columns
Aug 22, 2017 @ 10:55:05

Mohamed Said has an interesting post to his site showing how to use computed columns for faster lookups in a MySQL database containing JSON documents.

Laravel 5.3 is shipped with built-in support for updating and querying JSON type database fields, the support currently fully covers MySQL 5.7 JSON type fields updates and lookups.

He includes an example of using the Eloquent DB handling to search for the data in the JSON and the results. He links to more information about the feature, following it up with an example of the main topic: computed columns. In it he shows how to create (on the SQL side) a generated/computed column that allows for easier extraction of the data directly from the JSON. This makes it even simpler to get the data just as you would in a normal select. He also includes an example of creating it via the Schema functionality in the database migrations.

tagged: mysql json column lookup computed generated sql data laravel tutorial

Link: https://themsaid.com/laravel-mysql-json-colum-fast-lookup-20160709

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/

Remi Collet:
Microsoft SQL Server from PHP
Sep 23, 2016 @ 11:57:34

In this recent post to his site Remi Collet shows you how to set up your PHP installation to allow it to work with a Microsoft SQL Server as it's data store.

Here is a small comparison of the various solutions to use a [Microsoft SQL Server](https://en.wikipedia.org/wiki/Microsoft_SQL_Server) database from PHP, on Linux. All the tests have be run on Fedora 23 but should work on RHEL or CentOS version 7.

Several different extensions were tested as a part of making the connection to the SQL server:

  • Using PDO, ODBC and FreeTDS
  • Using PDO, mssql and FreeTDS
  • Using PDO, ODBC and Microsoft® ODBC Driver
  • Using the Microsoft® Driver
  • Using PDO and the Microsoft® Driver

Each item comes with a list of the requirements involved (other modules/extensions), examples of the configuration changes you'll need to make and some sample code to create the connection.

tagged: tutorial microsoft sql sqlserver database connection example testing

Link: http://blog.remirepo.net/post/2016/09/20/Microsoft-SQL-Server-from-PHP

Paul Jones:
SQL Schema Naming Conventions
Nov 04, 2015 @ 12:15:59

Paul Jones has a post to his site looking at SQL schema naming conventions and some of his own thoughts on the matter. There's a lot of different camps of thought around naming, much less database ones, and he makes a few suggestions learned from his experience over time.

Several weeks ago I asked on Twitter for SQL schema naming conventions from DBA professionals. (I’m always interested in the generally-accepted practices of related professions; when I can, I try to make my work as compatible with theirs as possible.)

I got back only a handful of responses, representing MySQL, PostgreSQL, and DB2 administrators, really not enough for a statistically useful sample. Even so, I’m going to present their anonymized responses here, because they led me to work I had not previously considered at length.

He asked about things like singular vs plural names, primary key choices and naming of association tables. The uses the rest of the post sharing the responses he got from his questions with a good range of responses representing both sides of each question. He wraps up the post looking at what these answers mean to the average developer and the answers that Joe Celko and Simon Holywell have to say on the matter.

tagged: sql schema naming convention feedback table primarykey association feedback

Link: http://paul-m-jones.com/archives/6188

Mastering Zend Framework:
Building and Executing SQL Queries In Zend
Apr 08, 2014 @ 12:18:17

The Mastering Zend Framework site (from Matthew Setter) has a new post today showing you how to execute SQL queries directly in a Zend Framework v2 application.

Whilst there are many ways for building and executing SQL queries in Zend Framework 2, the two that I usually use, and which are also used in the ZF2 manual, are closures and the selectWith function. I previously wrote a three part series, showing how to get started using the ZendDbSql classes with Zend Framework 2, but I didn’t cover how to actually run them. So in today’s tutorial, let’s do that.

He gives examples of these two methods starting with closures in a "tableGateway" select call. He shows how to add on parts of the query like "wheres" and an "order by" as well as some basic formatting. He then gets into the "selectWith" examples, showing the same criteria just added a different way. He also includes an example of the "tableGateway" objects used for the examples and how they're configured.

tagged: sql query zendframework2 execute query build tablegateway

Link: http://www.masterzendframework.com/php/building-and-executing-sql-queries-in-zend

MaltBlue.com:
Do We Use Magic Methods or Not?
Dec 13, 2013 @ 10:39:20

In the latest post to his MaltBlue.com site Matthew Setter takes a look at magic methods. He tries to answer a few basic questions about them - are they worth using and can you truly test effectively when they're in use.

As a freelance Zend Framework developer, I’m always looking to improve the quality of the applications I produce. So over the last 6 – 12 months, I’ve been learning as much as possible about testing. During this time, I’ve found the way I code’s dramatically changing (and improving). [...] In a recent development session, I attempted to test some of my ZendDb based classes, specifically the code which used the magic methods for dynamically building where clauses. [...] I can’t speak for what it’s like using PHPUnit’s mock objects, as I always use Mockery instead. But after attempting to do so in Mockery, I hit a stumbling block when trying to test the chained call.

His example is a call to "lessThanOrEqualTo" to create his where clause that makes use of the "__get" magic method to get and return "Where" object. After some research (and conversations on IRC) he started wondering if the magic methods were worth the trouble they may cause during testing. He references this post and lists several of the comments made about their use, most of them not in favor.

tagged: magic method zendframework sql builder query unittest testing

Link: http://www.maltblue.com/php/php-magic-methods-or-not

Google Cloud Platform Blog:
Google App Engine for PHP with PhpStorm
Oct 24, 2013 @ 10:09:05

On the Google Cloud Platform blog there's a recent post showing you how to directly integrate the Google App Engine PHP support with the popular PHP IDE PhpStorm for seamless development.

Our IDE for PHP, PhpStorm, provides seamless integration with Google App Engine for PHP - allowing you to locally develop, debug and deploy your PHP applications on Google App Engine. When testing your application locally, we also support full emulation of App Engine services through the App Engine Development server. The [introductory] video shows how to get started with Google App Engine for PHP in PhpStorm. We also have a comprehensive tutorial which covers Google App Engine with PhpStorm in detail.

His example shows how to integrate the IDE with the Google Cloud SQL service. He shows how to create a new user (via the API console) and how to connect that user in PhpStorm. He includes a CREATE statement for a sample table and the PHP code to connect.

tagged: phpstorm google appengine sql cloud tutorial integrate

Link: http://googlecloudplatform.blogspot.cz/2013/10/google-app-engine-for-php-with-phpstorm.html

Paul Jones:
Aura Has New Releases: Input, Sql, and View
Sep 18, 2013 @ 09:58:54

As Paul Jones mentions in his most recent post (pulled from the Aura blog), the Aura framework has some new releases of its component packages - specifically Input, Sql and View.

On the heels of last week’s slew of releases, we have three followups! The Aura.Input package got a feature-level bump to 1.1.0, with a new FormFactory. Thanks to Hari KT for championing that one. Aura.Sql is now at 1.3.0, due to lots of work from MAXakaWIZARD to provide SQLite- and PostgreSQL-specific query objects. Finally, the Aura.View package got a bugfix and is now at 1.2.1; it handles content-type negotiation better for those times when there is no Accept header.

If you'd like more information about the Aura framework, check out the project site or each of the packages that make it up. Aura is a decoupled set of components without additional dependencies.

tagged: aura framework release input sql view component dependency

Link: http://paul-m-jones.com/archives/4731

Chris Jones:
Using PHP and Oracle Database 12c Implicit Result Sets
Jul 26, 2013 @ 09:12:40

Chris Jones has a new post to his site showing you how to use Oracle 12c's implicit result sets in your code. Note: this functionality is still in development, so the naming/exact functionality might change.

The new Oracle Database 12c "Implicit Result Sets" (IRS) feature allows query results to be returned from a stored PL/SQL procedure (or a PL/SQL anonymous block) without requiring special PHP code. Support for IRS is available in PHP OCI8 2.0.0-devel extension when it is compiled and used with Oracle Database 12c. (OCI8 2.0 can be compiled and used with other versions of Oracle Database but the available feature set is reduced).

He shows a normal fetch loop that calls the oci_* functions and grabs each row with a oci_fetch_row call. He updates this to use an anonymous PL/SQL block (a string) instead that allows for more flexibility. He includes examples that fetch from one table, multiple tables and returns multiple result sets (that can be fetched one at a time) from the same single call.

tagged: implicit result set oracle 12c tutorial multiple single sql plsql

Link: https://blogs.oracle.com/opal/entry/using_php_oci8_2_0