I use
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
to get all the rows of my table.
The schema is defined as "id INTEGER PRIMARY KEY, TEXT header, year INTEGER, price REAL"
String from fetchAll Results
array(4) { [0]=> string(1) "1" [1]=> string(14) "The Dark Night" [2]=> string(4) "2008" [3]=> string(5) "19.95" }
Why are all data types returned as strings? I want them to be returned as defined in the scheme. I understand the “causeless” nature of SQLite, but they define restricted data types as TEXT, INTEGER, and REAL. How can I get the data that will be returned with the specified data types? I don't want to iterate over each line and convert it with PHP - this seems too slow.
Full test code as follows:
<? class sqlite_test { function __construct() { $this->dbase_filename = "test.sqlite"; $this->init_dbase(); } function init_dbase() { $this->pdo = new PDO("sqlite:".$this->dbase_filename); } function open_table() { $table = "dvds"; if ( ! ($test_to_see_if_table_exists = $this->pdo->query("SELECT 1 from $table")) ) { $schema = "id INTEGER PRIMARY KEY, title TEXT, year INTEGER, price REAL"; $query = "CREATE TABLE $table ($schema)"; $this->pdo->exec($query); } return $test_to_see_if_table_exists; } function add_test_records() { $query[]='INSERT INTO dvds (id, title, year, price) VALUES (null, "The Dark Night", 2008, 19.95)'; $query[]='INSERT INTO dvds (id, title, year, price) VALUES (null, "The Wizard of Oz", 1939, 9.95)'; $query[]='INSERT INTO dvds (id, title, year, price) VALUES (null, "Jaws", 1977, 6.95)'; $this->pdo->exec( join(';', $query) ); } function dump_test_records() { $query = "SELECT * FROM dvds"; if ($statement = $this->pdo->prepare($query)) { $statement->execute(); $rows = $statement->fetchAll(PDO::FETCH_ASSOC); echo "<pre>"; var_dump($rows); echo "</pre>"; } } function main() { if ( ! $this->open_table() ) { $this->add_test_records(); } $this->dump_test_records(); } } $test = new sqlite_test(); $test->main();