CODEWITHSUNDEEP
phpmysqlmysqliprepared-statement
Kellen Stuart
Kellen Stuart

Reputation: 8913

Mysqli Prepared Statement Fails On Second Iteration

I'm having trouble with my prepared statement not working on the second iteration and I was wondering if I need to free it or something I'm missing?

 // $sheetData an array created from an Excel Sheet
for($i=0; $i <= count($sheetData); $i++) {
     $sql;
     $sql .= "INSERT INTO equipment";
     $sql .= "(tag,date)";
     $sql .= "VALUES(?,?)";
     $sql .= "ON DUPLICATE KEY UPDATE number=VALUES(tag), date=VALUES(date)";

     if($stmt = $db->prepare($sql)) {
          if(Utility::filterNull($sheetData[$i][$idxOfTag]),Utility::convertDT($sheetData[$i][$idxOfDate]) != "1969-12-31 23:59:59") {
                $stmt->bind_param("ss",$sheetData[$i][$idxOfTag],$sheetData[$i][$idxOfDate]);
                $stmt->execute();
          }
      } else {
            DebugLog($debugFileName,"Prepared Statement is false. Error: " . $stmt->error);
            DebugLog($debugFileName,"i = " . $i);
      }
} // i loop

It returns an error only on the second iteration.

DebugLog($debugFileName,$db->error);
= 
You have an error in your SQL syntax; 
check the manual that corresponds to your 
MySQL server version for the right syntax 
to use near 'INSERT INTO equipment (tag,date' at line 1

So basically in my database it will add the very first row of the spreadsheet and nothing else.

It is not a syntax error, because the first iteration works.

Any ideas?

Been stuck on this for at least 2-3 hours by now.

Upvotes: 0

Views: 70

Answers (1)

Sablefoste
Sablefoste

Reputation: 4192

Move the $sql definition outside of the for statement:

 $sql ='';
 $sql .= "INSERT INTO equipment";
 $sql .= "(tag,date)";
 $sql .= "VALUES(?,?)";
 $sql .= "ON DUPLICATE KEY UPDATE number=VALUES(tag), date=VALUES(date)";
for($i=0; $i <= count($sheetData); $i++) {
// removed from here

 if($stmt = $db->prepare($sql)) {
      if(Utility::filterNull($sheetData[$i][$idxOfTag]),Utility::convertDT($sheetData[$i][$idxOfDate]) != "1969-12-31 23:59:59") {
            $stmt->bind_param("ss",$sheetData[$i][$idxOfTag],$sheetData[$i][$idxOfDate]);
            $stmt->execute();
      }
  } else {
        DebugLog($debugFileName,"Prepared Statement is false. Error: " . $stmt->error);
        DebugLog($debugFileName,"i = " . $i);
  }

} // i loop

Otherwise, it keeps adding additional statements on each iteration.

Upvotes: 1

Related Questions