?
Solved

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

Posted on 2014-01-16
9
Medium Priority
?
1,143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 111

Assisted Solution

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 111

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
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 668 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 111

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 111

Accepted Solution

by:
Ray Paseur earned 1332 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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 dynamically set the form action using jQuery.
Suggested Courses

800 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