Retrieving data

There are multiple ways, how we can retrieve data from a table.To fetch data from the table, we can use the sqlite_fetch_array().
The sqlite_fetch_array() does two things. Moves the pointer to the next row and returns that row from the result set. The row is is an array. We can control how the data is organized in the array, by using three result type flags. SQLITE_ASSOC, SQLITE_NUM, SQLITE_BOTH.

1. Retrieving data by using 'SQLITE_ASSOC' result type flag

<?php
   
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);


    
$query = "SELECT Name FROM scanftree";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");

//SQLITE_ASSOC
$row = sqlite_fetch_array($result, SQLITE_ASSOC); 
print_r($row);


sqlite_close($dbhandle);

?>

Using the SQLITE_ASSOC flag we will have an associative array

result look like

Array
(
    [Name] => ankit
)

2. Retrieving data by using 'SQLITE_NUM' result type flag

<?php
   
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);


    
$query = "SELECT Name FROM scanftree";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");

//SQLITE_NUM
$row = sqlite_fetch_array($result, SQLITE_NUM); 
print_r($row);

sqlite_close($dbhandle);
?>

Using the SQLITE_NUM, we will have a numerical array.

result look like

Array
(
    [0] => ankit
)

3. Retrieving data by using 'SQLITE_BOTH' result type flag

<?php
   
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);


    
$query = "SELECT Name FROM scanftree";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");


//SQLITE_BOTH
$row = sqlite_fetch_array($result, SQLITE_BOTH); 
print_r($row);


sqlite_close($dbhandle);

?>

The SQLITE_BOTH option is the default option also. Using this flag, we will have both arrays with associative indexes and numerical indexes.

result look like

Array
(
    [0] => ankit
    [Name] => ankit
)

In the following example, we will traverse the data using the associative indexes.

---

Retrieving data by using 'SQLITE_ASSOC' result type flag

while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
    echo $row['Name'];
    echo "<br/>";
}

here $row['Name'] means column_name Name.

Retrieving data by using 'SQLITE_ASSOC' result type flag

while ($row = sqlite_fetch_array($result, SQLITE_NUM)) {
    echo $row[0];
    echo "<br/>";
}

here $row[0]; means first column .