is it okay if i put all my db queries in one file?

Question

i have function file called models.php, which stores all my database functions, for example

// get the updates for the $user_id profile
function getProfileUpdates($user_id) {

    $query="SELECT m . * , u.picture, u.username
            FROM notes m, user u
            WHERE m.user_id = u.user_id
            AND u.user_id ='$user_id'
            ORDER BY m.dt DESC
            LIMIT 10";
    $result = mysql_query($query);
    return $result;



}
function getTopicId($topic){


              $query="SELECT id
                     FROM topic
                     WHERE topic ='$topic'
                      ";

              $result = mysql_query($query);

              $row = mysql_fetch_array($result);

              return $row['id'];
          }

is it okay to have all my mysql queries in one file, so then i can use any of them whenever i want during developing my web app, is this good practice, or is thier a better alternative.

p.s. my main concern is functioniality and performance, code readability is not an issue right now!

Answer 1

I prefer object-oriented programming for this sort of thing, e.g. $user->getProfileUpdates(), but seems like an agreeable solution for code re-use.

Watch out for the part where you have to start specifying large options arrays, though. What if you eventually decide that you might want a different order or limit on profile updates, depending on the context? Expect to expand what you have.

Answer 2

The word "all" worries me. Is it okay to have functions like getProfileUpdates that you can call from throughout the application to get a list of all the profile updates? Absolutely!

Do you want an absolute rule that 100% of all your SQL statements must always be written as functions in that file? Absolutely not!

While some queries (like fetching the ID of a topic) are common and reusable and likely to be needed in many different places in the application, other queries will be specialized to the task a particular script is performing. Don't add confusion by deliberately relocating those to functions in your "all SQL statements" library.

Answer 3

Yes, separate all database interfacing code out. This is a recommended practice. It aids reuse, readability and also decouples your application logic from handling the storage backend. Also, there is usually no performance penalty in doing so.

A theoretical advantage is that you could transition to a different database methodology if all your functionality is abstracted away. In practice it's hard to transition off a SQL backend, but at least your API would allow it.

Two notes of concern: you can never move out all database queries. There are often performance or parameter constraints, where it makes sense to retain SQL queries mixed within your application code. But try to move out as much as you deem sensible.

Second: try to retire mysql_query. It's not only simpler but also more secure to use PDO (which also exists emulated for PHP4). As example your first function could be simplified to:

function getProfileUpdates($user_id) {
    global $db;
    $s = $db->prepare("SELECT m . * , u.picture, u.username
        FROM notes m, user u
        WHERE m.user_id = u.user_id
        AND u.user_id = ?
        ORDER BY m.dt DESC LIMIT 10");
    $s->execute($user_id)
    return $s->fetch();
}

Where $user_id was added as parameter, and in the SQL replaced by a ? placeholder.