PHP storing db queries in an include file how?

Question

Hi, I'm just learning PHP and I'm trying to figure out how I could store my database queries in a separate file either as part of a class, method or just as simple variables.

For example: I have a query that looks like this:

$checkuser = mysql_query("SELECT UserName, Email FROM users 
                            WHERE UserName = '$username4db' OR EMAIL = '$email4db'") 
             or die($checkuser_error);

Instead of writing it inline within the file, I'd like to move this to a central place and then use them from there.

I tried using an include file and simply calling the variable in place but it doesn't work.

Can some one help please? Thanks!

Answer 1

Storing the queries at a central place makes hardly sense IMO, as they will always be part of the program logic, and you will need to see them (and alter them) when you work on the program's flow.

You could put something together with queries containing placeholders that you then fill in using sprintf(), but I don't really see the point.

Answer 2

You could simply store the queries as strings in a separate file, and then call those string variables when you want to use the query. However, I'm guessing most of your queries will require arguments - in which case you would need to create a series of functions (with or without a class) to build the queries with the appropriate variables.

Answer 3

I find the "one query file" paradigm very interesting. I think it makes more sense to just keep the queries where you want to get the data because that is where you expect to find it. Instead, others thing it is a good idea to put all of the queries in a central location that you can always refer back to, even when it gets to 5000 lines. I can't say that there is a right answer.

You could assign a function per query instead. Just have a file that includes all the queries you want each defined in functions. So when you want some specific query from your included file you just run get_random_data_query_uno() or what have you.

Answer 4

You should look at the MVC design pattern, the whole purpose is to provide a clean way of organizing code based on function.

The models are where your database interaction code goes. Depending on the MVC implementation this is sometimes a direct representation of the database table, or it could be a collection of functions you write yourself to deal with the problem at hand.

The views are for display code. As a general rule when writing views only HTML and basic PHP (echoing variables, a foreach loop to build a table) should go in the view.

The controller is where the program logic is placed. From the controller you call models to do the data processing necessary to build the data that is then passed into the view.

That's a very basic explanation, and always wikipedia has more to say on the topic: http://en.wikipedia.org/wiki/Model-view-controller.

There are many MVC frameworks out there in PHP that you can use to have a good starting point for building sites. I use and recommend CodeIgniter as it's very easy to use and flexible in its implementation.

Answer 5

Your question has both sense and nonsense.
You can use some ORM to encapsulate a query into some class. It will make sense.
But storing raw queries has no sense. Especially in a form you posted. All queries will be run unconditionally, no matter do you need this particular data, or not.
At lest encapsulate it in a function call

function checkuser($username4db, $email4db) {
  $sql = "SELECT UserName, Email FROM users WHERE UserName = %s OR EMAIL = %s";
  return db::getrow($sql, $username4db, $email4db); 
}

• in this case the query would be run only when checkuser() function is called.

but I still see no sense in storing all site queries in one file. Why not to store it logically, based on what site part this query belongs to?