Menu

Tag Archives: coding

I had been looking off and on for a few years for a way to insert a timestamp into a file in Notepad++, preferably by a hotkey. The no-longer-in-development TextFX plugin frequently comes up in Google searches as one way of doing this, but the plugin doesn’t offer any ability to customize the format of the date or time stamp.

Download the Python Script plugin from http://npppythonscript.sourceforge.net/download.shtml and install it. Do not download it from within Notepad++ by clicking Plugins | Plugin Manager | Show Plugin Manager, scrolling down the Available plugins list to Python Script, checking the box and clicking Install, because it just won’t work (and you may get an older version of the plugin).

Restart Notepad++ and you’ll find that Python Script has been added to the Plugins menu.

To create the script that will insert the timestamp, click Plugins | Python Script | New Script. Enter a filename for the script file you are about to create, like “Time.py”, and click Save.

A new, blank tab will appear in Notepad++. Paste in the following text:

But, you probably want to be able to run this as a macro from a keyboard shortcut. Close the Time.py tab and then exit and relaunch Notepad++. Click on Settings | Shortcut Mapper… and choose the Plugin commands tab. The Time script should be listed here somewhere (in my case, it is usually somewhere around number 27). Highlight it, click Modify and assign it to a shortcut.

I choose to map my Time script to F5, because this mirrors the timestamp functionality built into Windows notepad.exe, but Notepad++ already uses that keystroke for the Run command. I never use the Run command, so I just remove the shortcut from that command by clicking on Settings | Shortcut Mapper… and choosing the Main menu tab, scrolling down to the Run item and either removing the mapping by changing the shortcut to None or changing it to something else, such as Ctrl+F5. For me, Run was located in the Shortcut mapper under Main menu around number 208. Close the Shortcut Mapper and you’re ready to use your new timestamp hotkey.

I like Akismet, and it’s undeniably effective in stopping the vast majority of spam, but it adds a huge number of comments to the database and a very small percentage of comments still get through to my moderation queue.

It’s annoying to find comments in my moderation queue, but what I really object to is the thousands of records that are added to the database each month that I don’t see.

In May, I moved hosting providers to asmallorange.com and started with a fresh install of WordPress without implementing my custom spam method, which admittedly was not ideal because it involved changing core files. This left only Akismet between the spammers and my WordPress database. Since that time, instead of 150 or fewer spam comments per month making it into my WordPress database, Akismet was on pace to let in over 10,000.

I needed to insert a short delay between two processes, so I whipped up a little VBScript that accepts an argument in seconds and then sleeps for that amount of time. If no argument is passed, it sleeps for 3 seconds. It writes to the Application event log before it sleeps and after it wakes.

Usage: sleep.vbs 5

It could be better, sure, but I’m humble about it. It doesn’t validate that the argument is an integer, for example. But it does the trick when used correctly.

The WMI Code Creator tool allows you to generate VBScript, C#, and VB .NET code that uses WMI to complete a management task such as querying for management data, executing a method from a WMI class, or receiving event notifications using WMI.
https://technet.microsoft.com/en-us/scriptcenter/dd823314.aspx

The tool also allows you to browse through the available WMI namespaces and classes on the local computer to find their descriptions, properties, methods, and qualifiers.
https://www.microsoft.com/en-us/download/details.aspx?id=8572

I wanted to use BGInfo to display only the IPv4 address(es) of a workstation. BGInfo’s built-in IP address ouput returns both IPv4 and IPv6 formatted addresses, but you can use the output of a VBScript as a data source for a custom field. Starting with the nice script provided in the comments of the TechNet forum thread at: WMI Query to retrieve only active IPv4 address, I’ve made a few aesthetic changes so that the IPv4 addresses of active network adapters are displayed in a single column.

Ideally, I’d be able to report whether the IP address was attached to a wired or wireless adapter, but that is beyond the scope of this particular project.

But, in the event that someone wants to do something that sophisticated, Microsoft’s WMI Code Creator v1.0 would be a very good place to start.

