Larry Wall, the creator of Perl, has stated that the three virtues of a programmer are laziness, impudence, and hubris. I think he's right. Most programming seems to consist of writing a small amount of interesting logic to implement business rules or a user interface, and then a lot of glue to hold it all together.
Calling SQL in a PHP function is a pretty simple operation, requiring several steps. Example 1, a generic PHP function: first, take in any parameters to the function. Initialize MySQL or get an existing handle. Clean the inputs to guard against SQL insertion attacks or cross-site scripting. Run the SQL via the MySQL interface. Then process the result. In the case of a
SELECT statement the code must loop over the returned rows. In the case of an
INSERT, returning the
insert_id would make sense; otherwise, return a
true value to the calling function. The code should also throw an exception in case of a problem.
This is a little too complex to copy and paste for different SQL statements. First of all, the code to escape strings must be customized for each instance, as the parameter names will change. However, it is not hard to write code to parse a SQL statement and output a PHP function to meet these needs. You could write this function in any number of languages; for example, writing it in PHP seems obvious. As I do my programming in GNU Emacs, it seems sensible to write it in Emacs Lisp so that I can run it directly in my Emacs buffer without having to call an external program.
Emacs Lisp is an excellent language for this type of application, as it combines the power of Lisp with integration into Emacs, one of the most powerful text editors around.
This Lisp code is pretty simple. Grab the SQL text, parse out the PHP variables from a SQL statement using a regular expression, and then use that information to write the full PHP function to insert into the original Emacs buffer.
For programmers who grew up with languages in the C family tree (which also includes, C++, C#, Java, Perl, and PHP), Lisp just looks strange. There are lots of parentheses and not much else. The secret to Lisp's grammar is that it really doesn't have any. Everything in Lisp is data, including the code. A Lisp expression starts with a parenthesis, the first word is the command, and everything following that is a parameter to that command. In Lisp, all expressions return a value and the compiler evaluates and substitutes nested expressions as needed.
Emacs Lisp defines a function with the
defun keyword, which takes a function name and a list of parameters. You can also define an unnamed function with the
lambda keyword, which just takes a list of parameters. A function defined by
lambda has no name, but you can use it where you need to pass a function. This can be quite powerful, as you can create functions that return other functions. As in Perl, Lisp can treat functions as data.
Lisp stands for "List Processing," and of course it handles lists very well. The
list is the basic data structure in Lisp. The basic ways to access the elements of the list are with
(car list) and
(cdr list). The
(car list) statement returns the first element of the list, and the
(cdr list) statement returns the rest of the list. It is, of course, also possible to access the nth element of a list directly.
To build a list, if you know all of the items in advance, use the
(list args) command. If you don't know all of the items, put it together one item at a time with
cons. A list in Lisp is a sequential access data structure. Each element of the list consists of a pointer for data and a pointer for the next element in the list. A normal list has an empty list (equivalent of a null) as its last element. Lists can also be recursive or contain other data structures.
This application generally needs to apply a function to each element of the list and then concatenate the results together. To do that, use the
mapconcat function. This function takes a list and a function, in the form of a
mapconcat applies the function to each of the elements in the list, and then puts all the strings together into one big string.
Lisp comments start with a semicolon (
;) and continue to the end of the line, similar to the way the hash (
#) works in PHP.
To evaluate a Lisp expression in Emacs, you must be in a buffer in "Emacs-Lisp" mode. The Emacs command
C-x C-e evaluates the last Lisp expression and returns the result in the mini-buffer at the bottom of the Emacs window.
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* 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
join statement all in one.
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
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.
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
(load-file ".elisp/makeFunction.el") (global-set-key [f6] 'sql2php)
Example file: makeFunction.el
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.
Return to the PHP DevCenter.
Copyright © 2017 O'Reilly Media, Inc.