Solved

create a .db file instead of temporary table (sqlite and pdo)

Posted on 2014-01-16
9
994 Views
Last Modified: 2014-01-18
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28339587.html#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?


<?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);

Open in new window

0
Comment
Question by:rgb192
  • 5
  • 3
9 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 333 total points
ID: 39786053
Remove the word "TEMPORARY" from the CREATE TABLE statement.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39786064
Will all this code work with pdo:mysql
Almost certainly the answer is "no."  There are too many differences in the way the tables are defined and the queries prepared.  If you want to switch from one data base to another, which is a near-nonsense promise that some PDO proponents hold out, you will be facing a code conversion.  

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.
0
 

Author Comment

by:rgb192
ID: 39786123
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

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28340488.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39786150
Yeah, the SQLite data base seems to have its own way of obscuring things ;-)SqLite.db opened in a text editor
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 167 total points
ID: 39786195
As I pointed out in the other question, the SQLite db file format is not text, it has a lot of binary elements in it.  http://www.sqlite.org/fileformat.html
0
 

Author Comment

by:rgb192
ID: 39786315
okay part2 of this question was answered about creating the tables in the .db file

put part1:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28339587.html#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

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.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39786792
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.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 333 total points
ID: 39786901
And no sooner do I write that, than I go back to the SqLite queries, remove the colons (to see what the error message said) and SqLite worked perfectly without the colons.  Sheesh - now I wonder what I was doing this morning!
0
 

Author Closing Comment

by:rgb192
ID: 39791583
so remove 'temporary' and use same methods.

thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now