Solved

php pdo and sqlite tutorial

Posted on 2014-01-15
7
1,363 Views
Last Modified: 2014-01-16
Could you please show me a php pdo sqlite tutorial that teaches how to create ,select, insert.
0
Comment
Question by:rgb192
  • 4
  • 2
7 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39783470
The SQL commands are going to be very, very much like MySQL.  SQL is SQL and even though there may be minor differences, they are pretty obvious in practice.  MySQL uses the LIMIT clause, vs MSSQL uses the TOP clause to achieve the same thing.  There will undoubtedly be some similar things with SQLite.

The PDO part of it is mostly covered in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

This looks pretty good, even if it's kind of basic:
http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/

Here's the way to make the PDO connection and prepare a query.

<?php // pdo_error_example.php
error_reporting(E_ALL);
echo '<pre>';


// USE PDO TO CAUSE AN ERROR SO WE CAN SEE WHAT IT LOOKS LIKE
// MAN PAGE: http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/


// 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 (SPARSE) PDO OBJECT
echo "PDO OBJECT: ";
var_dump($pdo);
echo PHP_EOL;


// PREPARE THE QUERY -- THIS IS THE RIGHT WAY, WITH NAMED PLACEHOLDERS
$sql
=
"
INSERT INTO stock
(  symbol,  price,  xtime ) VALUES
( :symbol, :price, :xtime )
"
;

// THIS PREPARES THE QUERY (ONLY NEEDED ONCE)
try
{
    $res = $pdo->prepare($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}

Open in new window

Hope that helps you get started, ~Ray
0
 

Author Comment

by:rgb192
ID: 39784227
PDO OBJECT: object(PDO)#1 (0) {
}

object(PDOException)#3 (8) {
  ["message":protected]=>
  string(54) "SQLSTATE[HY000]: General error: 1 no such table: stock"
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  string(5) "HY000"
  ["file":protected]=>
  string(31) "C:\wamp\www\test\ray-sqlite.php"
  ["line":protected]=>
  int(48)
  ["trace":"Exception":private]=>
  array(1) {
    [0]=>
    array(6) {
      ["file"]=>
      string(31) "C:\wamp\www\test\ray-sqlite.php"
      ["line"]=>
      int(48)
      ["function"]=>
      string(7) "prepare"
      ["class"]=>
      string(3) "PDO"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(1) {
        [0]=>
        string(86) "
INSERT INTO stock
(  symbol,  price,  xtime ) VALUES
( :symbol, :price, :xtime )
"
      }
    }
  }
  ["previous":"Exception":private]=>
  NULL
  ["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "HY000"
    [1]=>
    int(1)
    [2]=>
    string(20) "no such table: stock"
  }
}

Open in new window




I have done this tutorial previously and I could not find sqlite.db file in
http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

How could I test with sqlite.db file
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39784315
I think you can just start with the script above and begin by writing a CREATE TABLE statement, then some INSERT statements, then some SELECT queries to do proof of concept.

Your CREATE TABLE statement would be exactly the same as the MySQL CREATE TABLE statement for a table named "stock" with columns named symbol, price, and xtime.  Probably symbol VARCHAR(8), price DECIMAL(10,3), xtime DATETIME or TIMESTAMP.
0
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

 
LVL 9

Assisted Solution

by:rinfo
rinfo earned 150 total points
ID: 39784903
I think this link well caters to your query.
http://juanmanuelllona.blogspot.in/
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 350 total points
ID: 39785519
Here is a teaching example using PDO and SQLite.  It works fine on my server, but it's worth noting that SQLite has some quirky things about it.  

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.

Another interesting difference is the set of named data types and the way SQLite handles AUTO_INCREMENT keys (which it calls AUTOINCREMENT, without the underscore).  You can name the key anything you want, and you can select it by using "rowid" in the query.
Absent the use of an alias with the AS keyword, the query will return the name you gave to the key when the table was created.

So overall, a few more differences than I expected, but still useable with PDO.

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

Author Closing Comment

by:rgb192
ID: 39785910
I can use this sqlite code and step through lines with ide, seeing changes on global/local variables.

Thanks.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39786952
For anyone coming upon this question in the future...

After re-testing my scripts, I have determined that the part about the colons was a red herring.   Neither MySQL nor SqLite PDO requires the colon prefix in the array keys; both are tolerant of the colon prefix if it is present.

The colons are required in the SQL statements.
0

Featured Post

What Security Threats Are You Missing?

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.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 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

13 Experts available now in Live!

Get 1:1 Help Now