How to perform a bind_param () with SELECT UNION ALL

Question

How to perform a bind_param () with SELECT UNION ALL

Navigation

#1 by (3 votes)

-1

I have this code.

<?php
$link = new mysqli("localhost","root", "SENHAAAA", "BANCO");
$sql_saldo_total = $link->prepare(
   "SELECT sum(g) saldo_total 
      FROM ( SELECT SUM(valor_pg) g 
         FROM tabela1 
         WHERE departamento = ? 
         AND MONTH(data_pg) <= ? 
         AND YEAR(data_pg) = ? 
         AND
         UNION ALL
         SELECT SUM(valor) 
         FROM tabela2 
         WHERE tipo = ? 
         AND MONTH(data) <= ? 
         AND YEAR(data) = ?
      ) tabela_virtual");

$sql_saldo_total->bind_param("siisii",
$departamento, 
$mes, 
$ano, 
$departamento, 
$mes, 
$ano);
?>

But it is returning the error:

Fatal error: Call to a member function bind_param() on a non-object in

php mysql

asked by anonymous 05.07.2017 / 23:07

1 answer

Button when clicked has effect on another Enable a button after entering password

score 3 · Answer 1

Your problem is that in your SQL in part:

AND YEAR(data_pg) = ? AND UNION ALL

There is an AND before UNION ALL and therefore generates error. Because there was a failure in preparing SQL.

A good practice is to test each step. Checking for possible errors and making it easier to locate the problem.

Follow the solution in SQL and improve the source code:

<?php
    $link = new mysqli("localhost","root", "SENHAAAA", "BANCO");
    if ($link->connect_errno) {
         die ("Falha ao conectar ao MySQL: (" . $link->connect_errno . ") " . 
               $link->connect_error);
   }

    $sql_saldo_total = $link->prepare(
            "SELECT sum(g) saldo_total 
               FROM ( SELECT SUM(valor_pg) g 
                        FROM tabela1 
                       WHERE departamento = ? 
                         AND MONTH(data_pg) <= ? 
                         AND YEAR(data_pg) = ? 
                       UNION ALL
                      SELECT SUM(valor) 
                        FROM tabela2 
                       WHERE tipo = ? 
                         AND MONTH(data) <= ? 
                         AND YEAR(data) = ?
                    ) tabela_virtual");

     if (!$sql_saldo_total) {
        die("Falha ao preparar o SQL: (" . $link->errno . ") " . $link->error);
     }

     if (!$sql_saldo_total->bind_param("siisii",
                                  $departamento, 
                                  $mes, 
                                  $ano, 
                                  $departamento, 
                                  $mes, 
                                  $ano)) {
         die ("Falha ao associar os parâmetros: (" . $sql_saldo_total->errno . ") " . 
               $sql_saldo_total->error);
     }
?>