@daharveyjr

Architect, Developer, Web Geek!

February 17, 2015
by daharveyjr
0 comments

Portfolio Project: MRP Design Engineering

MRP Design Engineering

About the Project

I recently completed work on a new project for MRP Design Engineering. We took their existing website, built using the SnippetMaster web editor, and migrated it to the WordPress platform with a new look and feel. Doing this allows them to easily control and manage their own content, which is an option they really didn’t have before. In addition, the website features an updated and improved user interface, enriched content across across the services offered to clients, a new navigation layout that is well-defined and structured, and resolves a few outstanding bugs that limited their client interactions on the old website.

View Portfolio Page | Visit MRP Design Engineering

December 22, 2014
by daharveyjr
0 comments

cat_mysql

MySQL REGEXP (Regular Expression) Operators

MySQL offers the ability to use regular expressions to perform complex searches against your data. A regular expression is a tool that provides for a concise and flexible way to identify strings of text based on user-defined patterns.

This article will discuss the MySQL regular expression operators, review their use and syntax, and identify the constructs and special characters that can be used in a MySQL regular expression, as well as provide a few examples of their use.

MySQL Regular Expression Operators

The following operators are used in MySQL to perform regular expression operations. These are used in a WHERE clause similar to the well-known and often used LIKE operator.

  • REGEXP: The pattern matching operator for using regular expressions.
  • NOT REGEXP: The negation of the REGEXP operator.
  • RLIKE: A synonym for the REGEXP operator.

MySQL Regular Expression Syntax

The basic syntax used for MySQL regular expression operations is:

-- For the REGEXP Operator
SELECT {COLUMN_NAME} FROM {TABLE_NAME} WHERE {COLUMN_NAME} REGEXP '{REGEXP_PATTERN}';

-- For the NOT REGEXP Operator
SELECT {COLUMN_NAME} FROM {TABLE_NAME} WHERE {COLUMN_NAME} NOT REGEXP '{REGEXP_PATTERN}';

-- For the RLIKE Alias Operator
SELECT {COLUMN_NAME} FROM {TABLE_NAME} WHERE {COLUMN_NAME} RLIKE '{REGEXP_PATTERN}';

