CODEWITHSUNDEEP
phpsqlite
user971741
user971741

Reputation:

Unable to retrieve data from SQLite using PHP

This is my first try to use SQLite, I am trying to retrieve data from SQLite but am not getting success neither its throwing any exceptions.

my respective code is as follows:

$sql_query = "SELECT * FROM `item` WHERE combo LIKE '" . $value . "' LIMIT 1;";
        try {
            /*** connect to SQLite database ***/
            $dbh = new PDO("sqlite:src/mydb.s3db");
            foreach ($dbh->query($sql_query) as $row) {
                $name = $row['name'];
                echo "name".$name; 
                $style = $row['style'];
                echo $style;
                $appr = $row['appr'];
                echo $appr;
                $style = $row['style'];
                echo $style;
            }

        } catch(PDOException $e) {
            //echo $e -> getMessage();
            echo "problem with DB";
            echo "<br>";
        }

Kindly guide me through this. Thank you.

//phpnifo();

PDO

PDO support enabled | PDO drivers mysql, sqlite, sqlite2

pdo_sqlite

PDO Driver for SQLite 3.x enabled | PECL Module version (bundled) 1.0.1 $Id: pdo_sqlite.c 293036 2010-01-03 09:23:27Z sebastian $ SQLite Library 3.3.7

Upvotes: 0

Views: 1079

Answers (1)

VolkerK
VolkerK

Reputation: 96189

a) Tell PDO that you want it to use exceptions for reporting errors

$dbh = new PDO("sqlite::memory:");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

b) instead of putting the payload paraemters into the sql statement use prepared parametrized statements

try {
    $stmt = $dbh->prepare( 'SELECT * FROM `item` WHERE combo LIKE ? LIMIT 1' );
    $stmt->execute( array($value) );
    foreach( $stmt as $row ) { ...

c) If you still don't get any output try code that unconditionally prints something. E.g. a SELECT Count(*) will always return at least one record (if no error occurs).

$stmt = $dbh->prepare( 'SELECT Count(*) as cnt FROM `item` WHERE combo LIKE ?' );
$stmt->execute( array($value) );
foreach( $stmt as $row ) {
    echo 'Count: ', $row['cnt'], "\n";
}
$stmt = null;

edit: self-contained example

<?php
ini_set('display_errors', true);
error_reporting(E_ALL);

echo "start\n";

try {
    $dbh = new PDO("sqlite::memory:");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    setup($dbh);


    $stmt = $dbh->prepare('SELECT * FROM item WHERE combo LIKE ? LIMIT 1');
    $stmt->execute( array('comboB') );
    foreach( $stmt as $row ) {
        echo
            $row['name'], " ",
            $row['style'], " ",
            $row['appr'], "\n"
        ;
    }
}
catch(Exception $ex) {
    var_dump($ex);
}
echo "done.\n";

function setup($dbh) {
    $dbh->exec('
        CREATE TABLE item (
            combo TEXT,
            name TEXT,
            style TEXT,
            appr TEXT
        )
    ');
    $stmt = $dbh->prepare('INSERT INTO item (combo,name,style,appr) VALUES (?,?,?,?)');
    $stmt->execute( array('comboA','nameA','styleA','apprA') );
    $stmt->execute( array('comboB','nameB','styleB','apprB') );
    $stmt->execute( array('comboC','nameC','styleC','apprC') );
}

Upvotes: 1

Related Questions