1. Count the number of rows and columns in our result set.

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

if (!$dbhandle) die ($error);
    
$query = "SELECT * FROM Friends";
$result = sqlite_query($dbhandle, $query);
if (!$result) die("Cannot execute query.");


$rows = sqlite_num_rows($result);
$cols = sqlite_num_fields($result);

echo "The result set has $rows rows and 
      $cols columns";

sqlite_close($dbhandle);

?>

Explanation :

The functions get the numbers from the result set. This means, that the number of rows and columns calculated depend on the SQL statement, that we use to obtain the data from the database table.

$query = "SELECT * FROM Friends LIMIT 2";

Here we build the SQL query. we limit the number of rows to 2.

2. Display table with the names of the columns.

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

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

$rows = sqlite_num_rows($result);

$field1 = sqlite_field_name($result, 0);
$field2 = sqlite_field_name($result, 1);

echo "<table style='font-size:12;font-family:verdana'>";
echo "<thead><tr>";
echo "<th align='left'>$field1</th>";
echo "<th align='left'>$field2</th>";
echo "</tr></thead>";

for ($i = 0; $i < $rows; $i++) {
    $row = sqlite_fetch_array($result, SQLITE_NUM); 
    echo "<tr>";
    echo "<td>$row[0]</td>";
    echo "<td>$row[1]</td>";
    echo "</tr>";
}

echo "</table>";

sqlite_close($dbhandle);

?>

Explanation :

$field1 = sqlite_field_name($result, 0);
$field2 = sqlite_field_name($result, 1);

The sqlite_field_name() returns the name of a particular field. Our SQL query returns two columns. The first function returns 'Name', the second 'Sex'.

echo "<thead><tr>";
echo "<th align='left'>$field1</th>";
echo "<th align='left'>$field2</th>";
echo "</tr></thead>";

We put the two column names into the html table header.

for ($i = 0; $i < $rows; $i++) {
    $row = sqlite_fetch_array($result, SQLITE_NUM); 
    echo "";
    echo "$row[0]";
    echo "$row[1]";
    echo "";
}

We use yet another way to retrieve data from the result set. We count the number of rows. And use the for cycle to go through the data.

3. Display column types of the Friends table.

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

if (!$dbhandle) die ($error);
    
$cols = sqlite_fetch_column_types('Friends', $dbhandle, SQLITE_ASSOC);

foreach ($cols as $column => $type) {
    echo "Column name: $column Column type: $type";
    echo "<br>";
}

sqlite_close($dbhandle);
?>

Explanation :

$cols = sqlite_fetch_column_types('Friends', $dbhandle, SQLITE_ASSOC);

The sqlite_fetch_column_types() function returns an array of column types from a particular table. The table name is the first parameter of the function.

foreach ($cols as $column => $type) {
    echo "Column name: $column Column type: $type";
    echo "<br>";
}

We go through the array using the foreach keyword.