Insert Into Database or Return ID of Duplicate Row in MySQL

Techonologies Used: SQL, MySQL, PHP

Note: The analysis and results contained herein applies only to MySQL databases. Other RDBMSs may have similar syntax to which the results of this investigation may be translated, but the tests described below should be repeated prior to use outside of MySQL.

Introduction

A fairly common need when storing data in a relational database is one of duplicate control. A normalized database will not contain the same data multiple times, a constraint which helps keep both the database’s file size, as well as query times, down. Maintaining this constraint, however, can be a headache. Applying the constraint on the database requires the application to monitor inserts to ensure they succeeded properly, the alternative to which is querying for possible duplicates before every insert. This can make writes to the database cumbersome, and worse, potentially slow. And to top it all off, this is compounded by a frequent need to then reference either the newly inserted row, or the duplicate row if one existed.

While all that sounds like it would take at least three queries to perform, there are various tricks to boil the functionality down to one or two queries. Indeed, the act of either inserting a new record or locating an existing duplicate, and then returning the id of the row inserted or found has a surprisingly large number of ways of being accomplished. Here I explore four such methods and analyze their performance. Read the rest of this entry »

Using PHP’s preg_replace() to Replace one String / Pattern with an Array of Replacements

Techonologies Used: PHP

Introduction

PHP has a few wonderful functions in str_replace() and its regular expression cousin preg_replace(). The applications for these functions are innumerable, ranging from stripping punctuation out of a string or replacing double quotes with single quotes all the way up to converting a string from XML to JSON formatting. For all their use though, there is a huge hole in their functionality; the ability to replace a single string (or regular expression match) with a series of replacement values. Here I will present a way to have that functionality simply without the overhead of looping structures.

Examples

Example 1:

Replace the ‘a’s in “abracadabra” with all the vowels in order:
Original: abracadabra
Altered: abrecidobru
$result = preg_replace(array_fill(0, 5, "/a/"), array("a", "e", "i", "o", "u"), "abracadabra", 1);

Example 2:

Replace the question marks in a query with values (mimic prepared statements):
Original: SELECT * FROM users WHERE gender=? AND age>? AND state=?
Altered: SELECT * FROM users WHERE gender=‘f’ AND age>23 AND state=‘OR’
$result = preg_replace(array_fill(0, 3, "/\?/"), array("'f'", "23", "'OR'"), "SELECT * FROM users WHERE gender=? AND age>? AND state=?", 1);

Example 3:

Replace the asterisks in a list with numbers in order:
Original: The first amendment guarantees freedoms of *) religion, *) speech, *) press, *) petition and assembly, and *) association.
Altered: The first amendment guarantees freedoms of 1) religion, 2) speech, 3) press, 4) petition and assembly, and 5) association.
$result = preg_replace(array_fill(0, 5, "/\*/"), range(1, 5), "The first amendment guarantees freedoms of *) religion, *) speech, *) press, *) petition and assembly, and *) association.", 1);

Prepared Statements

The goal here, replacing the occurrences of a repeated substring with a series of values in an array, is one commonly performed in the context of prepared statements. If you aren’t familiar with prepared statements, you might want to take a look at the php.net page on the topic. In short, the idea is that you build a string with wildcards either in the form of question marks (regex matched on \?) or named placeholders (regex matched on :\w+). These placeholders can then be easily replaced with the actual values later on. This replacement is handled one placeholder/value pair at a time using the functions stored in the PDO class. Replacing the placeholders one at a time can be clunky though, and if you aren’t actually using the prepared statement, including and instantiating the PDO class is a waste.

Working Around str_replace

The php.net page on str_replace() goes into great depth on how to use arrays for the arguments, in all the myriad combinations of relative their relative lengths. Unfortunately, all it has to say about the combination of use here (one search value, an array of replace values) is an insistence that “the converse would not make sense, though,” and the documentation for preg_replace() says even less. This won’t do at all. Thankfully, not all is lost, and even if the *_replace() functions don’t specifically support multiple replacement values for a single search value, that behavior can be coerced by feeding in some slightly unusual parameters.

