PHP操作SQLite示例程序 PDO驱动访问sqlite2、sqlite3数据库 常用函数

本示例算是比较全的PHP自带PDO驱动访问SQLite数据库的示例代码,可以学到新建文件、内存数据库,新建表,删除表,插入表,更新表,批量插入等等。
常用函数:setAttribute exec prepare bindParam query execute quote。
  date_default_timezone_set('Asia/Shanghai');
  echo date("Y-m-d H:i:s");
  echo "<pre>"; 
  try {
    $file_db = new PDO('sqlite:db1.sq3');
    $file_db->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);
    //$memory_db = new PDO('sqlite::memory:');
    $memory_db = new PDO('sqlite:db2.sq3');
    $memory_db->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);
    try{
    $file_db->exec("CREATE TABLE messages (
                    id INTEGER PRIMARY KEY, 
                    title TEXT, 
                    message TEXT, 
                    time INTEGER)");
    }catch(Exception $e)
    {
    }
    try{
    $memory_db->exec("CREATE TABLE messages (
                    id INTEGER PRIMARY KEY, 
                    title TEXT, 
                    message TEXT, 
                    time TEXT)");
    }catch(Exception $e)
    {
    }      
    $messages = array(
                    array('title' => '标题一',
                        'message' => '第一条数据',
                        'time' => time()),
                    array('title' => '标题二',
                        'message' => '第二条数据',
                        'time' => time()),
                    array('title' => '标题三',
                        'message' => '第三条数据',
                        'time' => time()),
                );
 
    $insert = "INSERT INTO messages (title, message, time) 
                VALUES (:title, :message, :time)";
    $stmt = $file_db->prepare($insert);
 
    $stmt->bindParam(':title', $title);
    $stmt->bindParam(':message', $message);
    $stmt->bindParam(':time', $time);
 
    foreach ($messages as $m) {
        $title = $m['title'];
        $message = $m['message'];
        $time = $m['time'];
        $stmt->execute();
    }
 
    //$insert = "INSERT INTO messages (id, title, message, time) 
                //VALUES (:id, :title, :message, :time)";
    $insert = "INSERT INTO messages (title, message, time) 
                VALUES (:title, :message, :time)";
    $stmt = $memory_db->prepare($insert);
 
    $result = $file_db->query('SELECT * FROM messages');
 
    foreach ($result as $m) {
      //$stmt->bindValue(':id', $m['id'], SQLITE3_INTEGER);
      $stmt->bindValue(':title', $m['title'], SQLITE3_TEXT);
      $stmt->bindValue(':message', $m['message'], SQLITE3_TEXT);
      $formatted_time = date('Y-m-d H:i:s', $m['time']);
      $stmt->bindValue(':time', $formatted_time, SQLITE3_TEXT);

      $stmt->execute();
    }
    $new_title = "修改后的标题1"'"";
    $new_title = $memory_db->quote($new_title);
    $update = "UPDATE messages SET title = {$new_title} 
                WHERE datetime(time) > 
                datetime('2015')";
    $memory_db->exec($update);
 
    $result = $memory_db->query('SELECT * FROM messages');
 
    foreach($result as $row) {
      echo "Id: " . $row['id'] . "n";
      echo "Title: " . $row['title'] . "n";
      echo "Message: " . $row['message'] . "n";
      echo "Time: " . $row['time'] . "n";
      echo "n";
    }

    $file_db->exec("DROP TABLE messages");
    $memory_db->exec("DROP TABLE messages");

    $file_db = null;
    $memory_db = null;
  }
  catch(PDOException $e) {
        echo $e->getMessage();
  }


发表于:2015-11-13 09:17:13

原文链接(转载请保留): http://www.multisilicon.com/blog/a25242761.html

友情链接: MICROIC
首页