PHP - SQLight3 ERROR no such table "table"
by pizzipie from LinuxQuestions.org on (#56RVJ)
The database opens successfully. The query produced by my code populates the database using 'DB Browser' with no problem. Under firefox I get the error no such table 'books'.Appreciate a solution to fix this problem!!
Here is the partial output of the code below:
Quote:
Here is my code. CREATE Database Library.db and table books:
Code:<html>
<head>
<title>Create Library Database and Table</title>
</head>
<body>
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('library.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql = "CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
book_title VARCHAR,
book_author VARCHAR,
book_pub_year INTEGER,
book_publisher VARCHAR,
book_read VARCHAR,
book_score VARCHAR,
book_loan VARCHAR
)";
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Table created successfully\n";
}
$db->close();
?>
</body>
</html>Here is the code to insert the data:
Code:<html>
<head>
<title>Personal Library: Add a Book</title>
</head>
<body>
<h1 align="center">Welcome to the Library</h1>
<?php
$database="/var/www/rickSQL.com/public_html/books/library.db";
$table="books";
if (empty($_POST['action'])) $_POST['action'] = '';
if ($_POST['action'] == "Insert") {
class MyDB extends SQLite3 {
function __construct() {
global $database;
$this->open($database);
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully<br>";
}
$sql="INSERT INTO ".$table." (book_title, book_author, book_pub_year,
book_publisher, book_read, book_score, book_loan)
values('$_POST[form_title]', '$_POST[form_author]',
'$_POST[form_pub_year]', '$_POST[form_publisher]',
'$_POST[form_read]', '$_POST[form_score]', '$_POST[form_loan]');";
//echo $sql;
$ret=$db->exec($sql);
if(!$ret) {
print ( '<p>Failed adding <b>'. $_POST[form_title] . '</b> because of error</p>');
echo "Crap - ".$db->lastErrorMsg();
} else {
print ( '<p>Successfully added '. $_POST['form_title'] . ' to table books.</p>' );
print ( '<p>Add another book if you wish.</p>' );
}
$db->close();
print ( '<p>Please enter book information below. Include at least a
title and author</p>') ;
}
?>
<p><form action="books_insert.php" method="post"><br />
Enter a book title:<br />
<input type="text" name="form_title"><br />
Enter the author (last, first) of this book:<br />
<input type="text" name="form_author"><br />
Enter the year of publication<br />
<input type="text" name="form_pub_year"><br />
Enter the publisher:<br />
<input type="text" name="form_publisher"><br>
Have you read this book yet?<br />
<select name="form_read"><br />
<option value="Yes" SELECTED>Yes</option><br />
<option value="No">No</option><br />
</select><br />
Give this book a rating (1 to 5)<br />
<input type="text" name="form_score"><br />
Currently loaned to:<br />
<input type="text" name="form_loan"><br />
<input type="submit" name="action" value="Insert"><br />
</form></p>
<p><a href="index.php">Back to the main library page.</a></p>
</body>
</html>
Here is the partial output of the code below:
Quote:
| Welcome to the Library Opened database successfully Failed adding war because of error Crap - no such table: books Please enter book information below. Include at least a title and author Enter a book title: Enter the author (last, first) of this book: |
Code:<html>
<head>
<title>Create Library Database and Table</title>
</head>
<body>
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('library.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql = "CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
book_title VARCHAR,
book_author VARCHAR,
book_pub_year INTEGER,
book_publisher VARCHAR,
book_read VARCHAR,
book_score VARCHAR,
book_loan VARCHAR
)";
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo "Table created successfully\n";
}
$db->close();
?>
</body>
</html>Here is the code to insert the data:
Code:<html>
<head>
<title>Personal Library: Add a Book</title>
</head>
<body>
<h1 align="center">Welcome to the Library</h1>
<?php
$database="/var/www/rickSQL.com/public_html/books/library.db";
$table="books";
if (empty($_POST['action'])) $_POST['action'] = '';
if ($_POST['action'] == "Insert") {
class MyDB extends SQLite3 {
function __construct() {
global $database;
$this->open($database);
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully<br>";
}
$sql="INSERT INTO ".$table." (book_title, book_author, book_pub_year,
book_publisher, book_read, book_score, book_loan)
values('$_POST[form_title]', '$_POST[form_author]',
'$_POST[form_pub_year]', '$_POST[form_publisher]',
'$_POST[form_read]', '$_POST[form_score]', '$_POST[form_loan]');";
//echo $sql;
$ret=$db->exec($sql);
if(!$ret) {
print ( '<p>Failed adding <b>'. $_POST[form_title] . '</b> because of error</p>');
echo "Crap - ".$db->lastErrorMsg();
} else {
print ( '<p>Successfully added '. $_POST['form_title'] . ' to table books.</p>' );
print ( '<p>Add another book if you wish.</p>' );
}
$db->close();
print ( '<p>Please enter book information below. Include at least a
title and author</p>') ;
}
?>
<p><form action="books_insert.php" method="post"><br />
Enter a book title:<br />
<input type="text" name="form_title"><br />
Enter the author (last, first) of this book:<br />
<input type="text" name="form_author"><br />
Enter the year of publication<br />
<input type="text" name="form_pub_year"><br />
Enter the publisher:<br />
<input type="text" name="form_publisher"><br>
Have you read this book yet?<br />
<select name="form_read"><br />
<option value="Yes" SELECTED>Yes</option><br />
<option value="No">No</option><br />
</select><br />
Give this book a rating (1 to 5)<br />
<input type="text" name="form_score"><br />
Currently loaned to:<br />
<input type="text" name="form_loan"><br />
<input type="submit" name="action" value="Insert"><br />
</form></p>
<p><a href="index.php">Back to the main library page.</a></p>
</body>
</html>