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

TutsPlus.com:
Get Started With CRUD Operations in PHP MySQL Databases
Dec 07, 2018 @ 10:53:06

On the TutsPlus.com site they've posted a tutorial for those new to PHP and MySQL out there sharing the basics of CRUD operations using the built-in language functionality ("CRUD" stands for "Create, Read, Update, Delete").

In this article, we're going to explore how you could use a MySQL database to perform CRUD (create, read, update, and delete) operations with PHP. If you want to get your hands dirty with database connectivity in PHP, this article is a great starting point.

If you are just getting started with PHP, you probably realize that database connectivity is an essential feature that you'll need to get familiar with sooner or later. In most cases, a database is the backbone of any web application and holds the data of the application. So, as a PHP developer, you'll need to know how to deal with database operations.

In this article, we'll keep things simple and explore how to use the core mysqli functions. In upcoming articles of this series, we'll explore a couple of other ways to handle database connectivity.

They then walk you through some of the basics of:

  • creating the connection to the database
  • selecting information from the database
  • inserting and updating records
  • how to pull the record information
  • deleting records from the database tables

Each item on the list comes with plenty of explanation and example code to get you on the right path to learn these basic concepts.

tagged: crud operation tutorial mysql beginner create read update delete

Link: https://code.tutsplus.com/tutorials/how-to-work-with-mysql-in-php--cms-32222

Jason McCreary:
Installing Apache, PHP, and MySQL on macOS Mojave
Nov 30, 2018 @ 11:21:05

Jason McCreary has provided a tutorial on his site today for all of the OS X Mojave users out there about how to install Apache, PHP and MySQL on macOS with the latest operating system changes. This is an update of his previous post covering the same installation on OSX Sierra.

I am aware of the web server software available for macOS, notably MAMP, as well as package managers like brew. These get you started quickly. But they forego the learning experience and, as most developers report, can become difficult to manage.

The thing is macOS runs atop UNIX. So most UNIX software installs easily on macOS. Furthermore, Apache and PHP come preinstalled with macOS. To create a local web server, all you need to do is configure Apache and install MySQL.

He walks you through the full process to get everything up and running:

  • testing to ensure Apache can be started (it comes installed by default)
  • changing the configuration to enable PHP
  • making a phpinfo page in your DocumentRoot to test that PHP is working
  • downloading and installing MySQL
  • configuration changes required to connect PHP and MySQL

He also includes an example of additional changes such as enabling mod_rewrite and a link to a tutorial about setting up virtual hosts on your local machine.

tagged: installation osx mojave update tutorial mysql apache configuration

Link: https://jason.pureconcepts.net/2018/11/install-apache-php-mysql-mac-os-x-mojave/

Joe Ferguson:
Adding MySQL 8 support to Laravel Homestead
Nov 07, 2018 @ 12:57:21

In a new post to his site Joe Ferguson has included a screencast showing how to add MySQL 8 support to Laravel Homestead for your local development.

My friend Beau Simensen has been doing awesome stuff building and streaming his work on astrocasts.com. He’s inspired me to start streaming again and last night I spent some time adding a feature to Laravel Homestead to add MySQL 8 as an option.

In the video Joe walks through the whole process including how Homestead is set up (via Vagrant) and all of the configuration changes you'll need to make to get MySQL 8 support up and running. The video runs about an hour and a half but it's a great resource if you're looking to use this latest version of MySQL in your application.

tagged: screencast mysql8 mysql support homestead tutorial

Link: https://www.joeferguson.me/adding-mysql-8-support-to-laravel-homestead/

Stitcher.io:
Eloquent MySQL views
Aug 28, 2018 @ 10:38:54

On the Sticher.io blog Brent has written up a post covering the use of MySQL views in Eloquent, the database ORM that's included with the Laravel framework.

MySQL views are a way of storing queries on the database level, and producing virtual tables with them. In this post we'll look at why you want to use them and how they can be integrated in Laravel with Eloquent models.

If you're already convinced of the power of MySQL views, or just want to know how to implement them in Laravel, you're free to skip ahead.

For those not familiar with the concept of "views" in MySQL, he spends a little time explaining what they are and what benefits they bring to the table. This includes a code example of a migration to create one and how something similar could be achieved with event hooks on a Laravel model. He then gets into the use of the views with Laravel, refactoring a more complex SELECT query into a view and creating/removing it using the same migration methods as any other table in the database.

tagged: eloquent mysql view tutorial introduction database laravel

Link: https://stitcher.io/blog/eloquent-mysql-views

php[architect]:
MySQL Without The SQL - Oh My!
Jul 13, 2018 @ 10:29:17

