PHP 连接 MySQL 与 SQLite.md

MySQL

创建和关闭连接

$con=mysqli_connect($servername,$username,$password,$database);
// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
mysqli_close($conn);

有返回值语句

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"];
    }
}
$result -> free();  //释放查询结果

无返回值语句

bool mysqli_query($conn, $sql)
bool mysqli_multi_query($conn, $sql)

参数化查询，无返回值语句

$stmt = $connection->prepare("INSERT INTO Guests (email) VALUES (?)");
$stmt->bind_param("s", $email);
// 设置参数并执行
$stmt->execute();
$stmt->close();

参数化查询，有返回值语句

$sql = "";
$stmt = $connection->prepare($sql);
$stmt->bind_param("ss",$day,$userID);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($num, $needReview, $russian, $chinese);
if ($stmt->num_rows != 0) {
    while($stmt->fetch()){
        array($num,$needReview,$russian,$chinese);
    }
}

SQLite

连接到SQLite数据库

以下PHP代码显示如何连接到SQLite数据库。 如果数据库不存在，那么它将创建一个新的数据库，最后将返回一个数据库对象。

<?php
class SQLiteDB extends SQLite3
{
    function __construct()
    {
        $this->open('phpdb.db');
    }
}
$db = new SQLiteDB();
if (!$db) {
    echo $db->lastErrorMsg();
} else {
    echo "Yes, Opened database successfully\n";
}

$db->close();

创建表

以下PHP程序将用于在上面创建的数据库(phpdb.db)中创建一个表：

<?php
// 先删除后创建表
$sql = "DROP table company";
$ret = $db->exec($sql);

// 创建表语句
$sql = <<<EOF
      CREATE TABLE if not exists 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 "Yes, Table created successfully<br/>\n";
}

插入数据操作

以下PHP程序显示了如何在上述示例中创建的company表中插入数据记录：

<?php
$sql = <<<EOF
      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );
EOF;

$ret = $db->exec($sql);
if (!$ret) {
    echo $db->lastErrorMsg();
} else {
    echo "Yes, Some Records has Inserted successfully<br/>\n";
}

SELECT 操作

以下PHP程序显示了如何从上述示例中创建的company表中获取并显示数据记录：

<?php
// 查询表中的数据
echo "<b> Select Data from company table :</b><hr/>";

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

$ret = $db->query($sql);
while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
    echo "ID = " .      $row['ID'] .      "<br/>\n";
    echo "NAME = " .    $row['NAME'] .    "<br/>\n";
    echo "ADDRESS = " . $row['ADDRESS'] . "<br/>\n";
    echo "SALARY =  " . $row['SALARY'] .  "<br/>\n\n";
    echo '----------------------------------<br/>';
}

echo "Operation done successfully\n";

更新操作

以下PHP代码显示了如何使用UPDATE语句来更新记录，然后从company表中获取并显示更新的记录：

<?php
// 更新ID=1的薪水为：29999

$sql = 'UPDATE COMPANY set SALARY = 29999.00 where ID=1';
$ret = $db->exec($sql);
if (!$ret) {
    echo $db->lastErrorMsg();
} else {
    echo $db->changes(), " Record(ID=1) updated successfully<br/>\n";
}

删除操作

以下PHP代码显示了如何使用DELETE语句删除任何记录，然后从company表中获取并显示剩余的记录：

<?php
// 删除ID小于等于2的数据记录

$sql = <<<EOF
  DELETE from COMPANY where ID<=2;
EOF;
$ret = $db->exec($sql);
if (!$ret) {
    echo $db->lastErrorMsg();
} else {
    echo $db->changes(), " Record(ID<=2) deleted successfully<br/>\n";
}