How do you embeded your sql queries in php scripts (coding-style)?

Question

Hi,

how do you embed your sql scripts in php? Do you just write them in a string or a heredoc or do you outsource them to a sql file? Are there any best practices when to outsource them ? Is there an elegant way to organize this?

Answer 1

I normally write them as function argument:

db_exec ("SELECT ...");

Except cases when sql gonna be very large, I pass it as variable:

$SQL = "SELECT ...";
$result = db_exec ($SQL);

(I use wrapper-functions or objects for database operations)

Answer 2

It depends on a query size and difficulty.

I personally like heredocs. But I don't use it for a simple queries. That is not important. The main thing is "Never forget to escape values"

Answer 3

Use a framework with an ORM (Object-Relational Mapping) layer. That way you don't have to put straight SQL anywhere. Embedded SQL sucks for readability, maintenance and everything.

Answer 4

$sql = sprintf("SELECT * FROM users WHERE id = %d", mysql_real_escape_string($_GET["id"]));

Safe from MySQL injection

Answer 5

You should always really really ALWAYS use prepare statements with place holders for your variables.

Its slightly more code, but it runs more efficiently on most DBs and protects you against SQL injection attacks.

Answer 6

Always remember to escape input. Don't do it manually, use prepared statements. Here is an example method from my reporting class.

public function getTasksReport($rmId, $stage, $mmcName) {
    $rmCondition = $rmId ? 'mud.manager_id = :rmId' : 'TRUE';
    $stageCondition = $stage ? 't.stage_id = :stageId' : 'TRUE';
    $mmcCondition = $mmcName ? 'mmcs.username = :mmcName' : 'TRUE';
    $sql = "
            SELECT
                    mmcs.id AS mmc_id,
                    mmcs.username AS mmcname,
                    mud.band_name AS mmc_name,
                    t.id AS task_id,
                    t.name AS task, 
                    t.stage_id AS stage,
                    t.role_id,
                    tl.id AS task_log_id,
                    mr.role,
                    u.id AS user_id,
                    u.username AS username,
                    COALESCE(cud.full_name, bud.band_name) AS user_name,
                    DATE_FORMAT(tl.completed_on, '%d-%m-%Y %T') AS completed_on,
                    tl.url AS url,
                    mud.manager_id AS rm_id
            FROM users AS mmcs
            INNER JOIN banduserdetails AS mud ON mud.user_id = mmcs.id
            LEFT JOIN tasks AS t ON 1
            LEFT JOIN task_log AS tl ON tl.task_id = t.id AND tl.mmc_id = mmcs.id
            LEFT JOIN mmc_roles AS mr ON mr.id = t.role_id
            LEFT JOIN users AS u ON u.id = tl.user_id
            LEFT JOIN communityuserdetails AS cud ON cud.user_id = u.id
            LEFT JOIN banduserdetails AS bud ON bud.user_id = u.id
            WHERE mmcs.user_type = 'mmc'
                    AND $rmCondition
                    AND $stageCondition
                    AND $mmcCondition
            ORDER BY mmcs.id, t.stage_id, t.role_id, t.task_order
    ";
    $pdo = new PDO(.....);
    $stmt = $pdo->prepare($sql);
    $rmId and $stmt->bindValue('rmId', $rmId); // (1)
    $stage and $stmt->bindValue('stageId', $stage); // (2)
    $mmcName and $stmt->bindValue('mmcName', $mmcName); // (3)
    $stmt->execute();
    return $stmt->fetchAll();
}

In lines marked (1), (2), and (3) you will see a way for conditional binding.

For simple queries I use ORM framework to reduce the need for building SQL manually.

Answer 7

You could use an ORM or an sql string builder, but some complex queries necessitate writing sql. When writing sql, as Michał Słaby illustrates, use query bindings. Query bindings prevent sql injection and maintain readability. As for where to put your queries: use model classes.

Answer 8

I prefer as such:

$sql = "SELECT tbl1.col1, tbl1.col2, tbl2.col1, tbl2.col2"
        . " FROM Table1 tbl1"
        . " INNER JOIN Table2 tbl2 ON tbl1.id = tbl2.other_id"
        . " WHERE tbl2.id = ?"
        . " ORDER BY tbl2.col1, tbl2.col2"
        . " LIMIT 10, 0";

It might take PHP a tiny bit longer to concatenate all the strings but I think it looks a lot nicer and is easier to edit.

Certainly for extremely long and specialized queries it would make sense to read a .sql file or use a stored procedure. Depending on your framework this could be as simple as:

$sql = (string) View::factory('sql/myfile');

(giving you the option to assign variables in the view/template if necessary). Without help from a templating engine or framework, you'd use:

$sql = file_get_contents("myfile.sql");

Hope this helps.

Answer 9

I like this format. It was mentioned in a previous comment, but the alignment seemed off to me.

$query = "SELECT "
       . " foo, "
       . " bar "
       . "FROM "
       . " mytable "
       . "WHERE "
       . " id = $userid";

Easy enough to read and understand. The dots line up with the equals sign keeping everything in a clean line.

I like the idea of keeping your SQL in a separate file too, although I'm not sure how that would work with variables like $userid in my example above.