Both functions will replace placeholder/value pairs one to one if the first two arguments are arrays of equal length. The trick to replacing only one search value (which may appear multiple times) is to provide an array filled with that one search string/regex multiple times — once for each replacement value. Of course, little is gained if such an array has to be built element by element in a forloop, but php offers a solution here in the form of the array_fill() function which allows the easy creation of an array with the same length of the replacements array, filled completely with the placeholder. This only gets halfway to the goal, however, as the *_replace() functions’ default behavior is to replace all instances of the search argument with the replacement, not the one for one replacement desired. This isn’t something one can get around with the str_replace() function, but the preg_replace() function does support an optional 4th parameter which limits the number of replacements per match. While it is true that preg_replace() is a a little slower than str_replace() for replacing plain substrings, the difference is a small enough cost for the extra functionality it provides.

The code

Putting the pieces together finally gives:

$replacements = array("value_1", "value_2", "value_3", "etc."); $search_pattern = "/\?/"; $search_array = array_fill(0, sizeof($replacements), $search_pattern); $result = preg_replace($search_array, $replacements, $subject, 1); or if you prefer it all condensed down into a single (more compact but somewhat less readable) line: $result = preg_replace(array_fill(0, sizeof($replacements = array("value_1", "value_2", "value_3", "etc.")), "/\?/"), $replacements, $subject, 1); or as a function takes the same arguments and works the same as preg_replace(), but runs the code above in the case of a single search pattern and an array of replacements: function preg_replace_plus($search_pattern, $replacement, $subject, $limit=1, &$count=null) { if (is_array($replacement) && !is_array($search_pattern)) { //run alternate code for one pattern and multiple replacements $result = preg_replace(array_fill(0, sizeof($replacement), $search_pattern), $replacement, $subject, 1, $count); } else { //otherwise just pass arguments through to preg_replace() $result = preg_replace($search_pattern, $replacements, $subject, $limit, $count); } return $result; }

The search string in the above can of course be replaced with whatever your placeholder might be, though since this only works through the preg_replace() function, literal search strings still need to be expressed as regular expressions (escaping the regex meta-characters and adding the necessary delimiters).

Transposing Arrays in PHP – A Quick Way to Loop Through Columns from a SQL Query

Techonologies Used: PHP, SQL

Introduction

Retrieving a set of rows from a database and looping through them may well be the single most common task performed by a web based application. Sometimes, however, it is more natural to loop though the columns of a query, or maybe to pick out just one column from a multi-column result. Sure you could loop through each row, putting the cell from the matching column in a new array, or even make another query which returns only the column in which you are interested, but both of those solutions require putting a few extra lines of code that serve no purpose other than to get data you already have into a different form. Beyond any annoyance this might cause when writing, the verbosity of those options can also impair the readability of the code.

Wouldn’t it be so much easier if there were a way to just use the foreach construct to loop through the columns of an array rather than the rows? Read the rest of this entry »

Get a Percentile from SQL Query

Techonologies Used: SQL

Introduction

Note: Throughout this article, MySQL syntax will be used due it its extreme popularity with web-based applications. However, the concepts discussed herein are not limited solely to the MySQL RDBMS, and the code provided is easily altered to whatever RDBMS you may be using.

Most SQL RDBMSs have a lot of extremely useful functions built in for analyzing the data within. Getting the count, sum, average, or even standard deviation of column in a result set is straight forward. Sometimes you need a less standard function, and that’s when you get the chance to write your own query. This post will show you how to write a query to get the percentile of a value, and to do so in a way that won’t cause the query to take forever to run.

A First Attempt

At first glance, the percentile of a query seems particularly easy to retrieve from a SQL database. The percentile is simple the number of records with the value in question or lower, divided by the total number of records. Going from such a definition might lead you to write a query that looks a lot like the following:

Read the rest of this entry »

Easy jQuery Tooltips

Technologies Used: HTML, jQuery, and CSS

Live Example

The icon after this line in a tool-tip! Hover over it see the tool-tip message.This is the text of the image tool-tip.

This entire line is in a span tag which has a tool-tip when you hover over it.And this is the text that appears when you hover over the paragraph.

Introduction

A common need in designing a clean user interface for a website is to be able provide the user (sometimes long) explanatory text without actually cluttering up the page with it. In the early days of the web, this was most commonly done by the addition of short links which would open a new window. As the web matured, these popups (which were inherently not user friendly as they required a page load and and a separate click to close) were gradually replaced with a much better solution — the tool-tip.

Read the rest of this entry »