Solved

php pdo and sqlite tutorial

Posted on 2014-01-15
7
1,384 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 109

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 109

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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 109

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 109

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to count in a table in php 22 34
PHP populating an array. 4 23
PHP Form Calculate Total Price 10 41
How do I fix this UPDATE error? 7 20
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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 look for a specific file type in a local or remote server directory using PHP.

786 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