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?

@phils Hmm, it almost works. The indentation is a bit off. It doesn't consider indentation level of the PHP code but it does indent the SQL code. — Oskar Persson
– Oskar Persson, Commented Jul 4, 2014 at 23:32

Jordon Biondo · Accepted Answer · 2014-07-09 17:24:53Z

10

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.

enter image description here

edited Jul 9, 2014 at 17:24

answered Jul 9, 2014 at 17:16

Jordon Biondo

4,0471 gold badge30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ReneFroger Over a year ago

(Thanks Jordon Biondo! However, I would like to add calling the function ` sqlup-capitalize-keywords-in-region` from SQLup. When I add sqlup-capitalize-keywords-in-region inside the function, and call it from the sql string, I got an empty result. Any tips?

user1971598 Over a year ago

Can you turn this into a minor mode?

fxbois · Accepted Answer · 2014-09-16 22:24:05Z

2

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

answered Sep 16, 2014 at 22:24

fxbois

83610 silver badges21 bronze badges

4 Comments

Oskar Persson Over a year ago

I can't get it to work with the code (second part) I had in the question. Should I be able to be on the third row, press TAB and have it indented?

fxbois Over a year ago

SELECT should be just after " Moreover are you sure you have the latest version ?

Oskar Persson Over a year ago

Great! It works on the example but could I make it indent it even if SELECT is on a new line? Also, there seems to be a bug when having SELECT inside JOIN. INSERT queries doesn't indent either. It's a great start but seems to need some more work.

fxbois Over a year ago

I've added highlighting and compatibility with insert, update, delete. I do not plan to work on indentation on new line. If you have other requests and issues, please open an issue on github

Mike Polachekster · Accepted Answer · 2014-06-24 22:25:48Z

0

Emacs doesn't handle SQL with PHP very well. There's an extension you can get here that supposedly helps with SQL only so I'm not sure if it will behave the way you want it to with PHP code. There are other alternatives to using Emacs though, and I would strongly suggest it.

answered Jun 24, 2014 at 22:25

Mike Polachekster

342 bronze badges

Collectives™ on Stack Overflow

Writing PHP with SQL queries in Emacs

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related