The WMI Code Creator tool allows you to generate VBScript, C#, and VB .NET code that uses WMI to complete a management task such as querying for management data, executing a method from a WMI class, or receiving event notifications using WMI.
http://www.microsoft.com/en-us/download/details.aspx?id=8572

Hint: look at the Description property of the Win32_NetworkAdapterConfiguration class.

Here’s an example script demonstrating how a publicly accessible home page can leverage JavaScript to detect whether a machine is on a corporate intranet and then redirect the browser to an intranet page.

In the example, http://alephstudios.com acts as the corporate intranet site that is not accessible from outside the company’s network, and //ardamis.com acts as the publicly accessible site, which can be accessed both from within and outside the corporate network.

The browser is set to use a page on the public //ardamis.com site which includes some JavaScript that attempts to load an image from a location on the company intranet. If the image can be successfully loaded by the browser, we have establishe that the machine is on the internal network. The browser can then be redirected via JavaScript to an appropriate intranet page. Otherwise, the browser is redirected to an Internet page.

Setting up an intranet detection/redirection page as the browser’s home page allows IT to display an intranet page while the device is on the network and an Internet page when the device is off the network.

In our corporate environment, our Windows 7 workstations can be powered off or restarted remotely in order to deploy updates, patches, or new software. For those of us running virtual machines in VMware Workstation, this means a running guest operating system would experience an abrupt power-off as the host machine is reset. At the very minimum, this causes the ‘Windows was not shut down properly’ message to appear when the guest OS is powered on, and it may cause serious problems with the integrity of the guest OS or the virtual machine files.

I wanted to improve the situation through the use of shutdown/logoff and startup/logon scripts on the host and the vmrun command line utility that ships with VMware Workstation and VMware Server, and I had three goals in mind.

Any running guest OS would be allowed to shut down or suspend before the host powered off

An event would be written to the Application log on the host for each guest that was shut down or suspended

A complementary process would start or resume each guest that was running when the host restarted

The VBScripts are written for use on a 64-bit Windows 7 host.

The challenge of correct timing

I soon ran into a problem when trying to use Local Group Policy to deploy the shutdown/logoff script on my Windows 7 host. The order of events is such that the shutdown/logoff process is halted by the still-running vmware.exe process (the VMware Workstation UI). I’ve added some notes about this behavior to the bottom of the post, but I have not yet solved this problem.

A word about networking

If the network adapter in the guest OS is not reconnected upon resuming from suspend (in Windows, this can be resolved with ipconfig /renew), it may be that the VMware Tools scripts are not running at start up/resume. Disconnecting from the network is a normal process when the VM receives a suspend command with a soft parameter. I have found that I can ensure that the network adapter is reconnected upon resuming by changing the Power Options for the VM to use “Start Up Guest” instead of “Power On”.

The shutdown/logoff script

This is what I came up with for the shutdown/logoff script.

