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

Question

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 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.

Answer 2

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 3

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 4

Once you get to a certain level, you realise that 99% of the SQL you write could be automated. If you write so much queries that you think of a properties file, you're probably doing something that could be simpler:

Most of the stuff we programmers do is CRUD: Create Read Update Delete

As a tool for myself, I built Pork.dbObject. Object Relation Mapper + Active Record in 2 simple classes (Database Abstraction + dbObject class)

A couple of examples from my site:

Create a weblog:

    $weblog = new Weblog(); // create an empty object to work with. 
    $weblog->Author = 'SchizoDuckie'; // mapped internally to strAuthor. 
    $weblog->Title = 'A test weblog';  
    $weblog->Story = 'This is a test weblog!'; 
    $weblog->Posted = date("Y-m-d H:i:s"); 
    $weblog->Save(); // Checks for any changed values and inserts or updates into DB. 
    echo ($weblog->ID) // outputs: 1 

And one reply to it:

    $reply = new Reply(); 
    $reply->Author = 'Some random guy'; 
    $reply->Reply = 'w000t'; 
    $reply->Posted = date("Y-m-d H:i:s"); 
    $reply->IP = '127.0.0.1'; 
    $reply->Connect($weblog); // auto-saves $reply and connects it to $weblog->ID 

And, fetch and display the weblog + all replies:

    $weblog = new Weblog(1); //Fetches the row with primary key 1 from table weblogs and hooks it's values into $weblog;

    echo("<h1>{$weblog->Title}</h1> 
    <h3>Posted by {$weblog->Author} @ {$weblog->Posted}</h3> 
    <div class='weblogpost'>{$weblog->Story}</div>"); 

    // now fetch the connected posts. this is the real magic: 
    $replies = $weblog->Find("Reply"); // fetches a pre-filled array of Reply objects. 
    if ($replies != false) 
    { 
        foreach($replies as $reply) 
        { 
            echo("<div class='weblogreply'><h4>By {$reply->Author} @ {$reply->Posted}</h4> {$reply->Reply}</div>"); 
        } 
    } 

The weblog object would look like this:

class Weblog extends dbObject 
{ 
    function __construct($ID=false) 
    { 
        $this->__setupDatabase('blogs', // database table 
        array('ID_Blog' => 'ID',    // database field => mapped object property 
            'strPost' => 'Story',    // as you can see, database field strPost is mapped to $this->Story 
            'datPosted' => 'Posted', 
            'strPoster' => 'Author', 
            'strTitle'  => 'Title',
            'ipAddress'  => 'IpAddress', 
            'ID_Blog',    // primary table key  
            $ID);    // value of primary key to init with (can be false for new empty object / row) 
        $this->addRelation('Reaction'); // define a 1:many relation to Reaction 

    }
}

See, no manual SQL writing :) Link + more examples: Pork.dbObject

Oh yeah i also created a rudimentary GUI for my scaffolding tool: Pork.Generator

Answer 5

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 6

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 7

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

Safe from MySQL injection

Answer 8

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 9

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 10

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)