CODEWITHSUNDEEP
phpdrupal
TheLQ
TheLQ

Reputation: 15008

Query builder like Drupal's Database API?

I'm looking to branch out of plain mysql and mysqli and try to use something way better for database access thats not a DAO layer (adds too much complexity here).

When I did some Drupal development I had to use its Database API. It was really nice most of the time, allowing you to build queries and have it escape everything for you. PDO and mysqli prepared statements don't come close to the niceness and cleanliness of the Drupal Database API. And I still can't figure out how to, for example, use a prepared statement in mysqli to insert an unknown size of rows without building the query yourself, defeating the purpose of prepared statements. Its just getting in my way

Example from actual code of Drupal API

$query = db_select('tcsync_queue', 'q')
    ->condition('q.id', $post["tcsync_lastrecord"], '>')
    ->fields('q', array('id', 'uid', 'type', 'name', 'data'))
    ->execute();

foreach ($query as $item) {
    $updateitem = array(
        "id" => $item->id, 
        "uid" => $item->uid, 
        "type" => $item->type, 
        "name" => strtoupper($item->name),
        "data" => $item->data);
    ....
}

Is there a query builder that accomplishes some close to Drupal's query builder?

Upvotes: 1

Views: 908

Answers (4)

hakre
hakre

Reputation: 197732

You might be looking for the NotORM­Homepage PHP library. It's quite lightweight (which I think is what you ask for) and a stand-alone component so you don't have the burden with integration so much.

It works with PDO as the connection object:

include "NotORM.php";
$pdo = new PDO("mysql:dbname=software");
$db = new NotORM($pdo);

so it supports any database that is available in PDO.

You can then just run queries in an easy fashion, this is an example for a database with an application table:

foreach ($db->application() as $application) { // get all applications
    echo "$application[title]\n"; // print application title
}

When it comes to where and limits, it works like this:

$applications = $db->application()
    ->select("id, title")
    ->where("web LIKE ?", "http://%")
    ->order("title")
    ->limit(10)
;
foreach ($applications as $id => $application) {
    echo "$application[title]\n";
}

It does things well, which is not the case with similar looking code, for example the sql code in codeigniter.

Upvotes: 1

wyqydsyq
wyqydsyq

Reputation: 2020

CodeIgniter does this really well:

e.g.

Select:

$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);
$query = $this->db->get();

Insert:

$this->db->set('title',$title)->insert('mytable');

Update:

$this->db->where('id', $id)->set('title',$title)->update('mytable');

See: http://codeigniter.com/user_guide/database/active_record.html

Upvotes: 1

Kenaniah
Kenaniah

Reputation: 5201

https://github.com/kenaniah/insight/blob/master/classes/query.php can be modified to stand on its own.

Upvotes: 0

Itako
Itako

Reputation: 2069

I think you'd like the Doctrine 2's DBAL. I'll throw some saple code for you:

    $locationIds = array(1,2,3,4,5,6,7);

    $locationTreeDepthQb = $conn->createQueryBuilder();
    $locationTreeDepthQb->select("COUNT(*) as count")
        ->from('location_tree_data', 'd')
        ->where("d.tree_depth >= 2")
        ->andWhere("d.tree_id IN (?)");
    $stmt = $conn->executeQuery($locationTreeDepthQb->getSQL(), array($locationIds), array(Connection::PARAM_INT_ARRAY));
    $res = $stmt->fetch();

Please note how easy is to pass an array for a "IN" SQL statement in this query builder.

Documentation can be found here: http://www.doctrine-project.org/docs/dbal/2.2/en/

Upvotes: 1

Related Questions