Parent - child table in PHP PDO SQLite under transaction - No error but it doesn't work

Question

Good morning,
I wrote the code below that it's running on PHP with PDO and SQLite under transaction. Inexplicably it does not go into error but it does not even write the second table which results with all the fields of the records at null. Could you tell me what I'm doing wrong since I can't find any errors in the Apache2 logs? Thank you very much.

I create two tables in SQLite:

The first contains the questionary data;
The second contains the details information for every questionary

My SQL script to create DB:

CREATE TABLE "questionari" (
  "pkid_questionario"   INTEGER NOT NULL,
  "dt_registrazione"    TEXT NOT NULL,
  "matricola"   INTEGER NOT NULL,
  "cognome" TEXT NOT NULL,
  "nome"    TEXT NOT NULL,
  "email"   TEXT NOT NULL,
  "pk_localizzazione"   INTEGER NOT NULL,
  "desc_localizzazione" TEXT NOT NULL,
  PRIMARY KEY("pkid_questionario" AUTOINCREMENT)
);

CREATE TABLE "t1" (
  "idQuestionario"  INTEGER,
  "pkidBene"    INTEGER,
  "NumeroEtichetta" INTEGER,
  "DescBene"    TEXT,
  "EsistenzaBene"   TEXT,
  "EsistenzaEtichetta"  TEXT DEFAULT null,
  "txtNote" TEXT DEFAULT null,
  FOREIGN KEY("idQuestionario") REFERENCES "questionari"("pkid_questionario") 
    ON UPDATE CASCADE 
    ON DELETE CASCADE,
  PRIMARY KEY("pkidBene","idQuestionario")
);

My PHP code is this:

define("T1_PkID","t1_PkID_r");
define("T1_Etichetta", "t1_Etichetta_r");
define("T1_Desc", "t1_Desc_r");
define("T1_Bene_YN", "t1_Bene_YN_r");
define("T1_Etichetta_YN", "t1_Etichetta_YN_r");
define("T1_Note", "t1_Note_r");

