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

SitePoint PHP Blog:
3 More Joins You Should Be Familiar With
Feb 08, 2016 @ 12:18:36

On the SitePoint PHP blog Zach Wallace shares more database wisdom with his readers introducing three more JOINs you should know in your development work.

There are many ways to JOIN data from two database tables and filter the information you require. Craig Buckler wrote a popular piece on understanding JOINs; namely INNER, LEFT, RIGHT, and FULL OUTER. This article is an extension of that one.

He starts with the data he'll be working with: customers and books, linked by a book_id column. He then quickly reviews some of the joins already discussed in the previous article (LEFT, RIGHT, OUTER, INNER) before getting into the newer, more powerful types:

  • LEFT JOIN with Exclusion
  • RIGHT JOIN with Exclusion
  • OUTER JOIN with Exclusions

He finishes the post with a few other thoughts about using WHERE clauses in JOINs, the CROSS JOIN and how the JOINs relate to each other in MySQL.

tagged: join database three list inner outer left exclusion advanced example tutorial

Link: http://www.sitepoint.com/3-more-joins-you-should-be-familiar-with/

Laravel News Podcast:
LN 08: Database Backups
Jan 28, 2016 @ 12:56:39

The Laravel News podcast with host Eric Barnes has posted their latest episode: Episode 8 - Database Backups.

Episode 8 of the Laravel News podcast is now out.

This episode features special guest Freek Van der Herten talking about backups, Test Driven Laravel, WordPress & Laravel, and PDF generation.

You can listen to this latest episode either by subscribing over on iTunes or pointing your feed reader at the RSS feed and grabbing the latest from there. If you're interested in listening to previous episodes, be sure to check out the podcast archive section on the Laravel News site.

tagged: laravelnews laravel podcast news ep8 ericbarnes database backup

Link: https://laravel-news.com/2016/01/ln-08-database-backups/

Paul Jones:
Atlas: a persistence-model data mapper
Dec 30, 2015 @ 09:48:50

Paul Jones has a new post to his site about a library he's worked up to provide "persistence-model data mapper" functionality for you to use in your PHP applications in accessing your database.

Atlas is a data mapper implementation for your persistence model (not your domain model).

As such, Atlas uses the term "record" to indicate that its objects are not domain entities. Note that an Atlas record is a passive record, not an active record; it is disconnected from the database. Use Atlas records indirectly to populate your domain entities, or directly for simple data source interactions.

The library is mostly an experiment on his part to create a tool that allows switching from the Active Record pattern to Data Mapper pattern for accessing your database without much hassle. The README on the library shows some of the basic usage of the tool, including the usual CRUD (create, read, write, execute) functionality.

tagged: atlas persistence model datamapper activerecord designpattern database library

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

Lorna Mitchell:
Insert Data with Phinx
Dec 18, 2015 @ 10:49:07

Lorna Mitchell has a new post to her site with a quick tip for any of the Phinx users out there around a new feature they've added: database seeding.

Database patching is a wicked hard problem, one that's got a bit easier in my world lately as I've been using Phinx on a few projects. [...] One thing I didn't immediately find was how to insert data. Phinx has seed functionality but in this case I needed to put in a lookup table to go along with a data structure change.

She includes a code examples of this new feature, showing how to create a roles table and seed it with a new record with a "name" value of "admin". She also mentions one "gotcha" in the name of the function used to save the data to the new table (saveData versus just save).

tagged: phinx insert data database migration example tutorial

Link: http://www.lornajane.net/posts/2015/insert-data-with-phinx

Kevin Schroeder:
Configuring MySQL SSL in Magento
Sep 28, 2015 @ 09:24:34

Kevin Schroeder has a quick post to his site showing the Magento users out there how to configure the SSL connection to their MySQL database backend.

’ve been asked a few times now if there is a way to use encrypted MySQL connections in Magento. [...] The answer, to my surprise, is that there is no way of doing it out of the box.

[...] All database configurations are stored in the local.xml file and the XML specification does not allow numbers for XML node names. So no matter how you try to slice it it looks like getting the SSL settings into the Magento adapter will not work without a code change. The Internet seems to confirm this. But that doesn’t mean that it can’t be done. So I wrote a quick Magento adapter that allows you to pass in the constant values.

He walks you through the process of getting the adapter installed, configuring MySQL to allow for the SSL connections and the configuration change to make. He includes the XML you'll need to update, including the addition of a secure_driver_options to the XML to provide the necessary SSL connection information.

tagged: magento mysql adapter ssl connection configure database

Link: http://www.eschrade.com/page/configuring-mysql-ssl-in-magento/

Fortrabbit.com:
Is your database password stored safely?
Sep 08, 2015 @ 11:48:18

The Fortrabbit blog has a post that want to help you store your database password securely and away from prying attacker eyes. While they use the example of a a database password, credentials for just about any other service could be protected the same way.

How do you protect your access data? Your sensitive secrets, basically anything your PHP application uses to authenticate or authorize with other services such as databases, caches, cloud storages, image resize services, transactional mail providers. All of them. Where do you put this — easily accessible while in development and secure for production?

They start by pointing out a few places where they should not be stored: in your code, in a version control system or in an environment variable (plain text). Instead, they suggest using a combination of a secret key that's configured in the application and encrypted versions of the values in environment variables. Some code is included showing how to set this up in a Laravel-based application, but the principle can be applied independent of the framework too with some other simple tools. They end the post with some links to other articles including a "considered harmful" piece reinforcing their methods.

