sqlite2转sqlite3转换php代码

Question

I'm trying to run a script on my server. This script uses sqlite2 as database. The php file is written in sqlite2, but on my server I have installed sqlite3.

This is my code:

if (!$db = sqlite_open ('chatdb'))
{
    die('Database problem. Please try again later.');
}

function sqlite_table_exists ($db, $mytable)
{
    $result = sqlite_query($db, "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$mytable'");
    $count = intval(sqlite_fetch_single($result));
    return $count > 0;
}

if (sqlite_table_exists($db, 'messages') == false)
{
    $sql = 'CREATE TABLE messages (username VARCHAR(50), message TEXT, date DATE)';
    sqlite_query ($db, $sql);
}

if (sqlite_table_exists($db, 'users') == false)
{
    $sql = 'CREATE TABLE users (username VARCHAR(50) UNIQUE, last_activity DATE, is_kicked INTEGER DEFAULT 0, is_banned INTEGER DEFAULT 0, kick_ban_message VARCHAR(100))';
    sqlite_query ($db, $sql);
}

if (sqlite_table_exists($db, 'bans') == false)
{
    $sql = 'CREATE TABLE bans (ip VARCHAR(15), time DATE)';
    sqlite_query ($db, $sql);
}

When I run the code, I get errors, such as;

Fatal error: Call to undefined function sqlite_open()

I think the code is not ready for sqlite3. How can I convert this code to sqlite3?

Thanks.

Answer 1

You need to use the SQLite3 class. The significant difference to your current code is that you'll be using an object and its methods instead of procedural functions.

For connecting to (or creating) the database you need to instantiate a new SQLite3 object. If there is a problem an Exception will be thrown:

try {
    $db = new SQLite3('chatdb');
} catch (Exception $e) {
    die($e->getMessage());
}

You shouldn't concatenate variables into your queries as this is a security risk (see: How can I prevent SQL-injection in PHP?) so I've modified your function to use binding:

function sqlite_table_exists($db, $mytable)
{
    $stmt = $db->prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=:name");
    $stmt->bindValue('name', $mytable);
    $result = $stmt->execute();

    $row = $result->fetchArray(SQLITE3_NUM);

    return $row[0] > 0;
}

Then you can create your tables like so:

if (sqlite_table_exists($db, 'table_name') == false) {
    $db->exec('CREATE TABLE ...');
}