PHP/MySQL: How to Store Queries in a Separate Include File

October 14, 2009DavidPHP, Programming2

When implementing a php/mysql database (or any database for that matter), it’s sometimes easy to have all of the queries located in one place, as opposed to having them hard-coded into the pages. If the queries are hard-coded in, and the queries are reused on multiple pages (which they often are), it can be a cumbersome task to update all of the queries. Having hard-coded queries also makes for larger, bulkier pages, which makes it harder to visualize the rest of the code on the page.

The following method is a simple solution to store all of your queries in one file and access them as needed. Please note that I am not advocating this as a “best practice” as there are many great articles on how to truly separate your presentation layer and your data layer, but this works.

I always have a database.php file that stores the connection data and has the related connection functions. So to continue with this “separation” concept, create a queries.php file.

To this file I simply add a variable that stores the specific query. So for example, I would add the following variables:

$getAuthors = "SELECT * FROM authors";
$getBooks = "SELECT * FROM book";

and so on with your queries.

Turning to the page that displays the results — the one that actually uses the query — (lets call it authors.php), I have the include statement:

include "queries.php";

and of course the code to access my database:

link_id = db_connect();
[here you can use whatever particular code you personally use to connect to a mysql database]

Then I simply populate the $sql variable (where I previously would have stored the actual code of the query), with the following:

$sql = $getAuthors;

To show you where it goes, I’m using the code: $result=mysql_query($sql) or DIE(mysql_error());

Again, the specifics don’t really matter other than that instead of hard-coding the sql query text for the $sql variable, you are simply referencing a variable in another file.

Now if you decide to update, change or modify the query, you can do it in one place — in the queries.php file.

So while this may not be the best method of handling this situation, it does work, and it does save time.

Tagged , , ,

Related Posts

| More

SpiderOak Online Backup, Storage, Access, Sync, and Sharing

We are currently offering a free copy of our 16-page eBook, "How to Organize your Digital Photography Collection" to everyone who signs up for our free newsletter. This eBook will review how to organize your digital photos, discuss metadata, and give you an overview of software than can help you with your digital photography workflow. Sign up in the signup area, located in the right-side column of every page on this site, and read your copy now!