On the php[architect] site they've posted a full article from their July 2018 issue sharing information about a new kind of data store in MySQL.

Do you work on projects where you begin coding before knowing what your data looks like? Or are you part of the vast majority of developers who have had little or no training in database theory, relational calculus, Structured Query Language, or sets? Could you be working on a project without a database administrator to set up relational tables, indexes, and schemas? Or are you tired of embedding ugly lines of SQL in your pristine PHP code? There is new hope for you.

In the article Dave Stokes (of MySQL/Oracle) covers the new native JSON data type that was added in MySQL 5.7 but enhanced in MySQL 8 as a document store. He shows how to use this new functionality from the command line, what a "document" is and how to install and use the X DevAPI PECL extension to make use of it from your PHP code.

tagged: mysql datastore json tutorial pecl extension

Link: https://www.phparch.com/2018/07/mysql-without-the-sql-oh-my/

Pineco.de:
Scheduling MySQL Backups with Laravel
May 28, 2018 @ 13:19:16

On Pineco.de they've posted a new tutorial showing you how to create a MySQL database backup system using Laravel and a command-line call to mysqldump wrapped in an Artisan command.

A few days ago, I signed in to the wrong database and deleted around 18 000 records that were in use. Not to mention, we had no backup for that data. So after all, I decided to make a little script to dump the database and save it to an SQL file automatically.

But, if you are here for a fully working backup system, you should take a look at <a href="https://github.com/spatie/laravel-backup>this package. We won’t cover more just scheduling database exports and its scheduling.

The post starts with the command that will be executed on the command-line and the code required to "warm up" the console and run the command. He shares the code for this command and walks through it, explaining each part. Next comes the scheduling of the task and, as a bonus, setting up the scheduler to run on the Forge service.

tagged: schedule mysql backup laravel mysqldump tutorial

Link: https://pineco.de/scheduling-mysql-backups-with-laravel/

Sameer Borate:
How to profile and debug MySQL queries for speed
Apr 02, 2018 @ 10:40:59

On his Code Diesel site Sameer Borate has a quick post sharing a helpful tool that can make profiling the SQL queries your application makes simpler. In this post he shows how to use the Neor Profile SQL profiler desktop application (compatible with Windows, OSX and Linux) to hook into your SQL flow and evaluate the queries in real time.

Most MySQL applications need profiling for speed enhancements at one time or other. I’ve developed several web applications in the past wherein MySQL queries were a major bottleneck in the application performance. In the past however there were no easy tools to analyse SQL queries in a running application and we programmers had to depend on some makeshift solutions to debug SQL queries. Now, however there are some nice free tools which can help you profile MySQL application queries easily.

My favorite among them is Neor Profile SQL profiler. This is a desktop tool which allows you to easily profile your application SQL queries and also lets you query the same in a live application.

He starts with the changes to make to your configuration to point to the profiler for interception. This is then hooked into the MySQL database and queries will be profiled in real-time. The tool provides information like time of execution, number of rows returned, a graph showing the queries by type and includes the ability to run (or re-run) custom queries.

tagged: mysql database profile speed performance

Link: https://www.codediesel.com/mysql/how-to-profile-and-debug-mysql-queries-for-speed/

Pineco.de:
Basic Eloquent Search Techniques
Mar 28, 2018 @ 12:36:01

The Pineco.de blog has a new tutorial posted that the Laravel users out there will find particularly useful. In their latest post, they introduce some basic Eloquent searching techniques that can be used to easily locate data via currently available Eloquent functionality.

When our app is smaller – for example at the beginning – it’s enough to apply small, handmade solutions. It’s true for database searching as well. It’s not necessary to pull in a package instantly. Most of the time we can use some simple techniques to perform searches in Eloquent.

[...] Searching is a vital part of any application. A good interface helps the user to retrieve the information it needs. So it’s essential to bring a good solution both on front-end and back-end. In this post, we are not covering any UI or front-end related topics. [...] So for now, let’s talk about MySQL and Eloquent only.

In the post, they share a few methods for searching data that don't require any additional service or package. They're mostly just taking advantage of features the MySQL database supports but they're showing how to use them in a Laravel environment:

  • simple "where" clauses
  • using the fuzzy matching of the "like" keyword
  • searching JSON columns by a "path"
  • using "sounds like" to find similar values

Each item on the list comes with a few lines of code showing how to use it via an Eloquent model and a brief explanation of what's happening behind the scenes in the database.

tagged: laravel eloquent search where like json soundslike tutorial mysql database

Link: https://pineco.de/basic-eloquent-search-techniques/

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/

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/