@daharveyjr

Architect, Developer, Web Geek!

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

August 8, 2014
by daharveyjr
1 Comment

cat_wordpress

Restore a WordPress Database to Localhost

Working with WordPress, I often will take a production database and restore it to my local server to verify changes and further aid in development. When doing so, I will always run WordPress under a named virtual host, with frequent production backups allowing me to mimic the production environment.

This article will discuss the steps necessary to backup and restore a production MySQL database to a local development environment, and configure it to run against a local named virtual host or URL.

NOTE: This post assumes that a localhost web server and MySQL instance has been setup, and that the web site code exists locally, and the localhost WordPress web site can be accessed.

Step 1. Backup the Production WordPress Database

Begin by backing up the production WordPress database. To backup a production MySQL database to a file from a production server, execute the following command on the command line to generate an SQL file that can be restored on your local development environment.

Replace the following variables within the command shown below, as needed for the production environment:

  • {user} : A MySQL user that with appropriate privileges for the database being backed up.
  • {database_name} : The MySQL database name being backed up.
  • {current_date} : The current date, in YYYYMMDD format. It is recommended to post-fix the database backups with the current date in the YYYYMMDD format, allowing the storage of multiple database backup files.
mysqldump -u {user} -p {database_name} > ~/{database_name}_{current_date}.sql

2. Create the WordPress Database on Localhost

The mysqldump command contained no options to drop and create the database that will restored. This was done purposefully, so if the database already exists, or when a user is created and granted all privileges, that work doesn’t need to be redone on any subsequent restore of the WordPress database.

If you find that you need to create the database on localhost, execute the following SQL statement, replacing the necessary variables:

  • {database_name} : The MySQL database name being created.
CREATE DATABASE {database_name};

3. Restore the WordPress Database on Localhost

To restore the MySQL database locally, download the MySQL backup to the local machine, and then restore it executing the command shown below, replacing the necessary variables in the command as needed for the localhost environment:

  • {user} : A MySQL user that has appropriate access to create and insert data into the database.
  • {database_name} : The MySQL database name just created.
  • {backup_filename} : The absolute path to the MySQL backup file downloaded previously.
mysql -u {user} -p {database_name} < {backup_filename}.sql

4. Create the Local MySQL User and Grant All Privileges

If web site code was downloaded directly from the production server and to avoid having to modify the WordPress configuration file to update the connection information, create the MySQL username and password used on the production server locally and assign it the privileges necessary.

Execute the following SQL statements replacing the variables as identified below to create the new MySQL user and to grant all privileges:

  • {user} : The MySQL user to be created.
  • {password} : The MySQL password for the user being created.
  • {database_name} : The MySQL database name the user is being granted privileges to.
CREATE USER '{user}'@'localhost' IDENTIFIED BY '{password}';

GRANT ALL PRIVILEGES ON {database_name}.* TO '{user}'@'localhost' WITH GRANT OPTION;

5. Modify the WordPress home and siteurl Options

Once the backup has been restored, execute the following SQL statement against the WordPress database to ensure that the URL values recorded in the wp_options table point to a named virtual host or localhost URL, replacing the variables as identified below within the SQL statement shown:

  • {localhost_url} : The local named virtual host or localhost URL used to access the restored WordPress web site.
UPDATE wp_options SET option_value = 'http://{localhost_url}/' WHERE option_name IN ('home', 'siteurl');

6. Verify Restored Database

Verify the restored database by accessing the WordPress web site at the localhost URL specified in the wp_options. Browse around the various pages and posts, and verify that a successful login can be made, and that a redirect is not issued to the production web site, this will confirm the wp_options were successfully set, and the localhost environment is now properly restored with the WordPress database.

August 5, 2014
by daharveyjr
0 comments

cat_magento

Magento Newsletter Subscribers Import Script

I was recently helping a client setup and configure their Magento newsletter module, and worked to help them get a large part of their subscriber base imported into Magento from external sources. To accomplish this, I created a newsletter subscribers import script for Magento that helped them accomplish a quick import of their subscriber base that existed outside of their Magento installation.

This script can handle one-off additions, as well as take a file upload, the format being one email address per line. It will also add the emails to the newsletter without sending a confirmation email that they’ve subscribed.

To use the subscribers script, you’ll need to change one thing, the require_once import path to point to your Magento installation’s Mage.php file.

I would also recommend that you place the file within a sub-directory in your current Magento installation, such as "newsletters", to allow you to easily access it for use (i.e. mydomain.com/newsletters/import_subscribers.php).

You can find the Magento newsletter subscribers import script below:

<?php

set_time_limit(180);

require_once "/path/to/magento/installation/app/Mage.php";

Mage::app();

?>

<h3>Subscribe Methods:</h3>

<p><strong>Method:</strong> TXT File</p>

<p>Please upload a text (.txt) file containing one (1) email address per line.</p>

