Best practices for writing procedural SQL queries

If some form of PDO or prepared statements aren't available and you're stuck writing procedural raw SQL queries, there are some best practices that help make your code maintainable and readable. There is no one right solution, but typically one size fits most.

A few things to keep in mind:

• Be consistent, even if you're by yourself and especially if you're in an organization. You don't have to do it exactly this way, but whatever way you do it, stick to it. Standards exist for a reason.
• If you don't have to alias, don't. Nothing spells obfuscation like t1, t2, etc. If you DO have to alias (like shortening table names), make it human readable.
• Avoid indenting the actual SQL. You'll spend more time playing with the indent than you will writing functional code.

For example:

$sql_query .= '(AND you = "winner") ';

vs.

$sql_query .= '           (AND you = "winner") ';

• Try to have one piece of logic per line. If you try to cram everything on one line, diffs won't clearly describe what changed, you break whatever coding standard line lengths may exist, you have to scroll to edit, you can't see everything at once, etc. Exception - JOIN statements that include the ON inline are typically easier to read.
• Capitalize verbs. There's no functional difference, but it a great visual indicator that you're doing something, not working with a field.
• Avoid SELECT * - you should always know exactly what you're getting, not just trying to stick your hand in a fire hose and hope everything's there.
• Don't LEFT JOIN unless you need things from both tables that don't match
• Avoid implicit syntax - keep the JOIN logic together so you don't have to hunt for the matching WHERE.

For example:

FROM books
INNER JOIN authors ON books.author_id = authors.author_id
WHERE authors.author_name = ...

vs.

FROM books, authors WHERE books.author_id = authors.author_id AND authors.author_name = ...

• End every line with a space before the ending quote. That way, if you cut & paste code, you don't have to worry about spacing differences.
• A space after every comma helps readability.
• Always use single quotes so you can use double-quotes on the inside. This in part because...
• Don't use expanding variables ("WHERE id = $id "), it's sloppy and can lead to security problems if you forget to surround the variable in matching quotes.
• Don't trust data from anybody - yourself included. Escape and and never assume.
• Use a consistent variable name, like $sql_query, so it's clear what the contents are. $q and other one-letter variables are hard to read.

Here's an example, which for the purposes of demonstration I'm acting as if $author_name has been properly escaped.

Standardized:

$sql_query = 'SELECT books.book_name ';
$sql_query .= 'FROM books ';
$sql_query .= 'INNER JOIN authors ON books.author_id = authors.author_id ';
$sql_query .= 'WHERE authors.author_name = "' . $author_name . '" ';
$sql_query .= 'ORDER BY book_name ASC ';

In contrast:

$q = "select t1.book_name as name from books as t1,authors as t2 where author_name='$author_name' and t1.book_id=t2.book_id order by name asc";

They're basically functionally equivalent, but the standardized approach is a lot easier to read and maintain in my opinion.

Again, there is no one right answer or approach, but from my experience applying these values have worked quite well for myself and teams that I've worked with.