CODEWITHSUNDEEP
phpmysqlselectpdo
vallete7
vallete7

Reputation: 123

Conditional select queries with PDO

i had some php scripts working with mysql_query but now i was trying to change it to PDO (to be less vulnerable and more secure they say) but i'm having some problems with conditional select queries. I have the following code:

$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'])
);

The error i'm getting is:

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

I'm not an expert on this so, probably i'm making something wrong. All the help is appreciated

Upvotes: 2

Views: 574

Answers (2)

rray
rray

Reputation: 2556

You need first build the query and after that prepare it

$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);

Upvotes: 3

Bang
Bang

Reputation: 929

You have to build a query string first.

$querydatahora = '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'

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

// Test you have the mandatory variables IDLinha and IDMaquina
if (isset($_SESSION['IDLinha']) && isset($_SESSION['IDMaquina'])) {
    $querydatahora .= " AND Avarias.IDLinha = :IDLinha AND Avarias.IDMaquina = :IDMaquina");
    // Adding parameters
    $params = array_merge($params, array(':IDLinha' => $_SESSION['IDLinha'],':IDMaquina' => $_SESSION['IDMaquina']));
}

// In anyway you will do the same order by then write it at the end
$querydatahora .= " order by DataHoraInicioAvaria DESC LIMIT $startrow, 9";

// Your querystring is ok then let's prepare it
$stmt = $conn->prepare($querydatahora);

// Now Run with parameters
$stmt->execute($params);

I have removed your querycount because I want to focus on the first query and make it work

Upvotes: 1

Related Questions