Application case of PHP using PDO to operate sqlite database

This article illustrates how PHP uses PDO to operate an sqlite database. Share it for your reference, as follows:

1. Requirements:

Known:

1), 1 json file, inside is a 2-D array, the array parsed out as:

array (
   0 =>
   array (
    'title' => '9109',
   ),
   1 =>
   array (
    'title' => ' Computer 9109',
   ),
   2 =>
   array (
    'title' => ' Mobile phone 9109',
   ),
   3 =>
   array (
    'title' => ' Mobile phone computer 9109',
   ),
);

2) 1 sqlite database file 20180824. db Create a new sqlite database file

New Table report

Table Field id words time

Ask for:

Searching the data found from json in sqlite to judge whether it exists or not;
If it exists, add an word_sort field to sqlite, and write the number of title in the file (increment once, not the key value of the json file array) to the word_sort field

Thoughts:

① Get the contents of jsonlist. json file and json_decode($str,true) Convert to 2-D array
② Connect sqlite table
③ try{}catch(){} Add word_sort field to table
4. Array the data in json file
⑤ Loop 5000 pieces of json data every time, and query in report table with IN (title fields need splicing)
⑥ Splice the queried data with new sentences in batches of sql
⑦ try{}catch(){} Batch update of report table data
8 echo output operation result

2. PHP code (yaf framework):

<?php
/**
 * @todo  Word formation 
 * Class CommunityController
 */
class CombinwordController extends Rest{
  /**
   * @todo  Judge .json Whether the data exists, it exists. Put the data forward 
   * @linux 212 /usr/local/php7/bin/php /var/www/web/shop/public/cli.php request_uri="/v1/combinword/index"
   */
  public function indexAction(){
    set_time_limit ( 0 );  // Set the time out of time 
    $data = $this->getjson();  // Get json Data 
    $dbfile_path = APP_PATH.'/data/combinword/20180824.db';
    $db = new PDO("sqlite:{$dbfile_path}");
    // Setting a database handle      Attribute  PDO::ATTR_ERRMODE Error reporting.    PDO::ERRMODE_EXCEPTION:  Throw  exceptions  Abnormal. 
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // Plus combinword Field  START
    $add_filed = 'word_sort';
    $add_filed_sql = "alter table report add {$add_filed} TEXT(32)";
    try {
      $db->beginTransaction();// Start a transaction 
      $db->exec($add_filed_sql);  // Add field 
      $db->commit();// Commit transaction 
    }catch(PDOException $e){
      //$e->getMessage();// Gets the error message. 
      echo ' Field already exists '.PHP_EOL;
      $db->rollBack();// Rollback, if 1 Errors occurred in four places, so go back to the overall operation. 
    }
    // Plus combinword Field  END
    $addStep = 5000;  // Data per operation 
    $word_cnt = 0;
    $succ_cnt = 0;
    $sort = 0;
    $total = count($data);
    for ( $x=0; $x<$total; $x += $addStep ){
      $temp_json = array_slice($data, $x, $addStep);  // Batch operation  100 Article 
      $temp_json = array_column( $temp_json, "title" );
      $temp_json = array_unique($temp_json);
      $temp_str = $this->getStrByArr($temp_json);
      $temp_sql = "select * from report where words IN ({$temp_str})";
      $res = $db->query($temp_sql);
      $result = $res->fetchAll(PDO::FETCH_ASSOC);  // Get Array Result Set 
      $words_result = array_column($result, 'words'); // Result weight removal 
      $unique_result = array_unique($words_result);
      //var_export($unique_result);die;
      // Batch update  START
      $update_sql = "UPDATE report SET {$add_filed} = CASE words ";
      foreach ($unique_result as $k => $v){
        $updateValue = $v;
        $update_sql .= " WHEN '{$updateValue}' THEN ".$sort++;
      }
      $sort += count($unique_result);  // Add sort field 
      $update_sql_str = $this->getStrByArr( $unique_result );
      $update_sql .= " END WHERE words IN ({$update_sql_str})";
        //var_export($update_sql);die;
      try {
        $db->beginTransaction();// Start a transaction 
        $cnt = $db->exec($update_sql);  // Add field 
        $db->commit();// Commit transaction 
        $word_cnt += count($result);
        $succ_cnt += $cnt;
        echo " Updated [{".count($result)."}] Keywords, which affect the total number of keywords [{$cnt}] Bar data  ".PHP_EOL;
      }catch(PDOException $e){
        //$e->getMessage();// Gets the error message. 
        echo " Bulk update failed  ".PHP_EOL;
        $db->rollBack();// Rollback, if 1 Errors occurred in four places, so go back to the overall operation. 
      }
      // Batch update END
    }
    echo "1 A total of updates [{$word_cnt}] Keywords, which affect the total number of keywords [{$succ_cnt}] Bar data  ".PHP_EOL;
    die;
  }
  /**
   * @todo  Returns a spliced string based on an array 
   * @param unknown $temp_json  Array 
   * @return string  String 
   */
  function getStrByArr($temp_json){
    $temp_str = '';
    $count = count($temp_json);
    $lastValue = end($temp_json);//var_export($lastValue);die;  // Get the last of the array 1 Elements 
    foreach ($temp_json as $k => $v){
      $next_str = '';
      if($v != $lastValue ){  // Not the last 1 A 
        $next_str = ',';
      }else{
        $next_str = '';
      }
      $temp_str .= "'".$v."'{$next_str}";
    }
    return $temp_str;
  }
  /**
   * @todo  Get json Data 
   */
  public function getjson(){
    $filename = APP_PATH.'/data/combinword/jsonlist.json';
    $json = file_get_contents($filename);
    $array = json_decode($json, true);
    return $array;
  }
}

For more readers interested in PHP related contents, please check the topics on this site: "Summary of PHP Database Operation Skills Based on pdo", "Summary of php+Oracle Database Programming Skills", "Encyclopedia of PHP+MongoDB Database Operation Skills", "Introduction to php Object-Oriented Programming", "Usage Summary of php String (string)", "Introduction to php+mysql Database Operation Skills" and "Summary of php Common Database Operation Skills"

I hope this article is helpful to everyone's PHP programming.