' This script passes the suspend command to each running VMware virtual machine, allowing it to gracefully sleep/hibernate
' It also saves the list of running VMs to a text file in %TEMP%, which may be parsed by a startup/logon script to resume the VMs
' It can be used as a shutdown/logoff script
' //ardamis.com/2012/03/08/managing-vmware-workstation-virtual-machines-with-vbscript/
Option Explicit
Dim objShell, objScriptExec, objFSO, WshShell, strRunCmd
Dim TEMP, strFileName, vmList, objFile, ForWriting, result, textLines, textLine, isFirstLine
'Initialize the objShell
Set objShell = CreateObject("WScript.Shell")
'Execute vmrun and create the list of running virtual machines
Set objScriptExec = objShell.Exec("""C:\Program Files (x86)\VMware\VMware Workstation\vmrun.exe"" list")
'Write the list to a variable
vmList = objScriptExec.StdOut.ReadAll()
'Debug
'WScript.Echo vmList
'Initialize the wshShell
Set WshShell = WScript.CreateObject("WSCript.shell")
TEMP = WshShell.ExpandEnvironmentStrings("%TEMP%")
'Enter the path to the file that will hold the names of the running VMs
strFileName = TEMP & "\vms.txt"
'Debug
'WScript.Echo strFileName
'Initialize the objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Create the file
Set objFile = objFSO.CreateTextFile(strFileName)
'Write the list to the file
objFile.Write vmList
'Close the file
objFile.Close
'Split the list into lines
textLines = Split(vmList,vbCrLf)
'Loop through the lines
For Each textLine in textLines
'Compare the first line in the file to the text "Total running VMs:"
isFirstLine = StrComp(Mid(textLine, 1, 18), "Total running VMs:")
'If the line has more than 0 character (is not blank) and is not the first line
If Len(textLine) > 0 And isFirstLine <> 0 Then
'Write to the application log
WshShell.LogEvent 4, "Event: VMware is attempting to suspend the VM at " & textLine
'Save the command as a variable
strRunCmd = """C:\Program Files (x86)\VMware\VMware Workstation\vmrun.exe"" -T ws suspend """ & textLine & """ soft"
'Run the command
result = WshShell.Run(strRunCmd, 0, True)
'Write to the application log
If result = 0 Then
WshShell.LogEvent 4, "Event: VMware successfully suspended the VM at " & textLine
Else
WshShell.LogEvent 1, "Event: VMware was unable to suspend the VM at " & textLine
End If
'Debug
'WScript.Echo result
'Debug
'WScript.Echo textLine
End If
Next

The vms.txt file that the script creates will contain something like the following, if it finds a running VM:

I have chosen to suspend the virtual machine, rather than shut it down, because I don’t want to lose any work that may be unsaved. The official explanation of the suspend power command from VMware:

Suspends a virtual machine (.vmx file) or team (.vmtm) without shutting down, so local work can resume later. The soft option suspends the guest after running system scripts. On Windows guests, these scripts release the IP address. On Linux guests, the scripts suspend networking. The hard option suspends the guest without running the scripts. The default is to use the powerType value specified in the .vmx file, if present.
To resume virtual machine operation after suspend, use the start command. On Windows, the IP address is retrieved. On Linux, networking is restarted.http://www.vmware.com/support/developer/vix-api/vix110_vmrun_command.pdf

The startup/logon script

This is the startup/logo script that compliments the shutdown/logoff script.

' This script reads a list of VMware virtual machines from a text file and passes the start command to each VM, allowing it to resume from sleep/hibernate/shutdown
' It can be used as a startup/logon script
' //ardamis.com/2012/03/08/managing-vmware-workstation-virtual-machines-with-vbscript/
Option Explicit
Dim objFSO, WshShell, strRunCmd
Dim TEMP, strFileName, objTextStream, vmList, ForReading, result, textLines, textLine, isFirstLine
'Initialize the wshShell
Set WshShell = WScript.CreateObject("WSCript.shell")
TEMP = WshShell.ExpandEnvironmentStrings("%TEMP%")
'Enter the path to the text file that will hold the names of the running VMs
strFileName = TEMP & "\vms.txt"
WshShell.LogEvent 4, "Event: VMware is attempting to find a list of VMs to restart in " & strFileName
'Initialize the objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Check to see if the text file exists
If objFSO.FileExists(strFileName) Then
'Open the text file
Set objTextStream = objFSO.OpenTextFile(strFileName, 1)
'Read the contents into a variable
vmList = objTextStream.ReadAll()
'Debug
'WScript.Echo vmList
'Close the text file
objTextStream.Close
'Split the list into lines
textLines = Split(vmList,vbCrLf)
'Loop through the lines
For Each textLine in textLines
'Compare the first line in the file to the text "Total running VMs: 0"
isFirstLine = StrComp(Mid(textLine, 1, 20), "Total running VMs: 0")
'Check to see if the first line of the text file reports 0 running VMs
If isFirstLine = 0 Then
'Write to the application log
WshShell.LogEvent 4, "Event: VMware found no running VMs were enumerated in " & strFileName
End If
'Compare the first line in the file to the text "Total running VMs:"
isFirstLine = StrComp(Mid(textLine, 1, 18), "Total running VMs:")
'If the line has more than 0 character (is not blank) and is not the first line
If Len(textLine) > 0 And isFirstLine <> 0 Then
'Write to the application log
WshShell.LogEvent 4, "Event: VMware is attempting to start the VM at " & textLine
'Save the command as a variable
strRunCmd = """C:\Program Files (x86)\VMware\VMware Workstation\vmrun.exe"" -T ws start """ & textLine
'Run the command
result = WshShell.Run(strRunCmd, 0, True)
'Write to the application log
If result = 0 Then
WshShell.LogEvent 4, "Event: VMware successfully started the VM at " & textLine
Else
WshShell.LogEvent 1, "Event: VMware was unable to start the VM at " & textLine
End If
'Debug
'WScript.Echo result
'Debug
'WScript.Echo textLine
End If
Next
Else
WshShell.LogEvent 4, "Event: VMware did not find a list of VMs to restart at " & strFileName
End If

This script starts/resumes the virtual machine and launches the Workstation user interface.

Timing of the shutdown/logoff events

Using Group Policy shutdown/logoff scripts seemed a natural way to power off and resume the virtual machines, but there is a timing problem that prevents this from working as desired. Instead of running any logoff scripts immediately when the user chooses to log off, Windows first tries to close any open applications by ending running processes. When it encounters vmware.exe, which is the VMware Workstation GUI, it pauses the log off process and asks the user whether the log off should force the applications to close, or if the log off should be cancelled.

On Windows 7, the screen will dim and the programs that are preventing Windows from logging off the user or shutting down are listed.

To close the program that is preventing Windows from logging off, click Cancel, and then close the program.
[Force log off] [Cancel]

As pointed out on the vmware.com community forums, this only happens when the Workstation UI process is running at the time.

We don’t support running Workstation a service. I assume you’re using some third-party tool for that?

Anyway, that error only appears if the Workstation UI is running when you try to log off. If you kill the UI process (vmware.exe) and let the VM run in the background, you shouldn’t get that. Alternatively you could try running VMware Player instead of VMware Workstation a service.http://communities.vmware.com/message/1189261

Quitting the Workstation process and allowing the scripts to close out the actual VMs seemed like an acceptable compromise. It still required some user interaction on the host to prepare the guest to be powered off, but I figured that there may be ways to end the Workstation UI programatically prior to the logoff.

You can set a virtual machine that is powered on to continue running in the background when you close a virtual machine or team tab, or when you exit Workstation. You can still interact with it through VNC or another service.
From the VMware Workstation menu bar, choose Edit > Preferences. On the Workspace tab, select Keep VMs running after Workstation closes and click OK.http://www.vmware.com/pdf/ws71_manual.pdf

I found that if the VMware Workstation application is already closed, the shutdown/logoff proceeds smoothly and the scripts fire. But there is another problem. By the time the logoff script runs, the vmware-vmx.exe process (the actual virtual machine) has already been quit, so the vmrun list command finds no running VMs and you end up with a vms.txt file that contains this:

Total running VMs: 0

At this point, running VMware Player like a service logged on as the Local System account, which presumably will allow the VMs to continue running even while users on the host log out, becomes the best solution, as it theoretically avoids the problem of a) requiring the user to close the UI and b) the vmware-vmx.exe process being ended as the user logs off. VMware Player is included with Workstation, but we’re not quite out of the woods yet. According to another VMware employee:

