[SOLVED] PHP

pizzipie · 08-10-2020, 11:13 PM

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:

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:

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>

michaelk · 08-10-2020, 11:35 PM

One obvious error is the postt variables index in your sql statment are not quoted like your if statements.

$_POST["form_title"]

You can either append the variables like you did with $table in your sql statement or escape the quotes.

pizzipie · 08-11-2020, 01:14 PM

Thanks michaelk,

Here is the output produced by my program exited at the query:

Quote:

Opened database successfully

INSERT INTO books (book_title, book_author, book_pub_year,book_publisher, book_read, book_score, book_loan) VALUES ('Submarine Life', 'Jentzen, Harry', '2001', 'Penguin Books', 'Yes', '4', 'Frank Jones');

Here is my corrected code:

Code:

      $sql="INSERT INTO ".$table." (book_title, book_author, book_pub_year,";
      $sql.="book_publisher, book_read, book_score, book_loan) VALUES ('";
      $sql.= $_POST['form_title']."', '".$_POST['form_author']."', '"; 
      $sql.= $_POST['form_pub_year']."', '".$_POST['form_publisher']."', '"; 
      $sql.= $_POST['form_read']."', '".$_POST['form_score']."', '".$_POST['form_loan']."');";

If I execute the query in DB Browser the data is input with no problem.

Tearing my hair out about now!

pizzipie · 08-11-2020, 01:48 PM

STOP!! Don't need help on this anymore.

I TOTALLY BLEW IT. HAD A COPY OF LIBRARY.DB IN ANOTHER FOLDER AND WAS TRYING TO ACCESS IT.

GOT THE CORRECT DB AND ALL IS FINE!!!

SORRY FOR WASTING YOUR TIME. I AM TOTALLY RED-FACED.

R

NevemTeve · 08-11-2020, 01:56 PM

Do you happen to have a complete PHP program that can be run width standalone php (as in php <filename>.php)?

michaelk · 08-11-2020, 01:56 PM

Good, I had run out of ideas... just as an aside although it works anyway is that you don't need to use a trailing ; within the sql statement itself and you really only need quotes around strings.