PHP script creates corrupt SQLite database

Question

I have this PHP script to create a database on a MAC Snow Leopard. When I try to open the .sqlite created I get an error message both from SQLite Database Browser 2.0 b1 ("An error occurred: File is not a SQLite 3 database") and Firefox SQLite Manager ("SQLiteManager: Error in opening file forum.sqlite - either the file is encrypted or corrupt Exception Name: NS_ERROR_FILE_CORRUPTED Exception Message: Component returned failure code: 0x8052000b (NS_ERROR_FILE_CORRUPTED)[mozIStorageService.openUnsharedDatabase]"). This is my script

<?php

$dbhandle = sqlite_open('forum.sqlite', $error);
if (!$dbhandle) die ($error);

$stm1 = 'CREATE TABLE author' .'(
            id INTEGER PRIMARY KEY,
            name TEXT,
            url TEXT
           )';

$ok1 = sqlite_exec($dbhandle, $stm1, $error);

if (!$ok1)
   die("Cannot execute query. $error");

echo "Table author created successfully"."\n";   

$stm2 = 'CREATE TABLE thread' .'(
            id INTEGER PRIMARY KEY,
            authorId INTEGER,
            title TEXT,
            reactions INTEGER,
            dislikes INTEGER,
            userScore INTEGER,
            createdAt DATETIME,
            slug TEXT,
            postNumber INTEGER,
            link TEXT,
            likes INTEGER,
            message TEXT,
            category INTEGER,
            score INTEGER,
            categoryLink TEXT,
            FOREIGN KEY(authorId) REFERENCES author(id)
            )';

$ok2 = sqlite_exec($dbhandle, $stm2, $error);

if (!$ok2)
   die("Cannot execute query. $error");

echo "Table thread created successfully"."\n"; 

$stm3 = 'CREATE TABLE commentAuthor' .'(
            authorUrl TEXT PRIMARY KEY,
            name TEXT
            )';

$ok3 = sqlite_exec($dbhandle, $stm3, $error);

if (!$ok3)
   die("Cannot execute query. $error");

echo "Table commentAutor created successfully"."\n"; 

$stm4 = 'CREATE TABLE comment' .'(
            id INTEGER PRIMARY KEY,
            threadId INTEGER,
            forum TEXT,
            parent INTEGER,
            authorUrl TEXT,
            dislikes INTEGER,
            rawMEssage TEXT,
            createdAt DATETIME,
            numReports INTEGER,
            likes INTEGER,
            message TEXT,
            FOREIGN KEY(threadId) REFERENCES thread(id),
            FOREIGN KEY(authorUrl) REFERENCES commentAuthor(authorUrl)
            )';

$ok4 = sqlite_exec($dbhandle, $stm4, $error);

if (!$ok4)
   die("Cannot execute query. $error");

echo "Table comment created successfully"."\n"; 

sqlite_close($dbhandle);

?>

The script run correctly and I receive confirmation the tables were created.

CL. · Accepted Answer · 2013-03-20 07:43:00Z

2

These sqlite_* functions create SQLite 2 databases.

For SQLite 3 databases, use PDO.

answered Mar 20, 2013 at 7:43

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CptNemo Over a year ago

Thank you, that explains it.

Christian Stocker · Accepted Answer · 2013-03-20 08:50:22Z

0

Or instead of PDO, you could also use this: http://php.net/sqlite3

(but I'd prefer PDO as well)

answered Mar 20, 2013 at 8:50

Christian Stocker

3611 silver badge5 bronze badges

Collectives™ on Stack Overflow

PHP script creates corrupt SQLite database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related