Conditional select queries with PDO

Question

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

prepare() returns a PDOStatement object which is where your exception is coming from, it's not a string. You should only pass prepare() the full query, i.e. generate the SQL query (with placeholders) into a string, then run prepare() passing the completed query string. — Jonnix
– Jonnix, Commented Oct 1, 2015 at 13:26
You need to assemble your query before calling $conn->prepare... $querydatahora isn't a string like "SELECT..." as you might think but an object htat received a prepared statement based on that string. — Julio Soares
– Julio Soares, Commented Oct 1, 2015 at 13:28

rray · Accepted Answer · 2015-10-01 14:08:38Z

3

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

edited Oct 1, 2015 at 14:08

answered Oct 1, 2015 at 13:37

rray

2,5461 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vallete7 Over a year ago

Thanks for the answer. I've tried that example but i got the following errors: Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xxxxxxxxxxxxx.php on line 61 Notice: Undefined variable: querycount in C:\xxxxxxxxxxxxxx.php on line 67 Fatal error: Call to a member function execute() on null in C:xxxxxxxxxxxxx.php on line 67

Bang · Accepted Answer · 2015-10-01 13:58:53Z

1

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

edited Oct 1, 2015 at 13:58

answered Oct 1, 2015 at 13:43

Bang

9394 silver badges11 bronze badges

11 Comments

vallete7 Over a year ago

Fatal error: Call to a member function execute() on string in line "$querydatahora->execute($params);"

Bang Over a year ago

Updated: replace $querydatahora->execute with $conn->execute

Jonnix Over a year ago

$stmt = $conn->prepare(...); $stmt->execute(...) you mean?

Bang Over a year ago

$querydatahora is a string, you can use fetchColumn on the statement : $stmt->fetchColumn()

Bang Over a year ago

use $conn->quote($_SESSION['datetimepicker'])

|

Collectives™ on Stack Overflow

Conditional select queries with PDO

2 Answers 2

1 Comment

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related