How do I insert into SQLite with PDO extension?

Question

I'm having a bit of trouble inserting into a sqlite3 database with pdo. You'll have to excuse my ignorance with PDO, it seems so foreign coming from Python's database interface.

So here's my problem. I have a simple insert:

$dbh = new PDO('sqlite:vets.db');
    $count = $dbh->exec("INSERT INTO vets(name,email,clinic,streetname,citystatezip,link,phone,fax,animal,region,visible) VALUES ($name,$email,$clinic,$streetname,$citystatezip,$link,$phone,$fax,$animal,$region,$visible)");
    $dbh = null;

I simply want to execute that SQL command on my database and be done with it. Though executing this script causes no errors, it never updates the database. I've tried all sorts of permissions on the database itself, even made it 777 but it doesn't make a difference.

Could someone help me?

Answer 1

One of the great benefits of PDO is that you can create prepared statements. Here's some code from a PHP project of mine:

$qry = $db->prepare(
    'INSERT INTO twocents (path, name, message) VALUES (?, ?, ?)');
$qry->execute(array($path, $name, $message));

As you can see, I use ? where I want to insert a value, then I execute the query with an array of values that should be put in place of the question marks.

If you do this, your query will be much safer, and more likely to work (since a missing value would stop your query from working if you insert variables directly in the query like you do.)

Answer 2

You can have an error in your SQL query. You could print it and then try to execute it in some SQLite GUI interface like SQLite Database Browser.

// you can skip PDO part for now, because we know it doesn't work
// $dbh = new PDO('sqlite:vets.db');
$query = "INSERT INTO vets(name,email,clinic,streetname,citystatezip,link,phone,fax,animal,region,visible) VALUES ($name,$email,$clinic,$streetname,$citystatezip,$link,$phone,$fax,$animal,$region,$visible)";
echo $query;
// $count = $dbh->exec($query);
// $dbh = null;

I see that you are not wrapping your values in quotes, probably that's the source of the problem. Maybe some typos in table field names as well. All will come out once you actually see the query.