0

I'm new in Stackoverflow and I'm sorry if I might not be clear with my question. I don't understand what's wrong in my code and I would like to have your help. This is the php code snippet:

$giorno = "2023-06-12";
$arraySezioni = [1,4,7,9];

//PRIMO SELECT
try {
    echo "Primo select: <br>";
    $q = "SELECT ";
    for ($j = 0; $j < count($arraySezioni); $j++) {
        if ($j != count($arraySezioni) - 1)
            $q .= "tavoli_sez:plArraySez$j, ";
        else
            $q .= "tavoli_sez:plArraySez$j ";
    }
    $q .= "FROM tavoli WHERE giorno = ':plGiorno';";

    echo "Query: $q";

    $st = $pdoConn->prepare($q);
    for ($j = 0; $j < count($arraySezioni); $j++) {
        $st->bindParam(":plArraySez$j", $arraySezioni[$j], PDO::PARAM_INT);
    }
    $st->bindParam(":plGiorno", $giorno, PDO::PARAM_STR);
    $st->execute();
    $arrayTavoli = $st->fetchAll(PDO::FETCH_NUM)[0];
    var_dump($arrayTavoli);

    $q = "UPDATE tavoli SET ";

    for ($j = 0; $j < count($arraySezioni); $j++) {
        if ($j != count($arraySezioni) - 1)
            $q .= "tavoli_sez:plArraySez$j=:plArrayTavoli$j-1, ";
        else
            $q .= "tavoli_sez:plArraySez$j=:plArrayTavoli$j-1 ";
    }
    $q .= "WHERE giorno = :plGiorno";

    $st = $pdoConn->prepare($q);
    for ($j = 0; $j < count($arraySezioni); $j++) {
        $st->bindParam(":plArraySez$j", $arraySezioni[$j], PDO::PARAM_INT);
        $st->bindParam(":plArrayTavoli$j-1", $arrayTavoli[$j] - 1, PDO::PARAM_INT);
    }
    $st->bindParam(":plGiorno", $giorno, PDO::PARAM_STR);
    $st->execute();

    echo "Query: $q <br>";
    if ($st->rowCount() > 0) {
        echo "Modifica avvenuta <br>";
    } else {
        echo "Modifica non avvenuta <br>";
    }

    //SELECT PER VEDERE LA MODIFICA
    echo "Secondo select per vedere la modifica: <br>";
    $q = "SELECT ";
    for ($j = 0; $j < count($arraySezioni); $j++) {
        if ($j != count($arraySezioni) - 1)
            $q .= "tavoli_sez:plArraySez$j, ";
        else
            $q .= "tavoli_sez:plArraySez$j ";
    }
    $q .= "FROM tavoli WHERE giorno = ':plGiorno';";

    $st = $pdoConn->prepare($q);
    for ($j = 0; $j < count($arraySezioni); $j++) {
        $st->bindParam(":plArraySez$j", $arraySezioni[$j], PDO::PARAM_INT);
    }
    $st->bindParam(":plGiorno", $giorno, PDO::PARAM_STR);
    $st->execute();
    $arrayTavoli = $st->fetchAll(PDO::FETCH_NUM)[0];
    var_dump($arrayTavoli);
} catch (PDOException $e){
    echo "Avvenuta PDOException: ".$e->getMessage();
}

and this is the output in the browser: output of the php program

At the beginning I've wrote this code without "try catch" and the exception was thrown in the first query (the first select), at the line $st->execute(). I've tried to write the placeholder in a different variable, but I've got the same result. I've tried to use some var_dump and echoes to find the problem but I don't understand where it could be. For example: I've created an array where I've put the $st->bindParam(...) values (in the first query), and I've obtained a bool array composed by 5 true. So maybe the problem can be in the $q variable and not in the bind param, but I'm not sure.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Dany
  • 1
  • 1
    Your query includes four columns to be selected, each of which has a name of the form `tavoli_sez:plArraySez1. The colon `:` is not a valid character in unquoted identifiers, so it's likely that PDO is taking the :plArraySez0 as a placeholder, and thus counting 5 placeholders but only one bound variable. – Tangentially Perpendicular Jun 13 '23 at 01:48
  • You know you're not supposed to put parameter placeholders inside quotes, right? – Bill Karwin Jun 13 '23 at 02:25
  • Does this answer your question? [PHP PDOException: "SQLSTATE\[HY093\]: Invalid parameter number"](https://stackoverflow.com/questions/18028706/php-pdoexception-sqlstatehy093-invalid-parameter-number) – Ron van der Heijden Jun 13 '23 at 09:13

0 Answers0