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

Pineco.de:
Implementing Custom Logic With Raw SQL In Laravel’s Query Builder
Jul 04, 2018 @ 17:19:35

The Pineco.de site has a new tutorial posted showing you how to implement custom logic with raw SQL in the Eloquent query builder in the Laravel framework.

Laravel’s query builder offers a nice way to work with raw SQL. We can use them in our where conditions and also in our orderings as well. Let’s see some examples where we can use raw SQL to implement custom logic for ordering the results.

The post starts with a brief mention of the difference between sorting and ordering results, noting that one happens on the SQL server and the other on the results collection. Next they show examples using raw SQL to order a query using both a simple and more complex condition. There's also a link to the official documentation for the raw methods for more information and examples.

tagged: custom logic raw sql laravel query builder tutorial

Link: https://pineco.de/implementing-custom-logic-with-raw-sql-in-laravels-query-builder/

Geoff Wozniak:
What ORMs have taught me: just learn SQL
Dec 20, 2017 @ 19: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

Delicious Brains:
SQL Query Optimization for Faster Sites
Nov 07, 2017 @ 15: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 @ 15: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 @ 18: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 @ 16: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 @ 18: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 @ 17: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 @ 16: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 @ 15: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


Trending Topics: