To actually build the PHP function, you need two things: the name to give the function and the SQL statement. The makePHPFunction function takes these parameters and uses them to build the PHP code. It returns the text of the PHP code as its result.

First, the function calls findArguments, which parses the SQL looking for PHP variables. This function uses tail recursion to build its list. It uses the Emacs Lisp string-match function to step through the code to find all of the variable names. It then uses the cons statement to build them into a list. The Emacs Lisp string-match function finds the first occurrence of a pattern in a string, or the first after a given position. To build a list out of this, a small inner function recursively iterates over the string. It first matches from position 0 and continues until it can not find a match. It then joins the result of each match with cons into a list. The final element in any list should always be an empty list, which is what it adds when it can no longer find a match.

Next, the function assigns the arguments that it has parsed out of the SQL statement and to the lexical variable arguments by using the let* statement. let* is a special form that allows the creation of lexical variables inside a body of Lisp code. The first section of the command assigns values to variables; the second part forms the lexical scope of those variables.

With the function name, the SQL statement, and the list of arguments, now the code can actually build the PHP code. In Emacs Lisp, the concat function merges several strings into one long string. The main body of the makePHPFunction function is a series of function calls, each of which build a section of the final PHP code.

To build the function signature and to escape all of the input variables, the code uses mapconcat again. This is the equivalent of using a Perl map and join statement all in one.

The makeEscape function uses mapconcat with a more complex lambda function. It needs to build a series of PHP statements to escape each variable against cross-site-scripting or SQL-injection attacks. To do that, it applies the PHP functions htmlspecialchars() and mysql->real_escape_string() to each of the variables found by the find arguments command. The Emacs Lisp format function mirrors the C sprintf function. This lambda builds the PHP commands to escape the inputs for each variable.

After escaping all of the input variables, all that is left is to run the SQL and do something with the result. The SQL itself is present in a here document in the output PHP code. The $mysql->query() method runs the code and throws an exception if there is a problem. The code will then respond to the code in one of three ways. In the case of an INSERT, it will return the insert_id of the inserted row. In the case of a SELECT, it will loop over the rows returning. It does not write code to do anything with this data, under the assumption that the user will write this code by hand. For any other type of SQL, it returns a true value to indicate that the code ran correctly.

UI Binding

The next step is to bind the Emacs Lisp code into Emacs to make it invokable with a keystroke. You need to do a few things to make Emacs notice the function and bind it to the user interface. To do this, use the interactive, function which takes as a parameter a string that tells what information to ask the user for. Pass it two parameters: r, which causes Emacs to fill in the region between the point and mark into the start and end, and s, which prompts for a string to pass into the fname parameter, to become the function name in PHP.

To actually activate the function, you have to tell Emacs to load all of this as it launches and then bind it to a key combination. In your .emacs file, use the load-file command to load Lisp code into Emacs. To bind it to a key combination, use the global-set-key command:

This binds the function to the F6 key, which happens to work well for me. You can bind it to pretty much any free key combination that you are not using for something else.

After loading and binding the function to a keystroke in Emacs, all you have to do is to write a SQL statement, select it in Emacs, and hit F6. Emacs will write and insert the ready-to-use PHP code into your buffer.

Zachary Kessin
has worked with free software and web development for more than ten years. He is a frequent speaker at Jerusalem.pm and has spoken at YAPC::Israel.