So many young developers are given endless amounts of information without any legitimate guidelines on how to correctly write code and plan a project correctly. My goal with P5 (proper planning prevents poor performance) is to shed some light on some very basic yet often overlooked principles of developing on LAMP (Linux, Apache, MySQL, Perl/PHP).

This is admittedly for the beginner, so continue with that in mind.

———-

Nowadays developing without a database driving the functionality & back-end is signing the death of your project. With that in mind, be prepared to write hundreds, if not thousands, of queries throughout your application/website.

As with programming 101, always use a variable if the value will be repeated throughout your site. This makes for easy updates without having to trudge through thousands of lines of code to make changes, or misusing the Find/Replace to your demise (we’ve all done it!).

The same principle applies to writing a query. When you do, always use a variable for your table names. I personally recommend DEFINING all your tables, and also prefixing your tables with a relevant prefix. Especially if the table is related to a sub-application or section within a large project.

The code below would be placed in your config or common file.

$tbl_prefix = "litwicki_";
DEFINE("PRODUCTS_TABLE", $tbl_prefix . "products");

Following this declaration, you would write your query as such:

$sql = "SELECT * FROM ".PRODUCTS_TABLE." WHERE product_id=123";

This leads to a subsection of this topic: correctly writing queries. I don’t mean the syntax or join preferences (LEFT/INNER, etc). I mean the actual code itself and how it’s presented within your files. So many developers ignore the immeasurable value in writing queries correctly, especially for debugging, but also for development on teams. And if you plan on developing professionally, you will be working on teams.

Rather than writing your query on one line to condense it, write it as you would in MySQL Workbench or SQL Management Studio:

$sql = "SELECT
     p.product_id, p.product_name, p.date_added,
     o.order_id, o.order_date
FROM
     ".PRODUCTS_TABLE." p
     JOIN ".PRODUCT_ORDERS_TABLE." o ON o.product_id=p.product_id
WHERE
     order_id=100";

Now the query is completely readable, and debugging is exponentially simplified if you want to add additional tables to the join, modify the values selected, or comment out or add additional WHERE clause items.

It may seem simple and trivial, but 10,000 lines into your code when someone changes the schema of your tables, or asks you to debug why invoices aren’t printing the correct order_date, you will thank yourself for spending the time to do this. And your colleague will probably buy you lunch after he/she sees the work you put into your code!