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.