如何在php脚本中嵌入sql查询(编码式)？

Question

如何在php中嵌入你的sql脚本？您只是将它们写在字符串或them文档中，还是将它们外包到sql文件中？什么时候外包他们有什么最佳实践吗？有没有一种优雅的方式来组织这件事？

Answer 1

使用具有ORM (对象关系映射)层的框架。这样，您就不必直接将SQL anywhere放在一起。嵌入式SQL在可读性、可维护性和其他方面都很糟糕。

Answer 2

始终记住转义输入。不要手动执行此操作，请使用预准备语句。下面是我的报告类中的一个示例方法。

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();
}

在标记为(1)、(2)和(3)的行中，您将看到一种条件绑定的方法。

对于简单的查询，我使用ORM框架来减少手动构建SQL的需要。

Answer 3

这取决于查询的大小和难度。

我个人喜欢heredocs。但我不会将它用于简单的查询。这并不重要。主要的事情是“永远不要忘记转义值”