tagged: credential protection password database tutorial encryption environment variable

Link: http://blog.fortrabbit.com/how-to-keep-a-secret

SitePoint PHP Blog:
Re-introducing PDO – the Right Way to Access Databases in PHP
Aug 25, 2015 @ 11:10:14

On the SitePoint PHP blog they have a post that "reintroduces PDO" or as they describe it, the "right way to access databases in PHP". The PDO functionality in PHP provides extra handling around database connections and queries as well as making it easier to connect to multiple types of databases with similar code.

PDO is the acronym of PHP Data Objects. As the name implies, this extension gives you the ability to interact with your database through objects. [...] PHP is rapidly growing, and it is moving toward becoming a better programming language. Usually, when this happens in a dynamic language, the language increases its strictness in order to allow programmers to write enterprise applications with peace of mind.

In case of PHP, better PHP means object-oriented PHP. This means the more you get to use objects, the better you can test your code, write reusable components, and, usually, increase your salary. Using PDO is the first step in making the database layer of your application object-oriented and reusable.

He starts by answering the question most ask about PDO versus mysql/mysqli by pointing out that PDO is more OOP friendly, it allows for parameter binding and the fact that the mysql extension is no longer supported. He shows how to check and ensure PDO is installed on your setup and, if not, how to add it in (for both linux and Windows systems). The tutorial then walks you through using PDO: making the connections to the server, running queries and returning the results. This includes a section on prepared statements and bound parameters and their benefits including SQL injection prevention.

tagged: pdo database access tutorial introduction prepared statements phpdataobjects

Link: http://www.sitepoint.com/re-introducing-pdo-the-right-way-to-access-databases-in-php/

Coding Geek:
How does a relational database work
Aug 19, 2015 @ 09:49:41

You may have been using relational databases in your PHP applications for a long time (PHP loves MySQL after all) but you might not have ever dug deep enough to understand how those databases work internally. In this detailed tutorial from Coding Geek they dive way in and cover everything from the basics out to complex sorting, management components and query handling.

When it comes to relational databases, I can’t help thinking that something is missing. They’re used everywhere. [...] you can google by yourself “how does a relational database work” to see how few results there are. [...] Are relational databases too old and too boring to be explained outside of university courses, research papers and books?

As a developer, I HATE using something I don’t understand. And, if databases have been used for 40 years, there must be a reason. [...] Over the years, I’ve spent hundreds of hours to really understand these weird black boxes I use every day. Relational Databases are very interesting because they’re based on useful and reusable concepts. If understanding a database interests you but you’ve never had the time or the will to dig into this wide subject, you should like this article.

He covers a wide range of topics during the post:

  • O(1)) vs O(n2) (or how data sets are handled based on size)
  • Array, Tree and Hash table
  • Global overview (structure of the database system and its pieces)
  • Query manager
  • Statistics (and optimizing storage of the data)
  • Data manager
  • Deadlock
  • Logging

Each of these topics comes with a lot of explanation, examples of how the internals are functioning as well as diagrams to help make a bit more sense. If you've ever really wanted to know how that database you use functions, this is definitely the article to check out.

tagged: relational database indepth concepts lowlevel highlevel query optimization transaction buffer

Link: http://coding-geek.com/how-databases-work/

Coding.bmail.net Blog:
Advanced logging system in PHP for careful developers
Aug 05, 2015 @ 12:19:51

On the Coding.bmail.net blog they've posted a guide to what they've called an advanced logging system in PHP for careful developers - essentially a logging system that's as "error proof" as possible and that works with as little user exposure as possible.

Being aware of all the activity and problems under the hood is essential when running big websites with lots of users, many features and, as it is usual in such cases, weak spots that must not be left untracked.

In order to be the first in knowing when errors or other events of interest happen we need a well designed logs manager. My code will provide such a feature, for PHP based websites.

They briefly outline how the complete setup will work, failing back to email if the database connection isn' available and logging based on environment. It also includes error levels and, on development only, a method for showing the errors being logged. While a good bit of this functionality could be handled by something like Monolog they do include some additional features like the email fallback, output of the errors in development mode and custom error/exception handlers.

tagged: logging advanced system custom database email environment tutorial

Link: http://coding.bmain.net/tutorials/php/advanced_logging_system_in_php_for_careful_developers

Michelangelo van Dam:
Speeding up database calls with PDO and iterators
Jul 27, 2015 @ 08:45:28

In a post to his site Michelangelo van Dam shows you how to speed up database calls with PDO and iterators in a "no framework" environment.

When you review lots of code, you often wonder why things were written the way they were. Especially when making expensive calls to a database, I still see things that could and should be improved.

When working with a framework, mostly these database calls are optimized for the developer and abstract the complex logic to improve and optimize the retrieval and usage of data. But then developers need to build something without a framework and end up using the basics of PHP in a sub-optimal way.

He points out some of the common issues with a simple approach using just PDO and simple arrays including performance issues. Instead he recommends the use of iterators that wrap a PDO connection and allow for much simpler fetching and iteration of the found results. He includes code examples for a base iterator instance and a way to extend it to get the customized results. He also includes a few benchmarks showing the difference between a foreach loop and this iterator method.

tagged: database pdo iterator foreach benchmark compare

Link: http://www.dragonbe.com/2015/07/speeding-up-database-calls-with-pdo-and.html