Tim Thorp Training & Consulting

Change Field Order in MySQL Table Structure using phpMyAdmin

Sometimes a database admin wants to change the order of fields in their table. I find it handy when I want SELECT * to return the fields in a particular order. While there are no buttons to change the field order, you can do it with a simple SQL statement. In the following example, I am changing the order of a field with the data type TINYTEXT, but it will work with any fieldtype:

ALTER TABLE `nameoftable` MODIFY COLUMN `columnname1` TINYTEXT AFTER `columnname2`;

XML-RPC with Windows Live Writer vs. Wordpress FCKeditor

If you frequently blog, you may be frustrated by the built-in editor for Wordpress: FCKeditor. It has its limitations, but it is actually a pretty good web-based rich text editor. However, there is another choice. If you want to write using a desktop client, turn on XML-RPC in your Wordpress writing settings and connect to your blog with Windows Live Writer – it’s a great way to publish and edit posts and pages!

Data Matchbacks in SQL, mySQL, Access

To better understand where your customers come from and the value of your marketing investment, you can use a data matchback. For example, let’s say that you sent an email to a list of leads that you purchased and now you want to see how many of those email addresses have made a purchase, you would matchback customers to your list. The technique is simple and straight SQL.

• Import customers into an empty table called “customers”
• Import leads into another empty table in the same database called “leads”

SELECT * FROM customers INNER JOIN leads ON customers.email=leads.email;

That should do it, however you may want to list the fields instead of using the * wildcard. To do this, just list the fields using the table.field convention so that it is clear which fields should be selected by the query.

One final tip: export the data to excel and analyze it in a pivot table.

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 WHERE clause is not necessary for the random sampling. It is just part of the query that was used in this case.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))

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.