Executing Raw SQL in an unknown db

Question

Executing Raw SQL in an unknown db

hkun May '13

Created May '13	Last Reply May '13	Replies 1	Views 1954	Votes 3

hkun 7.3k

May '13

Hi,

Thanks for giving us this wonderful framework. I am learning it and finding it quite useful.

I have a question regarding executing RAW SQL. There is an example given in help at https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql

This example uses Robots model.

However, my application is such that it allows users to connect on the fly to a database dynamically. So, I don't in advance know the model on which to execute Raw SQL. User is allowed to type any query and obtain the results of it

Can you tell me how can I accomplish this use case in Phalcon?

Thanks!

3

Andres Gutierrez · Answer 1 · 2013-05-24T11:51:14-07:00

You can pass the schema as a parameter to the method that return the resultset:

<?php

use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class Robots extends Phalcon\Mvc\Model
{
    public static function findByCreateInterval($params=null)
    {
        // A raw SQL statement
        if (isset($params['schema'])) {
            $sql = "SELECT * FROM " . $params['schema'] . ".robots WHERE id > 0";
        } else {
            $sql = "SELECT * FROM robots WHERE id > 0";
        }

        // Base model
        $robot = new Robots();

        // Execute the query
        return new Resultset(null, $robot, $robot->getReadConnection()->query($sql));
    }
}