-1
\$\begingroup\$

I have been writing simple php programs, trying to adhere to the PSR standards, for writing code.

However they do not seem to have any standards for writing mysql PDO statements in.

For example I have written the mysql statement;

$sql = "INSERT INTO `users`(
      `name`, 
      `phone`, 
      `city`,
      `date_added`
      )VALUES(
        :name,
        :phone,
        :city,
        :date)";

Is this a correct way of formatting? Or is there anything I can do to enhance it or follow some guidelines similar to PSR on how to write these types of statements in?

\$\endgroup\$
  • \$\begingroup\$ Do you like writing it this way? Alternatively, do you think others will find it easy to understand? \$\endgroup\$ – David Barker Jul 28 '16 at 16:19
1
\$\begingroup\$

I think this is a somewhat a matter of preference. What I try do to do when writing SQL in code context is adhere to the following guidelines:

  • Make sure all lines of code are <= 80 characters in length
  • (Especially for longer queries) Keep SQL definition on separate lines of code from surrounding programming language syntax, with query not indented (to help give you more room to not exceed 80 characters on line).
  • Try to break up lines of SQL query along where different clauses within the query begin/end.
  • Where a clause portion of query would exceed 80 characters on a line, break across lines, indenting to indicate that this is a logical continuation of the clause begun on a previous line.
  • When you have longer lists of columns/values (by longer I mean maybe 5 or more) put each column value on it's own line to make it more readable. This is like you have done in your example, except for in your example case I don't know that I would put each field/value on its own line.
  • I oftentimes use heredoc/nowdoc syntax on longer queries.

For your example query, I would probably write it like this:

$sql = "
INSERT INTO `users`
    (`name`, `phone`, `city`, `date_added`)
VALUES
    (:name, :phone, :city, :date)
";

Here you can clearly see the following progression with each line:

  1. The first line clearly indicate this is insert query operating against users table.
  2. The second line defines the columns you are operating against
  3. The third line introduces a new SQL clause (this case being that you are now specifying the values for insert)
  4. The fourth line is written in a similar manner to the second line, so it is really easy to correlate the insert values to the columns.

Let's expand your example. Let's say I had 10 columns I was going to insert. In this case you could do this:

$sql = "
INSERT INTO `users`
    (`name`, `phone`, `city`, `date_added`, `column5`, `column6`,
    `column7`, `column8`, `column9`, `column10`)
VALUES
    (:name, :phone, :city, :date, :column5, :column6,
    :column7, :column8, :column9, :column10)
";

But I would probably go ahead and break each column/value on its own line in this case because I find the following to be more readable:

$sql = "
INSERT INTO `users`
  (
    `name`,
    `phone`,
    `city`,
    `date_added`,
    `column5`,
    `column6`,
    `column7`,
    `column8`,
    `column9`,
    `column10`
  )
VALUES
  (
    :name,
    :phone,
    :city,
    :date,
    :column5,
    :column6,
    :column7,
    :column8,
    :column9,
    :column10
  )
";

Let's say we have a single WHERE clause condition added. Perhaps that looks like this:

$sql = "
INSERT INTO `users`
    (`name`, `phone`, `city`, `date_added`)
VALUES
    (:name, :phone, :city, :date)
WHERE `somefield` = 'some value'
";

Or multiple WHERE conditions:

$sql = "
INSERT INTO `users`
    (`name`, `phone`, `city`, `date_added`)
VALUES
    (:name, :phone, :city, :date)
WHERE
    `somefield` = 'some value'
    AND `someotherfield` = 'some other value'
";

So at the end of the day, it really is preference to what you (and any peers you may be working with) find to be a style that you want to adopt.

I almost always stay away from "in-lining" SQL into other language code like this:

$pdo->prepare('INSERT INTO `users` (`name`, `phone`, `city`, `date_added`) VALUES (:name, :phone, :city, :date));"

Having a SQL statement in a variable makes it easier to debug the code and provide more meaningful error messages, IMO.

\$\endgroup\$
  • \$\begingroup\$ thanks this is useful information. Originally I did used to use "in-lining" but it was to hard to read so changed to the way I showed in my question. I will be using some of the techniques you have suggested from now on. \$\endgroup\$ – mp252 Jul 28 '16 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.