Error inserting form data into the sqlite database

[vik87]

Hi everyone, this is my very first post resulted by an error in my code i couldnt figure out. I am relatively new to programming and picked up php as my first learning experience. I am following a book 'Learning PHP' and i cant seem to figure out the reason my code is throwing an exception when extracting data from the form and storing it into the tables of database. I didnt create the database before hand as i assumed the PDO object will create it if it doesnt find the database in the directory. It does create the database file but throws an exception "Couldnt add dish to the database" when adding data to it from the form. Any help would be greatly appreciated, thanks for takings your time to read and answer. Here is my code,

 

<?php
require 'formHelper.php';
try{
    $db = new PDO('sqlite:C:\xampp\htdocs\temp');
}
catch(PDOException $e){
    print "Cant connect. ". $e->getMessage();
    exit();
}
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if($_SERVER['REQUEST_METHOD'] == 'POST'){
    list($errors,$input) = validate_form();
    if($errors){
        show_form($errors);
    }
    else{
        process_form($input);
    }
}
else{
    show_form();
}


function show_form($errors = array()){
    $default = array('price'=>'5.00');
    $form = new formHelper($default);
    include 'restaurant-form.php';
}


function validate_form(){
    $input = array();
    $errors = array();
    $input['dish_name'] = trim($_POST['dish_name'] ?? '');
    if(!strlen($input['dish_name'])){
        $errors = "Please enter the name of the dish.";
    }
    $input['price'] = filter_input(INPUT_POST,'price',FILTER_VALIDATE_FLOAT);
    if($input['price'] <= 0){
        $errors = "Please enter the price of the dish.";
    }
    $input['is_spicy'] = $_POST['is_spicy'] ?? 'no';
    return array($errors, $input);
       }
       
function process_form($input){
    global $db;
    if($input['is_spicy'] == 'yes'){
        $is_spicy = 1;
    }
    else{
        $is_spicy = 0;
    }
    try{
        $stmt = $db->prepare('INSERT INTO dishes(dish_name,price,is_spicy) VALUES(?, ?, ?)');
        $stmt->execute(array($input['dish_name'], $input['price'], $is_spicy));
        print 'Added' . htmlentities($input['dish_name']) . 'to the database';
    }
    catch(PDOException $e){
        print "Couldnt add dish to the database";
    }
}
?>

Edited February 11, 2018 by Zane
use [code] tags

[requinix]

catch(PDOException $e){
    print "Couldnt add dish to the database";
}

Exceptions like $e almost always contain useful information. If you want to understand what the problem is then you should look in the exception for information.

print "Couldn't add dish to the database: " . $e->getMessage();

And note that while PHP will create the SQLite file for you (I think), it won't create everything else you might need...

[vik87]

 

And note that while PHP will create the SQLite file for you (I think), it won't create everything else you might need...

 

Thanks for your reply. So you are suggesting i will have to create table and columns via command line? Here is what var_dump gave out:

 

 

PDOException::__set_state(array(

'message' => 'SQLSTATE[HY000]: General error: 1 no such table: dishes',

'string' => '',

'code' => 'HY000',

'file' => 'C:\\xampp\\htdocs\\dishesform.php',

'line' => 54,

'trace' =>

array (

0 =>

array (

'file' => 'C:\\xampp\\htdocs\\dishesform.php',

'line' => 54,

'function' => 'prepare',

'class' => 'PDO',

'type' => '->',

'args' =>

array (

0 => 'INSERT INTO dishes(dish_name,price,is_spicy) VALUES(?, ?, ?)',

),

),

1 =>

array (

'file' => 'C:\\xampp\\htdocs\\dishesform.php',

'line' => 17,

'function' => 'process_form',

'args' =>

array (

0 =>

array (

'dish_name' => 'new',

'price' => 5.0,

'is_spicy' => 'no',

),

),

),

),

'previous' => NULL,

'errorInfo' =>

array (

0 => 'HY000',

1 => 1,

2 => 'no such table: dishes',

),

))

[vik87]

I got it. Although the database is created automatically if its not found, the table and columns have to be added manually. Thanks for your help. 

[requinix]

Right.

 

And though it's probably too late to matter, you don't need to use the command line to create the tables and such. You can do it in code.

 

What I would do is

try{
    $exists = file_exists('C:\xampp\htdocs\temp');
    $db = new PDO('sqlite:C:\xampp\htdocs\temp');
    if (!$exists) {
        $db->exec('CREATE TABLE dishes (...)');
        // etc.
    }
}

At least while you're still developing this. That way you can delete the database file at any time to reset the data. When you have everything set up and working you'd remove it.