Search for Resources and Solutions

the_db_request in midrub frontend themes

In Midrub's components for both Auth, User and Admin section you have to use the CodeIgniter's models to create sql queries because i have no inspiration yet for an alternative. In Midrub Apps, user's themes and admin's themes you have to use too CodeIgniter's models. But for Midrub's frontend themes i will make an exception and you can create sql queries by using the Frontend's functions.

In this article i will explain how to use advanced SELECT  queries by using the Frontend's function the_db_request. The function the_db_request uses as parameter a multilevel array.

To write a simply sql request like:

                                                
SELECT id FROM tables


The the_db_request way is:


the_db_request('tables', 'id');

As you seen in the example above, first parameter is the table, second is what i want to select. I can use even * or multiple words divided by quotes.


SELECT id FROM tables WHERE id > '10'

The the_db_request way is:


the_db_request('tables', 'id', array(
'id >' => 10 
));

As you seen in the example above, third parameter is the where parameters and you can use there multiple values.


SELECT id FROM tables WHERE id IN (10, 20, 30, 40)

The the_db_request way is:


the_db_request('tables', 'id', array(), array(
'id' => array(10, 20, 30, 40)
));

As you seen in the example above, fourth parameter is the where in parameter and you can use there multiple values.


SELECT id FROM tables WHERE name LIKE '%midrub%'

The the_db_request way is:


the_db_request('tables', 'id', array(), array(), array(
'name' => 'midrub'
));

As you seen in the example above, fifth parameter is the where like parameter and you can use there multiple values.

Now lets see an example with JOIN:


SELECT id FROM tables LEFT JOIN table ON tables.id=table.id WHERE name LIKE '%midrub%'

The the_db_request way is:


the_db_request('tables', 'id', array(), array(), array(
'name' => 'midrub'
),
array(
  array(
       'table' => 'table',
       'condition' => 'tables.id=table.id',
       'join_from' => 'LEFT'
  )              
));

As you seen in the example above, sixth parameter is a multilevel array with join parameters.

And lets see the last supported parameter:


SELECT id FROM tables WHERE name LIKE '%midrub%' ORDER BY id DESC LIMIT 0, 10

The the_db_request way is:


the_db_request('tables', 'id', array(), array(), array(
'name' => 'midrub'
),
array(),
array(
    'order' => array('id', 'desc'),
    'start' => 0,
    'limit' => 10
));

In the last parameter i'm going to add even the GROUP BY parameter.

I've invented the function in several minutes and my goal will be to add cache for queries when will be used this function.

If you like, you can use it, otherwise you can use CodeIgniter's models even in the Frontend's themes.

Was this article helpful?