• Development

    Rendering null boolean values with Grid4PHP

    I’m a big fan of Grid4PHP for adding a quick CRUD user interface to a MySQL database.

    I have a lot of boolean columns, like “is_telehealth” that need to start as NULL until we make a determination about the field’s value. Grid4PHP renders null booleans as “0” in the table/edit form. This is misleading, given it’s not actually a 0 value. If you add the ‘isnull’ attribute to the column it lets you save the null value, but it still renders as a “0”.

    Using a custom formatter I was able to render an empty string (nothing) instead of “0” for these null booleans:

  • Development

    Setting a Specific External Port for MySQL in Lando

    Lando sets an external MySQL port dynamically by default. This means every time you (re)start a lando app it gets (potentially) a different external MySQL port. This is an annoyance as you have to change your port in TablePlus, SequelPro, or whatever MySQL GUI you’re using.

    There is a simple fix: use a local lando file to override the forwarded MySQL port. These steps assume you already have a working .lando.yml file.

    Step 1: Create a local lando file: .lando.local.yml

    Step 2: Run lando rebuild if you’ve already started the site in the past

    Step 3: Verify the settings by running lando info

    Step 4: Add .lando.local.yml to your .gitignore  file

    This site will always get the port you specified if it’s available when you start the app. You can use this port in your MySQL gui, scripts, etc.

  • Development

    Setting a LIMIT on an UPDATE query in Drupal 8 / 9

    Here’s a quick tip regarding Drupal database manipulation. Specifically, I needed a way to flip a boolean value (from 1 to 0) on the first N rows in a database table that matched a specific set of conditions.

    The Problem

    It seems you cannot enforce a LIMIT on an UPDATE query using a static query in Drupal.

    Here’s what I was trying to do:

    This throws an error. I believe the issue is caused by the variable substitution wrapping the limit value in quotes. The error message starts with:

    If you run this query manually in MySQL, it works fine with LIMIT 10 but doesn’t work with LIMIT '10' (it throws the same error shown above).

    The Solution

    Given dynamic queries are favored over static queries, and that this static query doesn’t work, I ended up getting to the finish line with two dynamic queries.

    I have tested the solution on 10,000 records and it took < 6 seconds to process the entire request on my local dev lando site.

    “code” is a unique identifier column in the database table.

  • Development

    Snippet: MySQL table sizes ordered by largest to smallest (in MB and Row Count)

    Show All Tables By Size in MB

     

    Show All Tables By Size in MB (if > 1 MB)

     

    Show All Tables By Number of Rows

     

  • Development

    Using GROUP_CONCAT to Combine Rows in a Drupal Query

    Recently I was working on a D7 to D8 migration. I was trying to import news items and their taxonomy terms (among many other things). To make things simple I wanted the query results to have (for each node) a single field that contained a comma-separated list of taxonomy terms which I could then explode during the row processing. Using GROUP_CONCAT we can achieve this! Let’s break it down:

    The Drupal 7 site has the following structure (focusing on the important bits for this blog post):

    The migration relies on the d7_node migrate source plugin, which basically queries for nodes of a specific type. The query object looks like this (simplified for this blog post):

  • Tech Tips

    Sorting ActiveCollab tasks

    Our company has been using ActiveCollab for years. It’s a great project management tool and has improved considerably over the years. We have managed hundreds of projects and more than 12,000 tasks using ActiveCollab. Occasionally we want a little more from the system than it offers out of the box. I’ve built an importer (shameless plug!) to make it simple to add tasks in bulk. I’ve written some automated task creation scripts. I’ve even tried my hand at writing a 3rd party integration module. Sometimes, though, you just need a simple tweak to tailor the system to your needs. That’s what this post is about.

  • Development

    Proximity by City or Zip Code in Drupal 6 with Location and Views

    The location module for Drupal 6 is a robust module. On most projects, it gives us 100 percent of what we need. Proximity searching based on zip code is built-in and fairly painless to setup. I’ve recently been tasked to also allow proximity searching based on City. As you might imagine, the request is very similar. Both methods require the use of latitude and longitude (decimals). The difference is that instead of querying the database for latitude and longitude based on zip code (it’s usually termed postal code in Views, Location, etc.), we’re asking for the coordinates of a city. You’ll find that many cities have multiple zip codes, each of which is a row in the location module’s zipcodes database table. In this example, I’m not giving a real honest attempt at solving this issue, but rather I just return the first coordinate that matches the city.

  • Development

    db_query() and db_placeholders() example #1

    Have you ever wondered how to properly build a query like this in Drupal:

    This requires the use of db_placeholders() to create the placeholder ‘ ‘, ‘ ‘, etc.

    where $node_types is an array of node types.

  • Development

    Views Taxonomy: Get Terms with Associated Nodes

    This example serves as both an example of how to alter a Views2 query, as well as how to use the get_terms_by_count() function I’ve written.

    Unfortunately there is not (at present) a Views2 taxonomy filter that lets you “Get only terms with at least X associated nodes.” We had a client request that terms without associated nodes be hidden. This was actually more complex than it sounds, but the solution led me to a whole new level of Views2 understanding. Views2 has a hook called hook_views_query_alter() that lets you alter a Views2 query before it is executed. This is exactly what we needed to do in order to only pull terms with associated nodes. Specifically, we needed to add an additional WHERE clause to the query.