Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1566
  • Last Modified:

php pdo and sqlite tutorial

Could you please show me a php pdo sqlite tutorial that teaches how to create ,select, insert.
0
rgb192
Asked:
rgb192
  • 4
  • 2
2 Solutions
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
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
 
Ray PaseurCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rinfoCommented:
I think this link well caters to your query.
http://juanmanuelllona.blogspot.in/
0
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
I can use this sqlite code and step through lines with ide, seeing changes on global/local variables.

Thanks.
0
 
Ray PaseurCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now