To provide more detailed, yet simple, example of a MySQL regular expression operation, take the following statement. It will retrieve all the columns of each record in the table PRICE where the PRICELIST_ID matches the pattern specified (starts with the numeric range 0-9 occurring one or more times, followed by an ‘_’ (underscore), and then the character sequence ‘USD’.

SELECT * FROM PRICE WHERE PRICELIST_ID REGEXP '^[0-9]+_USD';

Another example of a MySQL regular expression operation, can be shown in the following statement. It will retrieve all columns of each record from the PRICE table where the PRICE_ID matches the pattern specified (starts with an O, followed by and ‘_’ (underscore), then the numeric range 0-9 occurring one or more times, followed by and ‘_’ (underscore), then ending with either the character sequence USD, or BRA.

SELECT * FROM PRICE WHERE PRICE_ID REGEXP '^O_[0-9]+_[USD|BRA]';

MySQL REGEXP Constructs and Special Characters

A MySQL regular expression may use any of the following constructs and special characters to construct a pattern for use with the REGEXP operators. The construct or special character is shown, followed by a description of each and what operations in performs within the pattern for the regular expression.

  • ^ : Match the beginning of a string.
  • $ : Match the end of a string.
  • . : Match any character (including carriage return and newline characters).
  • a* : Match any sequence of zero or more a characters.
  • a+ : Match any sequence of one or more a characters.
  • a? : Match either zero or one a characters.
  • de|abc : Match either of the character sequences, de or abc.
  • (abc)* : Match zero or more instances of the character sequence abc.
  • {1},{2,3} : Provides a more general way of writing regular expressions that match many occurences of the previous atom (or “piece”) of the pattern. i.e. a? can be written as a{0,1}.
  • [a-dX],[^a-dX] : Matches any character that is (or is not, if ^ is used) either a, b, c, d, or X. A “-” character between two other characters forms a range that maches all characters from the first character to the second.
  • [.characters.] : Within a bracket expression (using “[” and “]”), matches the sequence of characters of that collating element. i.e. the pattern [[.period.]] would match the ‘.’ (period) character.
  • [=character_class=] : Within a bracket expression, represents an equivalence class. It matches all characters with the same collation value, including itself.
  • [:character_class:] : Within a bracket expression, represents a character class that matches all characters belonging to that class. i.e. the pattern [[:alpha:]] would match against a string that is all aphabetic characters.
  • [[:<:]],[[:>:]] : These markers stand for word boundaries, and as such they match the beginning and ending of words, respectively.

* NOTE: MySQL interprets the “\” (backslash) character as an escape character. If you choose to use the “\” character as part of your pattern in a regular expression it will need to escaped with another backslash “\\”.

For further documentation on the MySQL regular expression operator, please visit Regular Expressions in the MySQL Reference Manual (v5.1 currently linked).

December 15, 2014
by daharveyjr
0 comments

cat_magento

Listing Subcategories on a Category Page in Magento

I recently was working on a Magento Commerce website for a client that wanted to display all of the subcategories of a category on the category page, to improve basic navigation, and to further enhance and show the range of products that they carried.

The following code snippet was used to pull the child categories from the category object on the category page, and retrieve the necessary information for display. It retrieves the child categories from the category object (in the form of a comma-delimited string) using the getChildren() method, explodes them into an array, and iterates over that array, retrieving the category object for each subcategory ID identified. If the subcategory is marked as an acive category, the category name, thumbnail, and URL are then retrieved and displayed on the page as needed.

NOTE: The client’s HTML structure and code has been removed from the snippet; however, the code snippet still provides an appropriate example to retrieve and display child categories.

<?php
$_subcategories = $_category->getChildren();
if ($_subcategories && trim($_subcategories) != '') {
	$_subcategory_ids = explode(',', $_subcategories);
	?>
	foreach($_subcategory_ids as $_subcategory_id): ?>
		<?php $_subcategory = Mage::getModel('catalog/category')->load($_subcategory_id); ?>
		if ($_subcategory->getIsActive()): ?>
			<?php if ($_subcategory->getThumbnail()): ?>
				<a href="<?php echo $_subcategory->getUrl(); ?>"><img src="<?php echo $_subcategory->getThumbnail(); ?>" /></a>
			<?php endif; ?>
			<a href="<?php echo $_subcategory->getUrl(); ?>"><?php echo $_subcategory->getName(); ?></a>
		<?php
		endif;
		?>
	<?php 
	endforeach; 
	?>
<?php 
}
?>

September 8, 2014
by daharveyjr
0 comments

cat_db2

DB2 CONCAT (Concatenate) Function

The DB2 CONCAT function will combine two separate expressions to form a single string expression. It can leverage database fields, or explicitly defined strings as one or both expression when concatenating the values together.

The syntax for the CONCAT function is shown here:

-- Using Database Fields
SELECT CONCAT({field_1}, {field_2}) FROM {table_name};

-- Using String Expressions
SELECT CONCAT('{string_1}', '{string_2}') FROM {table_name};

-- Using Database Fields and String Expressions
SELECT CONCAT({field_1}, '{string_2}') FROM {table_name};

To show an example statement in use:

SELECT CONCAT(MFNAME, MFPARTNUMBER) FROM CATENTRY;

The SQL statement above will take and concatenate the values from the MFNAME and MFPARTNUMBER columns in the CATENTRY table.

If we want to add an explicit string (or space) between the two columns, we could leverage the following SQL statement to do so. This query will add a blank space between the two defined expressions.

SELECT CONCAT(CONCAT(MFNAME, ' '), MFPARTNUMBER) FROM CATENTRY;

NOTE: There is a shortcut notation available; however, I’ve found that if DB2 is not configured to enable it you will receive errors should you attempt to use it.

To leverage the CONCAT function shortcut, you’ll leverage a ‘||’ (double pipe) notation, and will result in an SQL query with the following syntax and will produce the same result as the second example query above:

SELECT MFNAME || ' ' || MFPARTNUMBER FROM CATENTRY;

If you want to read the IBM DB2 documentation on the CONCAT scalar function, please visit the DB2 v9.7 Information Center: CONCAT scalar function.

September 1, 2014
by daharveyjr
0 comments

cat_db2

DB2 SUBSTR (Substring) Function

The DB2 SUBSTR function returns a substring of a string. The SUBSTR function takes two arguments (source and start location) with an optional third argument (length).

The syntax for the SUBSTR function is shown here:

-- With Required Parameters
SELECT SUBSTR({field_name}, {start_location});

-- With Optional {string_length} Parameter
SELECT SUBSTR({field_name}, {start_location}, {string_length});

To show an example statement in use:

SELECT SUBSTR(NAME, 5) AS NAME_SUBSTR FROM CATENTDESC;

The above query will take and substring the NAME column in the CATENTDESC table starting with the 5th character, and returning a resulting substring the length of which is defined by the remaining length of the column or source string.

To limit and define the length of the substring returned we can add in the length parameter to the SUBSTR function.

SELECT SUBSTR(NAME, 5, 15) AS NAME_SUBSTR FROM CATENTDESC;

The above query will take and substring the NAME column in the CATENTDESC table starting with the 5th character, and returning a resulting substring that is 15 characters in length.

NOTE: There are a few important considerations to make regarding the DB2 SUBSTR function:

  1. The start location must be an integer between 1 and the length or maximum length of the string, depending upon whether the string is fixed or varying-length. If the start location is out of range it will return a SQLSTATE 22011.
  2. If a length is explicitly specified, the source string is effectively padded on the right with the necessary number of blank characters so that the specified substring of string always exists.