I tend to prefer a GUI like Sequel Pro for looking at database data, but since Sequel Pro doesn’t support MySQL 8+ and I haven’t settled on an alternative, I found myself needing to use the CLI. It also can just be faster to use for new or rarely accessed servers or whatever, and is nice and lightweight. However, it wraps query output by default, and with many or wide columns, it can become very hard to read and figure out which data is in which column. Recently, I went looking for something better, and found a StackOverflow question with a couple ways: outputting vertically, and using a pager with a nowrap option set.
Continue reading post "Readable query output in MySQL CLI"mysql posts
MySQL: DELETE with sub-query on same table
I had an issue with a MySQL query containing a sub-query recently where it worked fine when done as a SELECT query, but gave an error when switching it to a DELETE query. The error given was something like ‘You can’t specify target table “items” for update in FROM clause’. The sub-query was referencing the same table as the main query, which apparently can’t be done directly in MySQL because the table will be modified during deletion. But there is a sort of a hack I found in this StackOverflow answer, among others, to force it to create a temp table and allow it to work.
Simple MySQL backup script
I made a simple bash backup script for the newer MySQL database servers at Cogneato.
I’ve just learned that, with the MySQL command line client, one can run \s or status; and get a nice readout of status information.
Automatically reconnect PDO when connection times out
In PHP scripts that run queries to a MySQL database over a long period of time, the connection may time out and give a “MySQL server has gone away” error.
Continue reading post "Automatically reconnect PDO when connection times out"Import SQL file in Doctrine / PDO
I recently needed to programmatically import SQL files containing database dumps into a database managed by Doctrine ORM.
Continue reading post "Import SQL file in Doctrine / PDO"Stearns: WordPress Custom Queries
For the Stearns site, we need to list upcoming events on the home page. Using Flutter, I created a custom write panel for the events (and other items). The events are simply posts that have a custom date field attached to them.
I was attempting to use the “query_posts()” function to get the posts I need. I discovered that it is possible to use this function multiple times on a single page. I previously thought you were unable to because of “the loop”, but you only have to make a few accommodations for the page name and other such WordPress variables getting changed. I was able to use this to output the page data on our home page plus two categories of posts.
Unfortunately, “query_posts()” allows limiting by category and sorting by a custom key, but no less than, greater than, or other such comparisons with the meta key [wrong, see end of post]. So I decided to make my own SQL query, to be run with the “$wp_db->get_results()” function. The function allows a straight SQL query to be run. Then some other functions are used to put the result set into “the loop”. So, the code to run my custom query looks like the following:
Continue reading post "Stearns: WordPress Custom Queries"