php - 使用 PDO 的条件选择查询

Question

我有一些 php 脚本与 mysql_query 一起工作，但现在我试图将其更改为 PDO(他们说这样更不容易受到攻击并且更安全)但是我在条件选择查询方面遇到了一些问题。我有以下代码:

$querydatahora = $conn->prepare('SELECT 
    Linhas.NomeLinha, Maquinas.Nome as maquina, Tecnicos.Nome, Avarias.DataHoraInicioAvaria, 
    Avarias.DataHoraFimAvaria, Avarias.Descricao, Avarias.Solucao, Avarias.TipoSolucao 
    FROM Avarias, Tecnicos, Linhas, Maquinas, avariatecnico 
    where Linhas.IDLinha = Avarias.IDLinha and avariatecnico.IDAvaria = avarias.IDAvaria and Avariatecnico.IDTecnico = Tecnicos.IDTecnico and 
    Maquinas.IDMaquina = Avarias.IDMaquina and DataHoraInicioAvaria >= :datetimepicker AND DataHoraFimAvaria <= :datetimepicker1 ');


if( $_SESSION['IDLinha'] ) {
    $querydatahora .= $conn->prepare(" AND Avarias.IDLinha = :IDLinha AND Avarias.IDMaquina = :IDMaquina order by DataHoraInicioAvaria DESC LIMIT $startrow, 9");
} else{
    $querydatahora .= $conn->prepare(" order by DataHoraInicioAvaria DESC LIMIT $startrow, 9");
}

$querydatahora->execute( array(
    ':datetimepicker'   => $_SESSION['datetimepicker'], 
    ':datetimepicker1'  => $_SESSION['datetimepicker1'], 
    ':IDLinha'          => $_SESSION['IDLinha'], 
    ':IDMaquina'        => $_SESSION['IDMaquina'])
);

if( $_SESSION['IDLinha'] ) {
    $querycount .= $conn->prepare(' AND Avarias.IDLinha = :IDLinha AND Avarias.IDMaquina = :IDMaquina');
}

$querycount->execute( array(
    ':datetimepicker'   => $_SESSION['datetimepicker'], 
    ':datetimepicker1'  => $_SESSION['datetimepicker1'], 
    ':IDLinha'          => $_SESSION['IDLinha'], 
    ':IDMaquina'        => $_SESSION['IDMaquina'])
);

我得到的错误是:

Catchable fatal error: Object of class PDOStatement could not be converted to string in C:\xxxxxxxxxxxxxxxxxxxxxxxxxx.php on line 52

我不是这方面的专家，所以我可能做错了什么。感谢所有帮助

Answer 1

您需要先构建查询，然后再准备

$sql = 'SELECT Linhas.NomeLinha,
               Maquinas.Nome as maquina,
               Tecnicos.Nome, 
               Avarias.DataHoraInicioAvaria,
               Avarias.DataHoraFimAvaria,
               Avarias.Descricao,
               Avarias.Solucao, 
               Avarias.TipoSolucao 
        FROM Avarias, Tecnicos, Linhas, Maquinas, avariatecnico
        WHERE Linhas.IDLinha = Avarias.IDLinha
        AND avariatecnico.IDAvaria = avarias.IDAvaria
        AND Avariatecnico.IDTecnico = Tecnicos.IDTecnico
        AND Maquinas.IDMaquina = Avarias.IDMaquina
        AND DataHoraInicioAvaria >= :datetimepicker
        AND DataHoraFimAvaria <= :datetimepicker1 ';

if ($_SESSION['IDLinha']) {
    $querydatahora = $conn->prepare($sql." AND Avarias.IDLinha = :IDLinha
                                           AND Avarias.IDMaquina = :IDMaquina
                                           ORDER BY DataHoraInicioAvaria
                                           DESC LIMIT $startrow, 9");
}else{
    $querydatahora = $conn->prepare($sql." ORDER BY DataHoraInicioAvaria
                                           DESC LIMIT $startrow, 9");
}


$params = array(':datetimepicker' => $_SESSION['datetimepicker'],
                ':datetimepicker1' => $_SESSION['datetimepicker1'],
                ':IDLinha' => $_SESSION['IDLinha'],
                ':IDMaquina' => $_SESSION['IDMaquina']
                );

$querydatahora->execute($params);

$params = array(':datetimepicker' => $_SESSION['datetimepicker'],
               ':datetimepicker1' => $_SESSION['datetimepicker1']);

if ($_SESSION['IDLinha']) {
    $querycount = $conn->prepare($sql.' AND Avarias.IDLinha = :IDLinha 
                                        AND Avarias.IDMaquina = :IDMaquina');
    $params[':IDLinha'] = $_SESSION['IDLinha'];
    $params[':IDMaquina']  $_SESSION['IDMaquina'];
}

$querycount->execute($params);