<form action="" method="post" enctype="multipart/form-data">
	<label for="file">Filename:</label>
	<input type="hidden" name="subscribe_method" value="txt_file" />
	<input type="file" name="file" id="file">
	<input type="submit" name="submit" value="Submit">
</form>

<p>- or -</p>

<p><strong>Method:</strong> Individual Email</p>

<form action="" method="post">
	<label for="">Email:</label>
	<input type="hidden" name="subscribe_method" value="individual_email" />
	<input type="text" name="email" id="email" value="" />
	<input type="submit" name="submit" value="Submit" size="60" />
</form>

<?php 

if (isset($_POST['submit']) ){
	
	?>
	<h3>Subscribe Results:</h3>
	<?php
	
    if ($_POST['subscribe_method'] == 'individual_email') {

    	$email = $_POST['email'];
    	Mage::getModel('newsletter/subscriber')->setImportMode(true)->subscribe($email);

    	echo $email . ' has been subscribed<br/>';
        
    } else if ($_POST['subscribe_method'] == 'txt_file') {

    	if ($_FILES["file"]["error"] == 0) {

    		// Open File Ref
    		$handle = fopen($_FILES["file"]["tmp_name"], 'r');

    		if ($handle) {
    			while (($email = fgets($handle, 4096)) !== false) {

    				$email = trim($email);
			        Mage::getModel('newsletter/subscriber')->setImportMode(true)->subscribe($email);
			        
			        echo $email . ' has been subscribed<br/>';
			        
    			}
    		}

    	}
    	
    }

} 

?>

April 11, 2014
by daharveyjr
0 comments

cat_magento

How to Setup the Magento Cron Job

I’ve encountered way too many Magento installations that were never configured to execute the Magento cron job, ultimately causing confusion and problems. This post is meant to be a tutorial to help people enable and setup the cron job to execute as required by Magento, as well as answer some questions as to why the Magento cron job should be enabled.

What Is a Cron Job?

A cron job is an automated event that is triggered by the server. Cron jobs are used to run periodic maintenance or scheduled activities.

What Does Magento Use Its Cron Job For?

By default, the cron job in Magento is used to handle activities that require a script to be run periodically. These activities include, but are not limited to:

  • Catalog Price Rules
  • Sending Newsletters
  • Generating Google Sitemaps
  • Customer Alerts/Notifications
  • Updating Currency Rates
  • Scheduled Log Cleanup

Cron Job Configuration Methods

There are several different ways to configure the Magento cron job to execute, and a quick Google search will show a plethora of different methods. I’ve shown two below that work on the majority of Linux/Unix-based web hosts I have encountered.

Configuration 1. A Magento Cron Job Using WGET

*/15 * * * * wget -q http://www.mydomainname.com/cron.php

Configuration 2. A Magento Cron Job Using Command Line PHP

*/15 * * * * /local/path/to/php -f /local/path/to/magento/cron.php

I generally recommend that most installations setup the Magento cron job to utilize the wget method, due to its simplicity; however, either method works and is generally accepted as correct.

Creating the Cron Job Configuration

You can configure either method above to execute using the crontab or control panel of your favorite web host (i.e. cPanel). The instructions necessary to bring up the crontab and/or edit the crontab through cPanel are shown below.

Method 1. Using Crontab

First, you will need to open the crontab editor, which is used to maintain and edit your cron job entries.

To open the cron tab as the user you are currently logged in as type:

crontab -e

Or, if you wish to open the crontab editor as a specific user, you’ll need to type the following command, replacing username with the specific username you wish to use:

crontab -u username -e

Once the crontab editor is open, you’ll simply type out (or copy and paste) one of commands above, leveraging either the wget or command line PHP method. Save the file and exit to complete your cron job configuration. Note: Each cron job entry needs to occupy its own line within the crontab file. Failure to do so will result in less than desirable results, and a multitude of failures!

Method 2. Using cPanel

Step 1. Under the “Advanced” section of your cPanel control panel, you’ll find the “Cron jobs” item. Click on this to enter the cron job configuration screens.

cpanel_advanced_cron_jobs

Step 2. Find the form section named “Add New Cron Job”, and fill out the necessary settings for the Magento cron job.

I recommend the following configuration, which is configured to run every 15 minutes, every day. I see a lot of configurations out there that recommend running the cron job every 5 minutes; however, I’ve found that 15 tends to be a little less resource intensive for those that are running on shared hosting platforms. If you have a dedicated server, feel free to configure it to run every 5 minutes.

  • Minute: */15 (Every 15 minutes)
  • Hour: * (Every hour)
  • Day: * (Every day)
  • Month: * (Every month)
  • Weekday: * (Every weekday)
  • Command: wget -q http://www.mydomainname.com/cron.php

Your screen should look similar to the following when completed:

cpanel_cron_jobs_add_new_cron_job

Step 3. Click “Add New Cron Job” to save the cron job configuration.

NOTE: More information on the jobs that are configured to run in Magento by default can be found on the Magento Wiki, found here).