Split char-separated values in MySQL

Introduction Recently I needed to extract a list of URLs from a MySQL table. All those URLs were inserted in a single TEXT column, however they were all separated by semicolons. The situation was more or less like the following: SELECT * FROM links --------------------------------------------------------------- | id | urls | --------------------------------------------------------------- | 1 | http://first.link;http://second.link | | 2 | https://third.link | | 3 | | | 4 | http://fourth.link;http://fifth.link;https://sixth.link| | ...| ... | I wanted to get those URLs with a single query, without using any programming language. Luckily for me I could make a few assumptions to make my work a lot easier: The column contained only urls or empty strings There were a maximum of 5 urls for each row Diggin in MySQL does not include a function to split a delimited string. You can define a function yourself for this purpose. However I didn’t have the required...

Read More
Feature Flags in ProcessWire

Introduction While building a website or an application, it’s often useful to control which parts are visibile (and which are not) to the actual users. Maybe you just want to hide a particular functionality on which you are still working, or maybe you just want to reveal things gradually without deploying new code in production. This concept is called Feature flags or Feature toggles. Feature Flag is a technique to turn some functionality of your application off, via configuration, without deploying new code. The concept of feature flags is explained really well in this article by Martin Fowler, which also explains why you shouldn’t do feature flags in the first place: Your first choice should be to break the feature down so you can safely introduce parts of the feature into the product. With this concept in mind let’s switch to my actual use case. Recently I had the need...

Read More
Using ProcessWire InputFieldDateTime via API

Introduction Recently I had the need to create a custom form in the backend, using the API and InputField modules that ProcessWire provides. Think InputFields as the basic building blocks to create forms in ProcessWire. They are used all over the administration panel, and provide styling (via jQuery UI), validation ad error rendering (through the FormBuilder) out of the box. Here is an example of how you can use the API to create a basic form. The code is taken from this PW forum thread written by Soma. It’s pretty straightforward, just follow the comments. <?php // the variable that holds the form output $out = ''; // create a new form field (also field wrapper) $form = $modules->get("InputfieldForm"); $form->action = "./"; $form->method = "POST"; $form->attr("id+name",'subscribe-form'); // the previous function ("id+name") is the same as: // $field->attr('id', 'subscribe-form'); // $field->attr('name', 'subscribe-form'); // create a text input $field = $modules->get("InputfieldText"); $field->label...

Read More
Conditional validation on Laravel's Form Request

Introduction In my opinion, Laravel Form Requests are one of the most useful components of Laravel. They can perform authorization and validation on requests, without even touching the controller. Form Requests are also very flexible, because they are completely customizable by method overriding. When creating a Form Request, the first method to implement is the authorization() method, which can be used to check if the authenticated user can perform the action. For example the following code checks through gates if the user is allowed to update a post. public function authorize() { $post = Post::find($this->route('id')); return $post && $this->user()->can('update', $post); } The code $this->route('id') retrieves the URI parameter named id, defined in our route file as Route::put('post/{id}'). If you don’t need authorization just return true. The second method to define, is of course the one that adds the validation rules and it’s called (as you may probably guess) rules(). public...

Read More
Confirm form submission without loops using jQuery

Introduction This post falls in the category “Short tricks that I would teach to my younger self”. Let’s assume that you want to display a confirmation dialog before submitting an important form. You can leverage the Window.confirm() method, which displays a modal dialog with two buttons, Ok and cancel. Window.confirm() returns true if the user clicks on the Ok button. <form onsubmit="return confirm('Do you really want to submit the form?');"> You can also implement this using jQuery: $(form).on('submit', function() { if(confirm('Do you really want to submit the form?')) { return true; } return false; }); Or more succint: $(form).on('submit', function() { return confirm('Do you really want to submit the form?'); }); This simply works out of the box because the confirm() method blocks the event loop, therefore the form is not submitted until the user clicks one of the two choices. Now, let’s say that we want to use a...

Read More
Check for foreign key existence in migrations

Laravel provides a couple of handy features to check for the existence of a table or column using the hasTable and hasColumn methods: <?php if (Schema::hasTable('users')) { // } if (Schema::hasColumn('users', 'email')) { // } However, sometimes you need to check the existence of foreign keys or indexes on tables, for example when you need to rename columns, in order to avoid annoying errors. Unfortunately Laravel doesn’t provide such methods out of the box, but you can easily leverage the doctrine/dbal library in order to get the current state of columns. Notice: You will need to add the doctrine/dbal dependency to your composer.json file and run the composer update command in your terminal to install the library. After that, you can easily get the Doctrine SchemaManager instance in your migration files, which provides additional abstraction methods on top of SQL. For example, you can easily build a function that returns...

Read More