Tim Thorp Training & Consulting

The 20 Minute WordPress Installation and Configuration

So, every time I follow the famous 5 minute WordPress Installation Guide, I do a few things differently. These extra steps make the installation take around 20 minutes (if I rush), but I am writing this note, primarily for myself to document my process and also as a guide for those who like doing things in the way I do them. Since the wordpress installation guide is so handy and thoughtful in its presentation, I am not trying to recreate it. This post is meant to supplement the instructions in the wordpress installation guide.

1. Login to your web hosting server and create a database and assign user permissions. This is usually done in cpanel
2. Download the latest package at http://wordpress.org/download/ and unzip
3. Copy wp-config-sample.php as wp-config.php and edit the first lines 3-8 so that the database connection can be made. Get the secret key for lines 13-15 at http://api.wordpress.org/secret-key/1.1/
4. Optional Step: add a theme to wp-content/themes (this can be done at a later time too, but I like doing it at the beginning). Note: the theme’s name is defined in its stylesheet and should be the same as the directory that holds it.
5. FTP all files to server
6. With your browser, visit domain.com/wp-admin/install.php and follow the onscreen instructions
7. Login and Add Users
8. If you want page titles in your URLs, go to Settings | Permalinks and choose a Custom Structure. I like/%category%/%postname%
9. Go to Design | Themes and choose the theme that you previously uploaded
10. Go to Design | Widgets and choose the ones you need. I usually turn on Pages and Text
11. To prevent Comment SPAM, go to Plugins and turn on Akismet. Get your API key at www.wordpress.com. If you are a returning user, your API key is the same every time. To get it, login and choose “Your Dashboard” (on the left) and “Profile” (on the right). The API key is at the top.
12. If Google Analytics will be used, install the tracking code.

Pulling a Random Sample in MS Access

I have a database with 500,000 records and I need to pull 12000 random records for analysis. The simplest way to do this is to randomize the sort order and use a SELECT limiter as follows:

SELECT TOP 12000 customers.first_name, customers.last_name, customers.street_address, customers.city, customers.state, customers.zip, customers.country, customers.email
FROM customers
WHERE (((customers.state) In ("NH","VT","ME","MA","RI","CT","NY","NJ","CA","WA","OR","DC","MD","PA","VA")) AND ((customers.email) Is Not Null))
ORDER BY Rnd(ID);

Splitting LASTNAME,FIRSTNAME columns in Excel

Too often, office workers have to manipulate improperly structured data that comes from mega-systems. For example, if a person wanted to do a mail merge intro line, stating, “Dear <firstname>” but the name field held LASTNAME,FIRSTNAME, they would have a hard time, but here’s a way to handle it using an excel formula that pulls it apart and displays the correct name in title case. I haven’t really tested it with special names including extra things like sir or esquire, but it works pretty good for most names.

Assuming cell A1 holds a name like THORP,TIMOTHY – this formula will result in Timothy Thorp

=PROPER(CONCATENATE(TRIM(RIGHT(A1, LEN(A1)- FIND(",",A1,1))), " ", LEFT(A1,FIND(",",A1,1)-1)))

Explanation of each function in the order that it is processed:

LEN() counts the number of characters in the name (in this example, 13)

FIND() returns the character position of the comma (in this example, 6)

RIGHT() returns the calculated number of characters to the right of the comma (in this example, chars 7-13 or TIMOTHY)

LEFT() returns all of the characters from the beginning of the name up to the comma (in this example, characters 1-5 or THORP)

TRIM() removes extra blank spaces around the comma in case they are present

CONCATENATE() squishes the first and last name into one column together

PROPER() puts the name in title case (first letter of each word capitalized)

From there, you can use the fill handle to copy the formula down the column and Excel Text to Columns and/or paste special to separate things out as you need.

To get the first name by itself :

=PROPER(TRIM(RIGHT(A1, LEN(A1)- FIND(",",A1,1))))