VMware Player is not built to run as a service. However, there are different discussions and possible solutions using srvany.exe.
If you google for site:vmware.com srvany player you will find some interesting posts for this issue.http://communities.vmware.com/message/1595588

I followed through on this suggestion, and while it didn’t solve my problem, I’m including some detail here in the hopes that it will further someone else’s exploration.

The Windows Server 2003 Resource Kit Tools are not officially supported on Windows 7, and in fact the installer will cause the Program Compatibility Assistant to warn that “This program has known compatibility issues”, but my observations seems to support other people’s reports that they work fine.

Open an elevated command prompt and enter instsrv [service name] [srvany.exe location], using anything you want for the service name (ex: instsrv vmplayer “C:\Program Files (x86)\Windows Resource Kits\Tools\srvany.exe”)

Open an elevated instance of the Windows Services snap-in (services.msc), right-click the newly created service, choose the Log On tab, and check the box next to “Allow service to interact with desktop”

What I need is the best of both worlds: a VMware GUI environment, be it Workstation or Player, that is able to load a VM when a user logs into the host, and at the same time is able to keep the VM running while that user logs off.

Ultimately, I’m left with the same feelings as expressed toward the end of the thread at http://communities.vmware.com/message/1402590: why should useful and highly sought-after functionality that is present in the free but no-longer-actively developed Server product be absent from the non-free and actively developed Workstation product?

The answer, if there is one, may be that VMware doesn’t want to get involved.

One of the nastier corner cases is, what happens if there is a failure suspending the VM? Do we decide the user really wanted to log off and forcibly kill the VM, or do we veto the log-off and go back to the user for input (which, if you are using a laptop, means closing the lid leaves the VM running and kills the battery)? What if the VM process crashes during this – who initiates the log-off then? What if the VM is busy doing something expensive (like disk consolidation) and cannot suspend? Getting involved in the log-off path is, realistically, just a mess of bugs.http://communities.vmware.com/thread/233117

Final thoughts

As with pretty much anything I do, this is far from finished. I’m not ready to give up on the goal of using scripts to start and suspend VMs without any user interaction. But it seems that it’s going to be much more difficult than one might reasonably expect.

As for the scripts themselves, I’m slightly bothered by the empty command prompt window that is opened momentarily by objShell.Exec. I’m not sure that I like saving the list of running VMs to %TEMP%, where it may be deleted by other processes that clean that location at login/logout. But they are a good start, and they seem to serve their purpose.

In the VBScript example below, I’m using the Icacls.exe utility to assign modify permissions to the D:\Test folder for the user Oliver on the LOOMER domain (or local machine). The script includes as comments some good resources on the subject.

This script is a work-in-progress. To be considered complete, I want it to be able to create multiple directories and assign them permissions. For extra credit, I want it to be able to accept as input a list of usernames from a text file and iterate through them, creating folders where necessary and assigning them permissions.

As part of a migration from Windows XP to Windows 7, I was asked to come up with a way to export the network printers installed on the XP machines such that they could be reinstalled on the Windows 7 machines. We did not want to capture local printers (printers installed via TCP/IP or connected via USB) or virtual printers (like the Adobe PDF virtual printer or the Microsoft XPS Document Writer). I thought that migrating the printers was less attractive because the source machines are 32-bit Windows XP but the destination machines are 64-bit Windows 7 and the drivers are therefore different.

There are a number of ways to export print queues, printer settings, and printer ports, but for my purposes, I decided that all I wanted was to determine the name of each printer (eg.: \\SERVER\Printer) on the XP machine, export that to a text file on a network share, and then run PrintUI.exe /ga on the Windows 7 machine, looping through the lines from the text file as input.

(Check out the Printer Migration wizard by launching PrintBrmUI.exe, or the command line version %WINDIR%\System32\Spool\Tools\Printbrm /?, for alternatives to printui.exe.)

As an added benefit, I’m also exporting the name of the default printer so that it can be set programatically in the new environment.

importPrinters.vbs

Note that, if your users in Windows 7 are not administrators, you will need to run the script as an administrator (there are a few different ways) or else you’ll get a UAC prompt for each printer installation and the restarting of the print spooler.

Again, watch the paths. This is a home-grown script for my specific environment.

Option Explicit
'This script must be run with administor privileges
'If it is not run with administrator privileges, it will launch a UAC prompt for each printer as it loops through the list
'Here's an interesting article about running VBS as a different user:
'http://blogs.technet.com/b/heyscriptingguy/archive/2006/04/28/how-can-i-use-the-runas-command-to-run-a-script-under-alternate-user-credentials.aspx
'For example:
'runas /profile /user:[username]\[password] "cscript.exe \"F:\Printer Driver Research\Automation\printers-import.vbs"\"
Dim objNetwork, strComputer, strName, strFolder, objFSO, strTextFile, strData, strLine, arrLines, strRunCmd, WshShell
CONST ForReading = 1
'Create a Network Object
Set objNetwork = CreateObject("Wscript.Network")
'Get the local machine name from the Network Object
strComputer = objNetwork.ComputerName
'Get the user's username from the Network Object
strName = objNetwork.UserName
'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Save the name of the text file as a variable
'Note that the script must be run as an administrator and that invoking the script with
'Run As causes the script to run as though it's located in the same directory as "%SystemRoot%\System32\WScript.exe"
'Hence the need to pass the full path to the printers.txt file
strTextFile = "H:\PRINTERS\printers.txt"
'Open the text file - strData now contains the whole file
strData = objFSO.OpenTextFile(strTextFile,ForReading).ReadAll
'Split the text file into lines
arrLines = Split(strData,vbCrLf)
'Initialize the wshShell
Set WshShell = WScript.CreateObject("WSCript.shell")
'Step through the lines
For Each strLine in arrLines
If Len(strLine) > 0 Then
'Only run the process on lines that aren't blank
' strRunCmd = "rundll32 printui.dll,PrintUIEntry /ga /c\\" & strComputer & " /n" & strLine & ""
strRunCmd = """printui.exe"" /ga /q /c\\" & strComputer & " /n" & strLine & ""
'Echo back the command to be run
' WScript.Echo strRunCmd
'This launches printui.exe
' strRunCmd = """printui.exe"""
'Run the command, display the window, and wait for the command to complete before continuing
Dim result
result = WshShell.Run(strRunCmd, 1, True)
' WScript.Echo result
'Write to the application log that the printer was installed
If result = 0 Then
WshShell.LogEvent 0, "User: " & strName & " - Event: attempted to install printer " & strLine & " with [" & strRunCmd & "] (success unknown)"
End If
' WScript.Echo "Processed printer: " & strLine
End If
Next
'Cleanup
Set objFSO = Nothing
'Wait 10 seconds
WScript.Sleep 10000
'Restart the Print Spooler
RestartService "Print Spooler", True
Sub RestartService( myService, blnQuiet )
' This subroutine restarts a service
' Arguments:
' myService use the service's DisplayName
' blnQuiet if False, the state of the service is displayed
' every second during the restart procedure
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com
' Standard housekeeping
Dim colServices, colServicesTest, objService
Dim objServiceTest, objWMIService, strQuery, strTest
' Create a WMI object
Set objWMIService = GetObject( "winmgmts:\\.\root\CIMV2" )
' Query the services for "our" service
strQuery = "SELECT * FROM Win32_Service WHERE DisplayName='" & myService & "'"
Set colServices = objWMIService.ExecQuery( strQuery, "WQL", 48 )
' Loop through the "collection" of returned services
For Each objService In colServices
' See if we need to tell the user we're going to stop the service
If Not blnQuiet Then
WScript.Echo "Stopping " & myService
End If
' Stop the service
objService.StopService
' Wait until the service is stopped
Do Until strTest = "Stopped"
' Create a new object for our service; this work-around is required
' since otherwise the service's state information isn't properly updated
Set colServicesTest = objWMIService.ExecQuery( strQuery, "WQL", 48 )
' Loop through the "collection" of returned services
For Each objServiceTest In colServicesTest
' Check the service's state
strTest = objServiceTest.State
' See if we need to show the progress
If Not blnQuiet Then
WScript.Echo "State: " & strTest
End If
' Wait 1 second
WScript.Sleep 1000
Next
' Clear the temporary object
Set colServicesTest = Nothing
Loop
' See if we need to tell the user we're going to (re)start the service
If Not blnQuiet Then
WScript.Echo "Starting " & myService
End If
' Start the service
objService.StartService
' Wait until the service is running again
Do Until strTest = "Running"
' Create a new object for our service; this work-around is required
' since otherwise the service's state information isn't properly updated
Set colServicesTest = objWMIService.ExecQuery( strQuery, "WQL", 48 )
' Loop through the "collection" of returned services
For Each objServiceTest In colServicesTest
' Check the service's state
strTest = objServiceTest.State
' See if we need to show the progress
If Not blnQuiet Then
WScript.Echo "State: " & strTest
End If
' Wait 1 second
WScript.Sleep 1000
Next
' Clear the temporary object
Set colServicesTest = Nothing
Loop
Next
End Sub

setDefaultPrinter.vbs

Finally, we want to set the default printer. The printer will have to already exist (obviously). If you’ve just fired off the printui.exe /ga command to install the printer, it won’t be available until the print spooler is restarted. So wait a minute or two before running the script.

For a recent project, I needed to create a form that would perform a look up of people names in a MySQL database, but I wanted to use a single input field. To make it easy on the users of the form, I wanted the input field to accept names in either “Firstname Lastname” or “Lastname, Firstname” format, and I wanted it to autocomplete matches as the users typed, including when they typed both names separated by a space or a comma followed by a space.

The Ajax lookup was quick work with jQuery UI’s Autocomplete widget. The harder part was figuring out the most simple table structure and an appropriate SQL query.

A flawed beginning

My people table contains a “first_name” column and a “last_name” column, nothing uncommon there. To get the project out the door, I wrote a PHP function that ran two ALTER TABLE queries on the people table to create two additional columns for pre-formatted strings (column “firstlast”, to be formatted as “Firstname Lastname”, and column “lastfirst”, to be formatted as “Lastname, Firstname”), added indexes on these columns, and then walked through each record in the table, populating these new fields. I then wrote a very straight forward SQL query to perform a lookup on both fields. The PHP and query looked something like this:

// The jQuery UI Autocomplete widget passes the user input as a value for the parameter "name"
$name= $_GET['name'];
// This SQL query uses argument swapping
$query = sprintf("SELECT * FROM people WHERE (`firstlast` LIKE '%1\$s' OR `lastfirst` LIKE '%1\$s') ORDER BY `lastfirst` ASC",
mysql_real_escape_string($name. "%", $link));

This was effective, accurate, and pretty fast, but the addition of columns bothered me and I didn’t like that I needed to run a process to generate those pre-formatted fields each time a record was added to the table (or if a change was made to an existing record). One possible alternative was to watch the input and match either lastname or firstname until the user entered a comma or a space, then explode the string on the comma or space and search more precisely. Once a comma or a space was encountered, I felt pretty sure that I would be able to accurately determine which part of the input was the first name and which was the last name. But this had that same inefficient, clunky bad-code-smell as the extra columns. (Explode is one of those functions that I try to avoid using.) Writing lots of extra PHP didn’t seem necessary or right.

I’m much more comfortable with PHP than with MySQL queries, but I realize that one can do some amazing things within the SQL query, and that it’s probably faster to use SQL to perform some functions. So, I decided that I’d try to work up a query that solved my problem, rather than write more lines of PHP.

CONCAT_WS to the rescue

I Googled around for a bit and settled on using CONCAT_WS to concatenate the first names and last names into a single string be matched, but found it a bit confusing to work with. I kept trying to use it to create an alias, “lastfirst”, and then use the alias in the WHERE clause, which doesn’t work, or I was getting the literal column names back instead of the values. Eventually, I hit upon the correct usage.

The first instance of CONCAT_WS isn’t needed for the lookup. The first instance allows me to order the results alphabetically and provides me an array key of “lastfirst” with a value of the person’s name already formatted as “Lastname, Firstname”, so I don’t have to do it later with PHP. The lookup comes from the two instances of CONCAT_WS in the WHERE clause. I haven’t done any performance measuring here, but the results of the lookup get back to the user plenty fast enough, if not just as quickly as the method using dedicated columns.

The result of the query is output back to the page as JSON-formatted data for use in the jQuery Autocomplete.

The end result works exactly as I had hoped. A user of the form is able to type a person’s name in whatever way is comfortable to them, as “Bob Smith” or “Smith, Bob”, and the matches are found either way. The only thing it doesn’t do is output the matches back to the autocompleter in the same format that the user is using. But I can live with that for now.