Difficulty listing a invoice and products using PHP and Mysql

Question

I’m having trouble with this page right below. My difficulty is in displaying the products of a specific invoice. Because in statment SELECT * FROM produtos WHERE = ? does not return anything! But when I put the number physically (SELECT * FROM produtos WHERE = 4519) he displays normally!

Here’s the code:

<code>
    include 'banco.php';
    $nota = $_GET['nota'];
    $pdoo = Banco::conectar();
    $sqll = "SELECT * FROM produtos where numnota = ?";
    $qq = $pdoo->prepare($sqll);
    $qq->execute(array($nota));
    $dataa = $qq->fetch(PDO::FETCH_ASSOC);
    print_r($dataa);

    foreach ($pdoo->query($sqll)as $row) {  
        echo '<tr>';
        echo '<td>'.$row['descricao'].'</td>';
        echo '<td>'.$row['unidade'].'</td>';
        echo '<td>'.number_format($row['qtde'],"2",",",".").'</td>';
        echo '<td>R$ '.number_format($row['valunit'],"2",",",".").'</td>';
        echo '<td>R$ '.number_format($row['desconto'],"2",",",".").'</td>';
        echo '<td>R$ '.number_format($row['total'],"2",",",".").'</td>';
        echo '</tr>';
    }
    Banco::desconectar();
</code>

Answer 1

The problem is occurring on the following line:

foreach ($pdoo->query($sqll)as $row) {

Just replace it with:

foreach ($dataa as $row) {

I looked here and there is another problem. You want all the products of a certain note. So instead of using fetch:

$dataa = $qq->fetch(PDO::FETCH_ASSOC);

Use fetchAll to bring all lines:

$dataa = $qq->fetchAll(PDO::FETCH_ASSOC);

Why the problem is occurring

When executing the query method it only executes its select. It does not take into account the parameter substitution. That’s why it is recommended to use execute. See a brief description of the two:

query performs a standard SQL statement and requires you to correctly escape all data to avoid Injection.

execute executes a prepared instruction that allows linking parameters to avoid the need to escape them.

Your corrected solution would look like this:

include 'banco.php';
$nota = $_GET['nota'];
$pdoo = Banco::conectar();
$sqll = "SELECT * FROM produtos where numnota = ?";
$qq = $pdoo->prepare($sqll);
$qq->execute(array($nota));
$dataa = $qq->fetchAll(PDO::FETCH_ASSOC);
print_r($dataa);

foreach ($dataa as $row) {  
    echo '<tr>';
    echo '<td>'.$row['descricao'].'</td>';
    echo '<td>'.$row['unidade'].'</td>';
    echo '<td>'.number_format($row['qtde'],"2",",",".").'</td>';
    echo '<td>R$ '.number_format($row['valunit'],"2",",",".").'</td>';
    echo '<td>R$ '.number_format($row['desconto'],"2",",",".").'</td>';
    echo '<td>R$ '.number_format($row['total'],"2",",",".").'</td>';
    echo '</tr>';
}
Banco::desconectar();