To get the last name by itself:

=PROPER(LEFT(A1,FIND(",",A1,1)-1))

Setting Up a Small Site in Drupal

I have a tendency to think that small sites should be done in static html and larger sites should be done in a dynamic system like drupal or zencart or Wordpress. However, I wanted to challenging that concept by doing a small site in drupal. Here is how I setup a small site,.
Note: If you have one or more sites that you manage, you need a secure place to keep a log of what you did, what the username and password is and all that stuff. I find that for each website I manage, I need customer contact info, billing information, domain registration info, hosting info, email setup info and application authentication info.

Installing Drupal

Download the latest version of drupal which is supported by all of the modules which you plan to use
Uncompress the installer (it has a .tar.gz extension) to local desktop using 7-Zip. (You could also put the compressed file to the server and then uncompress it from the command line, but if you are comfortable doing that, then you probably don’t need step by step instructions, right?)
What you do is right-click the *.tar.gz file, then click 7-Zip | Extract Here. This operation makes a *.tar file.
Then right-click the *.tar file, then click 7-Zip | Extract Here. This gives you a directory of files.

Perform the download and uncompress operations for each of the modules which you plan to use. You will need to create a directory for your modules in /sites/all/ . My sites always use the following add-on modules, so I install them at the beginning, along with the drupal core.
Administration Menu (it puts all of the administrator functions in a set of drop-down menus)
Pathauto (it makes really nice urls, automatically)
Google analytics (it submits the tracking code)
Token (a required component of PathAuto)
Once this is complete, the directory structure looks like this:

       -siteroot
		+includes
		+misc
		+modules
		+profiles
		+scripts
		-sites
			-all
				-modules
					+admin_menu
					+google_analytics
					+pathauto
                                        +token
		+themes

Next, get a new, empty MySQL database (this should be provided by your hosting service) and record that database name, database username and database password. Put this in a safe place where you can get to it later on.
PUT the files in the root of the site using ftp (I like filezilla, but use whatever you like) Be careful that you don’t put the parent folder, “drupal*” on your webserver or people will have to go to domainname.com/drupal and that’s not the goal.
Visit the site and follow the instructions on the screen.
The first task after installing drupal is to make the administrator username and password.
Then, go to Administer | Site Building | Modules and turn on the stuff that you want to work immediately. I like the Administration Menu on from the get-go because I find it easier to navigate during setup time than the traditional menus.
Now that the Administration Menu is loaded and working and you are authenticated as the administrator, you can go to to the Administrator Menu and look through each of the items in the Site Configuration drop-down menu, making changes as you go along.
This concludes the initial setup of a site in drupal. Next up? Theming, taxonomy and some page layouts.

Export data from MySQL to CSV using PHP

In a recent project, we were collecting registrations for an event using a PHP web form and a MySQL database. The office worker wanted to download all of the collected data in an excel file. In a subsequent meeting with another office worker, the idea of easily downloading the collected data into excel was very convenient for performing mail merges and other administrative tasks, so I tried to make one “csvdump.php” file which met many needs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?php
$tbl=$_GET['tbl']; //get the table to export from the parameter in the URL
$cur=date("m-j-Y"); //construct today's date 
$filename="basename_".$cur.".csv"; //construct the default filename 
header("Content-Type: text/csv");
header("Content-Disposition: inline; filename=$filename");
include("connect.php");
$query="SELECT * FROM $tbl;";
$result=mysql_query($query) or die(mysql_error());
//loop through field names and put them in the first row, separated by commas
while($field = mysql_fetch_field($result))
{ 
	print("$field->name,");
	$fields[]=$field->name;
}
print("\r\n"); //put a line break after the first row
//loop through records, separating them with commas
while($row = mysql_fetch_array($result)) 
{ 
                foreach($fields as $field)
                {
                                print("$row[$field],");
                }
                print("\r\n"); //line break after each row
} 
?>

