PHP SQLite - 准备好的语句行为不端？

Question

I have the following SQLite table

CREATE TABLE keywords
(
 id INTEGER PRIMARY KEY,
 lang INTEGER NOT NULL,
 kwd TEXT NOT NULL,
 count INTEGER NOT NULL DEFAULT 0,
 locs TEXT NOT NULL DEFAULT '{}'
);

CREATE UNIQUE INDEX  kwd ON keywords(lang,kwd);

Working in PHP I typically need to insert keywords in this table, or update the row count if the keyword already exists. Take an example

$langs = array(0,1,2,3,4,5);
$kwds = array('noel,canard,foie gras','','','','','');

I now these data run through the following code

 $len = count($langs);
 $klen = count($kwds);
 $klen = ($klen < $len)?$klen:$len;

 $sqlite = new SQLite3('/path/to/keywords.sqlite');
 $iStmt = $sqlite->prepare("INSERT OR IGNORE INTO keywords (lang,kwd) 
 VALUES(:lang,:kwd)");
 $sStmt = $sqlite->prepare("SELECT rowid FROM keywords WHERE lang = :lang 
 AND kwd = :kwd");

 if (!$iStmt || !$sStmt) return;

 for($i=0;$i < $klen;$i++)
 {
  $keywords = $kwds[$i];
  if (0 === strlen($keywords)) continue;
  $lang = intval($langs[$i]);


  $keywords = explode(',',$keywords);
  for($j=0;$j < count($keywords);$j++)
  {
   $keyword = $keywords[$j];
   if (0 === strlen($keyword)) continue;

   $iStmt->bindValue(':kwd',$keyword,SQLITE3_TEXT);
   $iStmt->bindValue(':lang',$lang,SQLITE3_INTEGER);
   $sStmt->bindValue(':lang',$lang,SQLITE3_INTEGER);
   $sStmt->bindValue(':kwd',$keyword,SQLITE3_TEXT);

   trigger_error($keyword);
   $iStmt->execute();
   $sqlite->exec("UPDATE keywords SET count = count + 1 WHERE lang = 
   '{$lang}' AND kwd = '{$keyword}';");

   $rslt = $sStmt->execute();
   trigger_error($sqlite->lastErrorMsg());
   trigger_error(json_encode($rslt->fetchArray()));
  }
 }

which generates the following trigger_error output

Keyword: noel Last error: not an error SELECT Result: {"0":1,"id":1}

Keyword: canard Last Error: not an error
SELECT Reult:false

Keyword:foiegras Last Error: not an error SELECT Result: false

From the SQLite command line I see that the three row entries are present and correct in the table with the id/rowid columns set to 1, 2 and 3 respectively. lastErrorMsg does not report an error and yet two of the three $rslt->fetchArray() statements are returning false as opposed to an array with rowid/id attributes. So what am I doing wrong here?

---

I investigated this a bit more and found the underlying case. In my original code the result from the first SQLite3::execute - $iStmt-execute() - was not being assigned to anything. I did not see any particular reason for fetching and interpreting that result. When I changed that line of code to read $rslt = $iStmt->execute() I got the expected result - the rowid/id of the three rows that get inserted was correctly reported.

It is as though internally the PHP SQLite3 extension buffers the result from SQLiteStatement::execute function calls. When I was skipping the assignment my next effort at running such a statement, $sStmt->execute() was in effect fetching the previous result. This is my interpretation without knowing the inner workings of the PHP SQLite3 extension. Perhaps someone who understands the extension better would like to comment.

Answer 1

Add $rslt = NONE; right after trigger_error(json_encode($rslt->fetchArray())); and the correct results appear.

FetchArray can only be called once and somehow php is not detecting that the variable has changed. I also played with changing bindValue to bindParam and moving that before the loop but that is unrelated to the main issue.

It is my opinion that my solution should not work unless there is a bug in php. I am too new at the language to feel confident in that opinion and would like help verifying it. Okay, not a bug, but a violation of the least surprise principle. The object still exists in memory so without finalizing it or resetting the variable, fetch array isn't triggering.