@daharveyjr

Architect, Developer, Web Geek!

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.

August 18, 2014
by daharveyjr
0 comments

cat_db2

DB2 COALESCE Function

The DB2 COALESCE function returns the value of the first non-NULL expression. The COALESCE function takes a comma separated list of arguments which are evaluated in the order in which they are specified and returns the first non-NULL value found.

The syntax for the COALESCE function is shown here:

-- NUMERIC EXAMPLE
SELECT COALESCE({field1}, {default_value}) FROM {table_name};
-- CHARACTER EXAMPLE
SELECT COALESCE({field1}, '{default_value}') FROM {table_name};

To show an example statement in use:

SELECT COALESCE(MFPARTNUMBER, PARTNUMBER) AS PART_NUM FROM CATENTRY;

The above query will first try to retrieve the MFPARTNUMBER column value from the CATENTRY table. If the MFPARTNUMBER value is NULL, then it will attempt to retrieve the PARTNUMBER column value from the CATENTRY table. If both columns are NULL, the COALESCE function will return NULL.

Now if we want to ensure we never get a NULL value to return, we can manipulate the above query as shown below to provide a default value in the instance that both MFPARTNUMBER and PARTNUMBER columns are NULL.

SELECT COALESCE(MFPARTNUMBER, PARTNUMBER, 'UNKNOWN') AS PART_NUM FROM CATENTRY;

The above query will return the value ‘UNKNOWN’ if the values in both the MFPARTNUMBER and PARTNUMBER columns are NULL.

NOTE: The COALESCE function is also supported on many other database platforms; however, this post was typed strictly to DB2.

August 11, 2014
by daharveyjr
0 comments

cat_mysql

MySQL Find and Replace (REPLACE)

Every had need to do a MySQL find and replace to quickly replace some data in your database? Fear not, MySQL has a nifty little string function, REPLACE, that allows a user to find and replace instances of a search string with a substitution string. The REPLACE function performs a case-sensitive search of a field replacing all instances of the search string found with the substitution string.

The following variables are referenced in the example REPLACE function below:

  • {table_name} : The table name to update.
  • {field_name} : The field name to update.
  • {search_string} : The search string that will be replaced when matched.
  • {substitution_string} : The substitution string that will be replace the search string when a match is found.
UPDATE {table_name} SET {field_name} = REPLACE({field_name}, '{search_string}', '{substitution_string}');

For more information on the MySQL REPLACE function, or to view the MySQL 5.0 documentation on REPLACE, please visit: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace