Native QueriesΒΆ

PHPLucidFrame provides several db helper functions to retrieve data from your database using native queries. You can use the following functions to retrieve your data:

  • db_query() which peforms a query on the database.
  • db_fetchAssoc() which fetchs a result row as an associate array.
  • db_fetchArray() which fetchs a result row as a numeric array.
  • db_fetchObject() which fetchs a result row as an object.
  • db_fetch() which performs a query on the database and return the first field value only.
  • db_fetchResult() which performa a query on the database and return the first result row as object.
  • db_extract() which performs a query on the database and return the array of all results.

Note

Instead of using native query to fetch data, QueryBuilder is recommended. See Query Builder usage.

The following is an example to retrieve multiple result rows:

$sql = 'SELECT * FROM ' . db_table('post') . ' ORDER BY title';
if ($result = db_query($sql)){
    while($row = db_fetchAssoc($result)){
        // do somethings here...
    }
}

// Extract all data into an array of objects
// db_extract() invokes db_fetchObject() by default internally
$sql = 'SELECT * FROM ' . db_table('post') . ' ORDER BY title';
$posts = db_extract($sql); // The second or third argument can be given one of these: LC_FETCH_OBJECT (default), LC_FETCH_ASSOC, LC_FETCH_ARRAY
_pr($posts);

// Extract all data into key/value pair of array
$sql = 'SELECT id AS key, title AS value FROM ' . db_table('post') . ' ORDER BY title';
$posts = db_extract($sql);
_pr($posts);
/*
array(
  $id => $title
)
*/

The following is an example to retrieve a single result:

// Retrieving a single-row result
$sql = 'SELECT * FROM ' . db_table('post') . ' WHERE id = :id';
if ($post = db_fetchResult($sql, array(':id' => $id))) {
    _pr($post);
    // $post->id;
    // $post->title;
}

// Retrieving the result count
$sql = 'SELECT COUNT(*) FROM ' . db_table('post');
$count = db_count($sql);

// Retrieving a field
$sql = 'SELECT MAX(id) FROM ' . db_table('post');
$max = db_fetch($sql);