Good looking, good working forms

Look, I got some old-school web form techniques that I need to upgrade. I need a user friendly, server friendly, data-worker friendly, programmer friendly, form (in order of importance). Form design shall be inspired by wufoo. It shall protect the server against SQL injection and cross site scripting. Forms will be completely filled out and data will be correctly formed. It shall have well commented, standards compliant, indented and consolidated (where appropriate) code.

CSS Classes and IDs

I love working with sites designed with CSS and XHTML, but learning and remembering all of the syntax and rules challenges me. When should I use classes and when should I use IDs? What does the HTML and CSS look like when either is chosen?

As a general rule, IDs are meant for elements that happen once per page and classes are meant for elements that happen several times per page. In a practical sense, I think of classes as things like “alertbox”, “code”, “note”, “warning”, “productname”, etc.

Whereas with IDs, they are useful for page layout elements like “header”, “body”, “wrapper” and “box”. They are also useful for the name of a form and anytime you are integrating with JavaScript, IDs are more useful than classes.

I will approach this issue by using this page as an example. In the following paragraphs, I would like to write some code snippets and have them contained by a light grey box with a black border and courier text. Since an ID is used to identify an individual element on a page and I want to have several code snippets on this page, I should use a CSS class.

Classes

Classes refer to elements which will be used throughout the site and in some cases, several times on one page. I like to think of classes as the stylistic elements that are the pillars of the site.
This is HTML for applying a class to a div:

<div class="alert">

This is CSS for defining a class:

.alert {color : red; }

IDs

IDs are used for elements which occur once on a page. Their rules override class rules and can be set up differently on any page, but can only be used once per page. I use them to identify each of my form fields and page divider elements. I could use them for an alert box, as follows, but then I could only have one alert on the page.

This is HTML for applying an ID to a div:

<div class="alert">

This is CSS for defining an ID:

#alert {color : red; }

Initial Design Meetings

So, I’m at a meeting with a fellow office worker that wants a new system and I’m struck by how difficult it is for an internal client to talk about what they want. It’s not that they feel intrusive with making demands, but there’s just no common language. For example, working at a university, I wouldn’t expect a dean to ask me if I would be willing to make a web 2.0 system with a relational database backend and ajax form controls. What I am finding is that I have to understand their work in order to build a system.

This might be a stretch, but I love analogies, so I’ll give it a shot. I think the meetings are similar to what would happen if a person who never had seen a modern kitchen wanted one. He would say,

“I wonder if there is any way that I could cut my food components and heat them over a hot element”

“It would be really neat if there were a place to clean the cooking devices including a mechanism for the removal of waste material”

Of course there’s a way to do that, it’s called an oven and a sink. With programming, the capacity is there to do anything that you have seen on the web, but if the functional owner and the developer can’t talk clearly about what is wanted, I think the risk of misunderstanding could lead to a significant miscalculation such as an incorrect data model and there is no amount of AJAX that can clean that up.

Making Decisions

I just got a new position as Web Developer and am busy taking things in, meeting people and learning about priorities. One of the things that stands out is the quantity of things on my plate that need improvement. I really want to get going on some of this stuff and I am interested in utilizing a decision making model that is known for its reliable results. The most reliable one that I know of is to think thoroughly, brainstorm potential solutions, evaluate solutions and, ultimately, take a risk by making a decision. So, these are some of the things I need to decide about:

FAQ system - leaning towards Interspire ActiveKB, but maybe I should use the ASK Drupal module

Content Management System - down to drupal vs. joomla

ad-hoc forms system - this tends to be a complete hack, but I’d like to have it make some sense when it is all said and done. Kinda liking drupal webforms module. Also am considering just doing it in PHP.

project portfolio management. Really, a fancy name for the fact that people want stuff done and we can’t do all of it right now. We need a way to prioritize, estimate, complete and generally, to perform.