//[Omissis...]
try{
$db = new SQLiteDB();
//$dbh = new PDO("sqlite:./DB/DB_Inventario.db");
$dbh = $db->connect();
  
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
            
$sql  = "INSERT INTO questionari ";
$sql .= "(dt_registrazione, matricola, cognome, nome, email, pk_localizzazione, desc_localizzazione) VALUES ";
$sql .= "(:dt_registrazione, :matricola, :cognome, :nome, :email, :pk_localizzazione, :desc_localizzazione)";
            
$stmt = $dbh->prepare($sql);
$dt_registrazione = date('Y/m/d H:i:s');
            
$stmt->bindParam(":dt_registrazione", $dt_registrazione);
$stmt->bindParam(":matricola", $matricola);
$stmt->bindParam(":cognome", $cognome);
$stmt->bindParam(":nome", $nome);
$stmt->bindParam(":email", $email);
$stmt->bindParam(":pk_localizzazione", $pkLocalizzazione);
$stmt->bindParam(":desc_localizzazione", $DescLocalizzazione);

$stmt->execute();
                
$lastID = $dbh->lastInsertId();

// t1_NumRows is the rows number to write for t1            
for($i=0;$i<$t1_NumRows;$i++){
  $sql  = "INSERT INTO t1 ";
  $sql .= "(idQuestionario, pkidBene, NumeroEtichetta, DescBene, EsistenzaBene, EsistenzaEtichetta, txtNote) VALUES ";
  $sql .= "(:p_idQuestionario, :p_pkidBene, :p_NumeroEtichetta, :p_DescBene, :p_EsistenzaBene, :p_EsistenzaEtichetta, :p_txtNote)";

  $stmt->bindParam(":p_idQuestionario", $lastID, PDO::PARAM_INT);
  $stmt->bindParam(":p_pkidBene", $_POST[T1_PkID.$i], PDO::PARAM_INT);
  $stmt->bindParam(":p_NumeroEtichetta", $_POST[T1_Etichetta.$i], PDO::PARAM_INT);
  $stmt->bindParam(":p_DescBene", $_POST[T1_Desc.$i], PDO::PARAM_STR, 500);
  $stmt->bindParam(":p_EsistenzaBene", $_POST[T1_Bene_YN.$i], PDO::PARAM_STR, 2);
                
  if($_POST[T1_Bene_YN . $i]=="NO")
    $stmt->bindParam(":p_EsistenzaEtichetta", null, PDO::PARAM_STR, 2);
  else
    $stmt->bindParam(":p_EsistenzaEtichetta", $_POST[T1_Etichetta_YN.$i], PDO::PARAM_STR,   2);

if(strlen(trim($_POST[T1_Note.$i]))==0)
  $stmt->bindParam(":p_txtNote", null, PDO::PARAM_STR, 500);
else
  $stmt->bindParam(":p_txtNote", $_POST[T1_Note.$i], PDO::PARAM_STR, 500);
                
$stmt = $dbh->prepare($sql);
$stmt->execute();
}
//[Omissis...]
$dbh->commit();  

// In case of exception make rollback!

This is the result on "t1" after record first questionary to have two details:

I don't see `$t1_NumRows` defined, so it will never enter the loop — aynber, Dec 20 '21 at 14:14
$ t1_NumRows is a value that is set by the form that loads $ _POST [] Represents the number of lines to be posted. In my test case it is 2. — Tonky75, Dec 20 '21 at 14:18
Try a bit of troubleshooting. Add some echoing or logging lines along the way, such as inside of your for loop. Check for [PDO errors](http://php.net/manual/en/pdo.error-handling.php) inside the loop to make sure the query is not failing. — aynber, Dec 20 '21 at 14:28

Tonky75 · Answer 1 · 2021-12-20T16:02:34.893

Ok, I did some debugging in my code and I found that the prepare statement must be inserted immediately after the definition of the parametric queries. At this point my code works: D: D

for($i=0;$i<$t1_NumRows;$i++){
  $sql  = "INSERT INTO t1 ";
  $sql .= "(idQuestionario, pkidBene, NumeroEtichetta, DescBene, EsistenzaBene, EsistenzaEtichetta, txtNote) VALUES ";
  $sql .= "(:p_idQuestionario, :p_pkidBene, :p_NumeroEtichetta, :p_DescBene, :p_EsistenzaBene, :p_EsistenzaEtichetta, :p_txtNote)";
  
  $stmt = $dbh->prepare($sql);

  $stmt->bindParam(":p_idQuestionario", $lastID, PDO::PARAM_INT);
  $stmt->bindParam(":p_pkidBene", $_POST[T1_PkID.$i], PDO::PARAM_INT);
  $stmt->bindParam(":p_NumeroEtichetta", $_POST[T1_Etichetta.$i], PDO::PARAM_INT);
  $stmt->bindParam(":p_DescBene", $_POST[T1_Desc.$i], PDO::PARAM_STR, 500);
  $stmt->bindParam(":p_EsistenzaBene", $_POST[T1_Bene_YN.$i], PDO::PARAM_STR, 2);
                
  if($_POST[T1_Bene_YN . $i]=="NO")
    $stmt->bindValue(":p_EsistenzaEtichetta", null, PDO::PARAM_STR, 2);
  else
    $stmt->bindParam(":p_EsistenzaEtichetta", $_POST[T1_Etichetta_YN.$i], PDO::PARAM_STR,   2);

if(strlen(trim($_POST[T1_Note.$i]))==0)
  $stmt->bindValue(":p_txtNote", null, PDO::PARAM_STR, 500);
else
  $stmt->bindParam(":p_txtNote", $_POST[T1_Note.$i], PDO::PARAM_STR, 500);
                
$stmt->execute();
}

Parent - child table in PHP PDO SQLite under transaction - No error but it doesn't work

1 Answers1