Detailed explanation of SQLite PHP interface

This article mainly shares the SQLite PHP interface knowledge with you, hoping to help everyone. First, let’s take a look at the relevant knowledge of the PHP interface API.

PHP Interface API

Connect to database

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open(&#39;test.db&#39;);
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
?>

Create table

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open(&#39;test.db&#39;);
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = $db->exec($sql);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo "Table created successfully\n";
   }
   $db->close();
?>

INSERT operation

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open(&#39;test.db&#39;);
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, &#39;Paul&#39;, 32, &#39;California&#39;, 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, &#39;Allen&#39;, 25, &#39;Texas&#39;, 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, &#39;Teddy&#39;, 23, &#39;Norway&#39;, 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, &#39;Mark&#39;, 25, &#39;Rich-Mond &#39;, 65000.00 );
EOF;

   $ret = $db->exec($sql);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo "Records created successfully\n";
   }
   $db->close();
?>

SELECT operation

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open(&#39;test.db&#39;);
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
      echo "ID = ". $row[&#39;ID&#39;] . "\n";
      echo "NAME = ". $row[&#39;NAME&#39;] ."\n";
      echo "ADDRESS = ". $row[&#39;ADDRESS&#39;] ."\n";
      echo "SALARY =  ".$row[&#39;SALARY&#39;] ."\n\n";
   }
   echo "Operation done successfully\n";
   $db->close();
?>

UPDATE operation

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open(&#39;test.db&#39;);
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = $db->exec($sql);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record updated successfully\n";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
      echo "ID = ". $row[&#39;ID&#39;] . "\n";
      echo "NAME = ". $row[&#39;NAME&#39;] ."\n";
      echo "ADDRESS = ". $row[&#39;ADDRESS&#39;] ."\n";
      echo "SALARY =  ".$row[&#39;SALARY&#39;] ."\n\n";
   }
   echo "Operation done successfully\n";
   $db->close();
?>

DELETE operation

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open(&#39;test.db&#39;);
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      DELETE from COMPANY where ID=2;
EOF;
   $ret = $db->exec($sql);
   if(!$ret){
     echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record deleted successfully\n";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
      echo "ID = ". $row[&#39;ID&#39;] . "\n";
      echo "NAME = ". $row[&#39;NAME&#39;] ."\n";
      echo "ADDRESS = ". $row[&#39;ADDRESS&#39;] ."\n";
      echo "SALARY =  ".$row[&#39;SALARY&#39;] ."\n\n";
   }
   echo "Operation done successfully\n";
   $db->close();
?>

Related recommendations:

How to correct the php interface Usage

Tips for using the PHP interface

Some summary of issues regarding the use of the PHP interface

The above is the detailed content of Detailed explanation of SQLite PHP interface. For more information, please follow other related articles on the PHP Chinese website!