PHP SQlite Database Form

Question

I have been struggling through PHP and sqlite for a bit now and I'm just confusing myself.
I have an html form that accessess a php script called processFeedback.php.My html code looks like this..

<html>
<head>
</head>
<body>
<form action="processFeedback.php" method="POST">
    <table>
        <tr>
            <td>Name:</td><td><input name="name"/></td>
        </tr>
        <tr>
            <td>Email:</td><td><input name="email"/></td>
        </tr>
        <tr>
            <td>Comments:</td><td><textarea name="comments"></textarea></td>
        </tr>
        <tr>
            <td></td><td><input type="submit" value="Submit"/></td>
        </tr>
    </table>
</form>
</body>
</html>

and my php file looks like this...

<?php
try
{
//open the database
$db = new PDO('sqlite:feedback.db');

$name = $_POST["name"]; 
$email = $_POST["email"]; 
$comments = $_POST["comments"]; 

//Insert record  
$db->exec("INSERT INTO feedback (name, email,comments) VALUES ('&name', '&email','&comments');");

//now output the data to a simple html table...
print "<table border=1>";
print "<tr><td>Id</td><td>Name</td><td>Email</td><td>Comments</td></tr>";
$result = $db->query('SELECT * FROM feedback');
foreach($result as $row)
{
print "<tr><td>".$row['feedbackid']."</td>";
print "<td>".$row['name']."</td>";
print "<td>".$row['email']."</td>";
print "<td>".$row['comments']."</td>";
}

print "</table>";

$db = NULL;
}
catch(PDOException $e)
{
print 'Exception : ' .$e->getMessage();
}

?>

And here is my table creation method...

CREATE TABLE feedback (feedbackid INTEGER PRIMARY KEY,name TEXT,email TEXT,comments TEXT);

The form is outputting the table headers and also a record that I manually entered using the Terminal but it won't insert a record in??? Can anyone see an easy mistake?

Disco

As a side note, please beware SQL injection: http://php.net/manual/en/security.database.sql-injection.php — Raoul, Jul 06 '11 at 11:08
I understand, this is one thing worth learning early on, check out what problems it can cause. — Raoul, Jul 06 '11 at 11:13

score 2 · Accepted Answer · answered Jul 06 '11 at 11:41

2

//Insert record
$db->exec("INSERT INTO feedback (name, email,comments) VALUES ('&name', '&email','&comments');");

One obvious issue is that you're doing no error checking:

if (1!=$db->exec("INSERT...)) { print "Error: " . implode('/',$db->errorInfo()) . "
\n"; }

While I'm not overly familiar with PDO, I don't see how your placeholders are getting mapped to the corresponding PHP variables - which is probably the cause of the error:

$bound=array(
     $_POST["name"], $_POST["email"], $_POST["comments"] 
);
$stm=$db->prepare("INSERT INTO feedback (name, email,comments) 
   VALUES (?, ?,?));"

if (!$stm || !$stm->execute($bound)) {
     print "Error: " . implode('/',$db->errorInfo()) . "<br />\n";
}

answered Jul 06 '11 at 11:41

symcbean

47,736
6
59
94

Ah brilliant got an error back saying I am attempting to write to a read only database :) – StuStirling Jul 06 '11 at 11:46
Ok I have changed the permission of the database file to read/write but now im getting this Error: 00000/14/unable to open database file but below it its still showing the record I manually entered – StuStirling Jul 06 '11 at 11:52
Its now working. I had to move the database up a directory and give that one read/write permissions for the inster query to work – StuStirling Jul 06 '11 at 12:14

Tim · Answer 2 · 2011-07-06T11:36:44.487

0

Check to make sure the PK column [feedbackid] is auto-generated in the database (AUTOINCREMENT).

EDIT: THis is not absolutely necessary but the lack of an explicit AUTOINCREMENT may be confusing the middleware. At least it cannot hurt to try adding an explicit AUTOINCREMENT to another table to test.

edited Jul 06 '11 at 11:36

answered Jul 06 '11 at 11:19

Tim

5,371
3
32
41

Yea it is. It is an auto incremented column – StuStirling Jul 06 '11 at 11:20
Can you post your table-creation statement. – Tim Jul 06 '11 at 11:26
added it to the original question – StuStirling Jul 06 '11 at 11:28
I don't see why I need to do that when it works anyway when I insert manually using terminal. Surely that means it is autoincremented? – StuStirling Jul 06 '11 at 11:36
I don't use PHP but use SQLite often. Are you sure you're supposed to wrap the variables in single-quotes? ('&name', '&email','&comments'); – Tim Jul 06 '11 at 11:38

score 0 · Answer 3 · answered Jun 28 '22 at 21:13

I had a similar problem in a PHP form where I used php to get data from a sqlite database and autofil fields.

You have

$db->exec("INSERT INTO feedback (name, email,comments) VALUES ('&name', '&email','&comments');");

I beleive that this should be

$db->exec("INSERT INTO feedback (name, email,comments) VALUES (" . $name . "," . $email . "," . $comments . ");");

The period "." character combinds strings. Because you need exec() to basically be a one line sting (sql statement), you want to combine your written text with your variables as a string. I am not sure what the & does in place of the $ in a variable name in php, but I know in some functions the variable prefix is actually : instead of $ but the : variables have to be "bound" from a value.

see what does 'period' character do in php

I am by no means an expret but I happen to have the same problem as you so I hope I helped :)

EDIT: I just noticed this article is quite old. I hope this helps someone in the future with newer PHP.

PHP SQlite Database Form

3 Answers3