Writing PHP with SQL queries in Emacs

Question

What should I use in Emacs for developing PHP files with SQL queries?

When indenting the buffer the code should look like this:

<?php
$query = "
    SELECT
        id,
        name
    FROM 
        products
    WHERE 
        id > 12"
?>

In web-mode and php-mode it looks like this:

<?php 
$query = "
SELECT
id,
name
FROM
products
WHERE
id > 12"
?>

---

If this isn't possible, one alternative would be to have it enable manual indentation (using TAB and ShiftTAB, like in Sublime and other editors) when at multi-line strings in PHP code. How would I do that?

Answer 1

Doing this automatically when the buffer is indenting will be very hard, you can try multiple-major modes but that isn't ideal.

One solution will be to write a function that will format the sql underneath your cursor, you can then manually run this command when you are finished writing your query string.

This example requires two packages: expand-region and sql-indent, both are available for download on MELPA.

If you have the packages installed this function will format the SQL at point, this also handles indenting the entire SQL string according to depth of the code around it unlike the solution in the comments.

(defun sql-indent-string ()
  "Indents the string under the cursor as SQL."
  (interactive)
  (save-excursion
    (er/mark-inside-quotes)
    (let* ((text (buffer-substring-no-properties (region-beginning) (region-end)))
           (pos (region-beginning))
           (column (progn (goto-char pos) (current-column)))
           (formatted-text (with-temp-buffer
                             (insert text)
                             (delete-trailing-whitespace)
                             (sql-indent-buffer)
                             (replace-string "\n" (concat "\n" (make-string column (string-to-char " "))) nil (point-min) (point-max))
                             (buffer-string))))
      (delete-region (region-beginning) (region-end))
      (goto-char pos)
      (insert formatted-text))))

The function works by copying the string under your cursor and moving it to a temporary buffer where sql-indent will format it, then going back to the original buffer and replacing the old string with the new one. It is functional but not pretty.

Answer 2

The last version (9.0.84) of web-mode (available on github) provides native sql indentation in block strings.