rgb192
asked on
create a .db file instead of temporary table (sqlite and pdo)
https://www.experts-exchange.com/questions/28339587/php-pdo-and-sqlite-tutorial.html?anchorAnswerId=39785519#a39785519
1.
Will all this code work with pdo:mysql, because once this code uses an external .db file, I would like to substitute using pdo:mysql (a future question)?
2.
.db file is never used
Could you create a .db file instead of using temporary table?
1.
Will all this code work with pdo:mysql, because once this code uses an external .db file, I would like to substitute using pdo:mysql (a future question)?
2.
.db file is never used
Could you create a .db file instead of using temporary table?
<?php // RAY_sqlite_example.php
error_reporting(E_ALL);
echo '<pre>';
// USE SQLITE
// REF: http://www.sqlite.org/about.html
// WHY: http://www.sqlite.org/mostdeployed.html
// SET UP OUR PHP DATA OBJECT USING SQLITE
$db_name = 'SqLite.db';
$db_drvr = 'sqlite';
try
{
$pdo = new PDO("$db_drvr:$db_name");
}
catch(PDOException $e)
{
var_dump($e);
}
// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
// SHOW THE (RATHER SPARSE) PDO OBJECT
echo "PDO OBJECT: ";
var_dump($pdo);
echo PHP_EOL;
// A QUERY TO CREATE A TABLE http://www.sqlite.org/datatype3.html
$sql
=
"
CREATE TEMPORARY TABLE stock
( symbol TEXT
, price REAL
, xtime TEXT
, key INTEGER PRIMARY KEY AUTOINCREMENT
)
"
;
// RUN QUERY TO CREATE THE TABLE
try
{
$pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($pdos);
// PREPARE THE QUERY WITH NAMED PLACEHOLDERS THAT MATCH PHP ARRAY KEYS
$sql
=
"
INSERT INTO stock
( symbol, price ) VALUES
( :symbol, :price )
"
;
// THIS PREPARES THE QUERY (ONLY NEEDED ONCE)
try
{
$sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);
// SOME TEST DATA FOR OUR STOCK TABLE
$names = array
( array( ':symbol' => 'YHOO', ':price' => 41.07 )
, array( ':symbol' => 'GOOG', ':price' => 1148.62 )
, array( ':symbol' => 'AAPL', ':price' => 557.36 )
, array( ':symbol' => 'JCP', ':price' => 6.92 )
)
;
// LOADING OUR ARRAYS OF TEST DATA INTO THE TABLE
foreach ($names as $name)
{
// USE THE ARRAY OF KEYWORD => VALUE TO ATTACH symbol AND price
try
{
// RUN THE QUERY TO INSERT THE ROW
$sth->execute($name);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// GET THE SQLITE AUTOINCREMENT ID OF THE RECORD JUST INSERTED
$rowid = $pdo->lastInsertId();
echo "PDO INSERTED A ROW CONTAINING <b>" . $name[':symbol'] . ' ' . $name[':price']. "</b> WITH AUTO_INCREMENT ID = $rowid" . PHP_EOL;
}
// A QUERY TO RETRIEVE INFORMATION FROM THE TABLE
$sql
=
"
SELECT rowid, symbol, price FROM stock WHERE price > :value LIMIT 3
"
;
// PREPARES THE QUERY
try
{
$sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);
// USE ARRAY OF KEYWORD => VALUE TO ATTACH QUERY PARAMS
$parms = array( ':value' => 500 );
try
{
// RUN THE QUERY TO SELECT THE ROWS
$sth->execute($parms);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// RETRIEVE THE RESULTS IN THE FORM OF ANONYMOUS OBJECTS
while ($obj = $sth->fetch(PDO::FETCH_OBJ))
{
print_r($obj);
}
// A QUERY TO REMOVE INFORMATION FROM THE TABLE
$sql
=
"
DELETE FROM stock WHERE price < :value
"
;
// PREPARES THE QUERY
try
{
$sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);
// USE ARRAY OF KEYWORD => VALUE TO ATTACH QUERY PARAMS
$parms = array( ':value' => 500 );
try
{
// RUN THE QUERY TO DELETE THE ROWS
$sth->execute($parms);
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// A QUERY TO SHOW THE INFORMATION LEFT IN THE TABLE
$sql
=
"
SELECT rowid, symbol, price FROM stock ORDER BY symbol
"
;
// PREPARES THE QUERY
try
{
$sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);
try
{
// RUN THE QUERY TO SELECT THE ROWS (NO PARAMS NEEDED HERE)
$sth->execute();
}
catch(PDOException $exc)
{
var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);
}
// RETRIEVE THE RESULTS IN THE FORM OF ANONYMOUS OBJECTS
while ($obj = $sth->fetch(PDO::FETCH_OBJ))
{
print_r($obj);
}
// A QUERY TO CAUSE AN ERROR AND SHOW THE EXCEPTION OBJECT
$sql = "SELECT oopsie FROM stock ORDER BY symbol";
// PREPARES THE QUERY
try
{
$sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
var_dump($exc);
}
// SHOW THAT THE PREPARED STATEMENT HANDLE IS MEANINGLESS AFTER THE EXCEPTION
var_dump($sth);
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I took out the word 'temporary' in the table create
I attached SqLite.db
using notepad++
and see many [NULL] characters
I open with
SQLite Database browser
and can read the tables
so I opened a new question to create a readable (using notepad++) file
https://www.experts-exchange.com/questions/28340488/readable-notepad-sqlite-db-file.html
I attached SqLite.db
using notepad++
and see many [NULL] characters
I open with
SQLite Database browser
and can read the tables
so I opened a new question to create a readable (using notepad++) file
https://www.experts-exchange.com/questions/28340488/readable-notepad-sqlite-db-file.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
okay part2 of this question was answered about creating the tables in the .db file
put part1:
https://www.experts-exchange.com/questions/28339587/php-pdo-and-sqlite-tutorial.html?anchorAnswerId=39785519#a39785519
is there any pdo classes/properties/methods that are sqlite specific
not the parameters (sql statements)
because Ray may have wrote that execute() was only for sqlite
put part1:
https://www.experts-exchange.com/questions/28339587/php-pdo-and-sqlite-tutorial.html?anchorAnswerId=39785519#a39785519
is there any pdo classes/properties/methods
not the parameters (sql statements)
because Ray may have wrote that execute() was only for sqlite
One of the things that differs from the MySQL PDO implementation is the use of the colon prefix in the execute() array keys. MySQL PDO does not use the colonic in the array keys, just in the query placeholders. SQLite PDO requires the colon prefix in the array keys. The error message that it emits if you're missing the colon is really opaque.
The PDOStatement::Execute() method works in PDO so it is not specific to any particular underlying data base. But the inputs you give to the underlying data base may be different, depending on the data base you've chosen. Here is an example of one of the differences. In both MySQL and SqLite, a prepared query looks something like this. Note the use of the colons. This tells PDO where to substitute values when the execute() method is called,
INSERT INTO stock ( symbol, price ) VALUES ( :symbol, :price )
For MySQL you can pass an array like this to the execute() method.
array( 'symbol' => 'YHOO', 'price' => 41.07 )
For SqLite, however, the colons are required in the array keys.
array( ':symbol' => 'YHOO', ':price' => 41.07 )
In testing MySQL today I've found that it can use the colon prefix on the array keys, but SqLite cannot live without the colons.
INSERT INTO stock ( symbol, price ) VALUES ( :symbol, :price )
For MySQL you can pass an array like this to the execute() method.
array( 'symbol' => 'YHOO', 'price' => 41.07 )
For SqLite, however, the colons are required in the array keys.
array( ':symbol' => 'YHOO', ':price' => 41.07 )
In testing MySQL today I've found that it can use the colon prefix on the array keys, but SqLite cannot live without the colons.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so remove 'temporary' and use same methods.
thanks
thanks
You might think about creating an abstraction layer of some sort, just implementing your basic DB verbs in the form of PHP function calls. If your DB access is fairly simple that might work.