{"id":276,"date":"2016-11-14T06:57:25","date_gmt":"2016-11-13T21:57:25","guid":{"rendered":"http:\/\/www.dogrow.net\/php\/?p=276"},"modified":"2018-10-18T00:51:07","modified_gmt":"2018-10-17T15:51:07","slug":"blog17","status":"publish","type":"post","link":"https:\/\/www.dogrow.net\/php\/blog17\/","title":{"rendered":"(17) SQLite\u3067\u7c21\u6613\u63b2\u793a\u677f\u4f5c\u6210"},"content":{"rendered":"

1. \u3084\u308a\u305f\u3044\u3053\u3068<\/h1>\n

\u307e\u305a\u306f\u300c\u6295\u7a3f\u300d\u3001\u300c\u7de8\u96c6\u300d\u3001\u300c\u524a\u9664\u300d\u3092\u5b9f\u88c5\u3059\u308b\u3002
\n\u753b\u9762\u30ec\u30a4\u30a2\u30a6\u30c8\u306f index.php \u306b\u66f8\u304f\u3088\u3046\u306b\u5207\u308a\u96e2\u3057\u3066\u3042\u308b\u306e\u3067\u3001\u30c7\u30b6\u30a4\u30f3\u304d\u308c\u3044\u5316\u306f\u5f8c\u3067…<\/p>\n

\u5b9f\u884c\u30b5\u30f3\u30d7\u30eb\u306f\u3053\u3061\u3089\u3067\u3059\u3002
\nhttp:\/\/www.dogrow.net\/php\/sample\/00017\/
\n<\/a><\/p>\n

2. \u30d7\u30ed\u30b0\u30e9\u30e0\u4f5c\u6210<\/h1>\n

1) index.php<\/h3>\n
\r\n<!DOCTYPE html>\r\n<html>\r\n<head>\r\n<meta charset="utf-8" \/>\r\n<title>SQLite\u30b5\u30f3\u30d7\u30eb<\/title>\r\n<\/head>\r\n<\/body>\r\n<?php\r\n  require_once("CBbsMng.php");\r\n  $cBbsMng = new CBbsMng();\r\n  $cBbsMng->connect_db();\r\n\r\n  \/\/ \u6295\u7a3f\u5236\u5fa1\r\n  $cmd =(isset($_POST['cmd']))? $_POST['cmd'] : 0;\r\n  if($cmd == 1){\r\n    $tid   = htmlspecialchars($_POST['tid']);\r\n    $title = htmlspecialchars($_POST['title']);\r\n    $name  = htmlspecialchars($_POST['name']);\r\n    $bun   = htmlspecialchars($_POST['bun']);\r\n    if(($title == "") && ($name == "") && ($bun == "")){\r\n      if($tid == 0){      \/\/ \u65b0\u898f\u30b9\u30ec\u30c3\u30c9\u3092\u4f5c\u6210\uff1f\r\n        $cBbsMng->create_thread($title, $name, $bun);\r\n      }else{\r\n        $cBbsMng->create_item($tid, $title, $name, $bun);\r\n      }\r\n    }\r\n  }\r\n  \/\/ \u30b9\u30ec\u30c3\u30c9\u8868\u793a\r\n  $ary_th = $cBbsMng->get_thread();\r\n  foreach($ary_th as $th){\r\n    echo "<hr><h1 style=\\"color:#f00\\">".$th['title']."<\/h1>";\r\n    $ary_it = $cBbsMng->get_item($th['tid']);\r\n    foreach($ary_it as $it){\r\n      echo "<h2>\u25cf".$it['title']." [".$it['name']."] ".strftime("%G\/%m\/%d %H:%M:%S",$it['dt_post'])."<\/h2>";\r\n      echo "<p style=\\"border:1px #000 solid;margin-left:2rem;padding:0.5rem;\\">".$it['bun']."<\/p>";\r\n    }\r\n  }\r\n?>\r\n\r\n<hr>\r\n<form enctype="multipart\/form-data" action=".\/" method=post>\r\n\u30b9\u30ec\u30c3\u30c9\uff1a<select name=tid>\r\n<option value=0>\u65b0\u898f\u4f5c\u6210<\/option>\r\n<?\r\n  foreach($ary_th as $th){\r\n    echo "<option value=\\"".$th['tid']."\\">".$th['title']."<\/option>";\r\n  }\r\n?>\r\n<\/select><br \/>\r\n<input type=hidden name=cmd value=1>\r\n\u30bf\u30a4\u30c8\u30eb\uff1a<input type=text name=title><br \/>\r\n\u540d\u524d\uff1a<input type=text name=name><br \/>\r\n\u8a18\u4e8b\uff1a<textarea name=bun><\/textarea><br \/>\r\n<input type=submit value="\u6295\u7a3f">\r\n<\/form>\r\n\r\n<\/body>\r\n<\/html>\r\n<\/pre>\n
2) CBbsMng.php<\/h3>\n
\u203bthread\u3068 item\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u5206\u3051\u308b\u5fc5\u8981\u306f\u306a\u304b\u3063\u305f… \u3044\u3064\u304b\u4e00\u3064\u306b\u307e\u3068\u3081\u3088\u3046\u3002<\/span><\/p>\n
\r\n<?php\r\nclass CBbsMng {\r\n  private $m_db;        \/\/ SQLite3 instance\r\n  private $m_db_fpath;  \/\/ DB\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9\r\n\r\n  function __construct( $fpath = "thbbs.db" ) {\r\n    $this->m_db = NULL;\r\n    $this->m_db_fpath = $fpath;\r\n  }\r\n\r\n  \/\/ DB\u63a5\u7d9a\r\n  public function connect_db(){\r\n    if($this->m_db == NULL){\r\n      $isDbExist = file_exists($this->m_db_fpath);\r\n      \/\/ DB\u63a5\u7d9a\r\n      $this->m_db = new SQLite3($this->m_db_fpath);\r\n      $this->m_db->enableExceptions(TRUE);\r\n      if($isDbExist === FALSE){   \/\/ DB\u306f\u65b0\u898f\u4f5c\u6210\uff1f\r\n        try {\r\n          \/\/ \u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\r\n          $this->m_db->exec('begin');\r\n          $this->m_db->exec("CREATE TABLE `thread` (`tid` INTEGER PRIMARY KEY, `top_iid` INTEGER, `new_iid` INTEGER, `dt_update` INTEGER);");\r\n          $this->m_db->exec("CREATE TABLE `item`   (`iid` INTEGER PRIMARY KEY, `tid` INTEGER, `title` TEXT, `name` TEXT, `bun` TEXT, `dt_post` INTEGER);");\r\n          $this->m_db->exec('commit');\r\n        }catch(Exception $e){\r\n          $this->m_db->exec('rollback');\r\n          echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n        }\r\n      }\r\n    }\r\n  }\r\n\r\n  \/\/ \u30b9\u30ec\u30c3\u30c9\u3092\u4f5c\u6210\r\n  public function create_thread( $title, $name, $bun ){\r\n    if($this->m_db != NULL){\r\n      $dt_now = time();\r\n      try {\r\n        $this->m_db->exec('begin');\r\n        \/\/ \u30bf\u30a4\u30c8\u30eb\u3092\u4f5c\u6210\r\n        $this->m_db->exec("INSERT INTO `thread` (`top_iid`,`new_iid`,`dt_update`) VALUES('0','0','0');");\r\n        $tid = $this->m_db->lastInsertRowID();\r\n        \/\/ \u8a18\u4e8b\u3092\u4f5c\u6210\r\n        $this->m_db->exec("INSERT INTO `item` (`tid`,`title`,`name`,`bun`,`dt_post`) VALUES('$tid','$title','$name','$bun','$dt_now');");\r\n        $iid = $this->m_db->lastInsertRowID();\r\n        \/\/ \u30bf\u30a4\u30c8\u30eb\u306b\u5148\u982d\u9805\u76ee\u306eID\u3092\u6301\u305f\u305b\u308b\u3002\r\n        $this->m_db->exec("UPDATE `thread` SET `top_iid`='$iid',`new_iid`='$iid',`dt_update`='$dt_now' WHERE `tid`='$tid';");\r\n        $this->m_db->exec('commit');\r\n      }catch(Exception $e){\r\n        $this->m_db->exec('rollback');\r\n        echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n      }\r\n    }\r\n  }\r\n\r\n  \/\/ \u8a18\u4e8b\u3092\u4f5c\u6210\r\n  public function create_item( $tid, $title, $name, $bun ){\r\n    if($this->m_db != NULL){\r\n      $dt_now = time();\r\n      try {\r\n        $this->m_db->exec('begin');\r\n        $this->m_db->exec("INSERT INTO `item` (`tid`,`title`,`name`,`bun`,`dt_post`) VALUES('$tid','$title','$name','$bun','$dt_now');");\r\n        $iid = $this->m_db->lastInsertRowID();\r\n        $this->m_db->exec("UPDATE `thread` SET `new_iid`='$iid',`dt_update`='$dt_now' WHERE `tid`='$tid';");\r\n        $this->m_db->exec('commit');\r\n      }catch(Exception $e){\r\n        $this->m_db->exec('rollback');\r\n        echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n      }\r\n    }\r\n  }\r\n\r\n  \/\/ \u8a18\u4e8b\u3092\u5909\u66f4\r\n  public function modify_item( $iid, $title, $name, $bun ){\r\n    if($this->m_db != NULL){\r\n      $dt_now = time();\r\n      $rs = $this->m_db->query("SELECT `tid` FROM `item` WHERE `iid`='$iid';");\r\n      $row = $rs->fetchArray();\r\n      if(isset($row['tid']) === TRUE){\r\n        try {\r\n          $this->m_db->exec('begin');\r\n          $tid = $row['tid'];\r\n          $this->m_db->exec("UPDATE `item` SET `title`='$title',`name`='$name',`bun`='$bun',`dt_post`='$dt_now' WHERE `iid`='$iid';");\r\n          $this->m_db->exec("UPDATE `thread` SET `new_iid`='$iid',`dt_update`='$dt_now' WHERE `tid`='$tid';");\r\n          $this->m_db->exec('commit');\r\n        }catch(Exception $e){\r\n          $this->m_db->exec('rollback');\r\n          echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n        }\r\n      }\r\n    }\r\n  }\r\n\r\n  \/\/ \u30b9\u30ec\u30c3\u30c9\u3092\u524a\u9664\r\n  public function delete_thread( $tid ){\r\n    $ret = array();\r\n    if($this->m_db != NULL){\r\n      try {\r\n        $this->m_db->exec('begin');\r\n        \/\/ \u30b9\u30ec\u30c3\u30c9\u3092\u524a\u9664\r\n        $this->m_db->query("DELETE FROM `thread` WHERE `tid`='$tid';");\r\n        \/\/ \u914d\u4e0b\u306e\u8a18\u4e8b\u3092\u524a\u9664\r\n        $this->m_db->query("DELETE FROM `item` WHERE `tid`='$tid';");\r\n        $this->m_db->exec('commit');\r\n      }catch(Exception $e){\r\n        $this->m_db->exec('rollback');\r\n        echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n      }\r\n    }\r\n    return $ret;\r\n  }\r\n\r\n  \/\/ \u8a18\u4e8b\u3092\u524a\u9664\r\n  public function delete_item( $iid ){\r\n    $ret = array();\r\n    if($this->m_db != NULL){\r\n      try {\r\n        $this->m_db->exec('begin');\r\n        $this->m_db->query("DELETE FROM `item` WHERE `iid`='$iid';");\r\n        $this->m_db->exec('commit');\r\n      }catch(Exception $e){\r\n        $this->m_db->exec('rollback');\r\n        echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n      }\r\n    }\r\n    return $ret;\r\n  }\r\n\r\n  \/\/ \u30b9\u30ec\u30c3\u30c9\u3092\u53d6\u5f97\r\n  public function get_thread(){\r\n    $ret = array();\r\n    if($this->m_db != NULL){\r\n      try {\r\n        \/\/ \u5168\u30b9\u30ec\u30c3\u30c9\u60c5\u5831\u3092\u53d6\u5f97\r\n        $rs = $this->m_db->query("SELECT * FROM `thread` ORDER BY `dt_update` DESC;");\r\n        while($row = $rs->fetchArray()){\r\n          if(isset($row['tid']) === TRUE){\r\n            \/\/ \uff11\u30b9\u30ec\u30c3\u30c9\u306e\u5148\u982d\u8a18\u4e8b\u3092\u53d6\u5f97\r\n            $rs2 = $this->m_db->query("SELECT * FROM `item` WHERE `iid`='".$row['top_iid']."';");\r\n            $row2 = $rs2->fetchArray();\r\n            if(isset($row2['iid']) === TRUE){\r\n              $row['title'] = $row2['title'];\r\n              $ret[] = $row;\r\n            }\r\n          }\r\n        }\r\n      }catch(Exception $e){\r\n        echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n      }\r\n    }\r\n    return $ret;\r\n  }\r\n\r\n  \/\/ \u8a18\u4e8b\u3092\u53d6\u5f97\r\n  public function get_item( $tid, $iid=-1 ){\r\n    $ret = array();\r\n    if($this->m_db != NULL){\r\n      try {\r\n        $where = "`tid`='$tid'";\r\n        if($iid != -1){\r\n          $where .= " `iid`='$iid'";\r\n        }\r\n        \/\/ \u8a18\u4e8b\u60c5\u5831\u3092\u53d6\u5f97\r\n        $rs = $this->m_db->query("SELECT * FROM `item` WHERE $where ORDER BY `iid`;");\r\n        while($row = $rs->fetchArray()){\r\n          if(isset($row['iid']) === TRUE){\r\n            $ret[] = $row;\r\n          }\r\n        }\r\n      }catch(Exception $e){\r\n        echo "[".__LINE__."] Caught exception: ".$e->getMessage();\r\n      }\r\n    }\r\n    return $ret;\r\n  }\r\n}\r\n?>\r\n<\/pre>\n
\n","protected":false},"excerpt":{"rendered":"
1. \u3084\u308a\u305f\u3044\u3053\u3068 \u307e\u305a\u306f\u300c\u6295\u7a3f\u300d\u3001\u300c\u7de8\u96c6\u300d\u3001\u300c\u524a\u9664\u300d\u3092\u5b9f\u88c5\u3059\u308b\u3002 \u753b\u9762\u30ec\u30a4\u30a2\u30a6\u30c8\u306f index.php \u306b\u66f8\u304f\u3088\u3046\u306b\u5207\u308a\u96e2\u3057\u3066\u3042\u308b\u306e\u3067\u3001\u30c7\u30b6\u30a4\u30f3\u304d\u308c\u3044\u5316\u306f\u5f8c\u3067… \u5b9f\u884c\u30b5\u30f3\u30d7\u30eb\u306f\u3053\u3061\u3089\u3067\u3059\u3002 http:\/\/w\u2026 \u7d9a\u304d\u3092\u8aad\u3080 »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-276","post","type-post","status-publish","format-standard","hentry","category-sqlite"],"views":9461,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/posts\/276","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/comments?post=276"}],"version-history":[{"count":27,"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/posts\/276\/revisions"}],"predecessor-version":[{"id":1741,"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/posts\/276\/revisions\/1741"}],"wp:attachment":[{"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/media?parent=276"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/categories?post=276"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dogrow.net\/php\/wp-json\/wp\/v2\/tags?post=276"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}