Category: MVC

how to get sql query from facade DB::table in laravel 5

Normally we fetch the data as below

$dataresult = DB::table(‘sometable’)
->select(‘sometable.column1’, ‘sometable.column2’, ‘sometable.column3’, ‘sometable.column4’, ‘sometable.column5’)
->get();

Now if we want to get the actaul query from $dataresult using $dataresult->toSql(); // it will not work

The solution for this is first prepare the query without the get() method and then call the toSql() method as below.

$dataQuery = DB::table(‘sometable’)
->select(‘sometable.column1’, ‘sometable.column2’, ‘sometable.column3’, ‘sometable.column4’, ‘sometable.column5’);

$dataQuery->toSql(); // this will give you the actual query

After that you can fetch the result as below.
$dataresult = $dataQuery->get();

Read the next article to know how to get the sql with data binding

Set widget label from action file in symfony 1.4

In Action File

==========================================================

$this->form = new youFormclassname();

To set individual Label

$this->form->getWidget(‘widgetname’)->setLabel(‘widget Custom label’);

To set multiple field label

$this->form->getWidgetSchema()->setLabels(array(
‘field_name_1’    => ‘Field 1 Custom Lable’,

‘field_name_2’    => ‘Field 2 Custom Lable’,

}

++++++++++++++++++++++++++++++++++++++

In Template File

============================================

<?php echo $form[‘field_name_1’]->renderLabel() ?>

configure database using propel

$ ./symfony configure:database --name=propel --class=sfPropelDatabase "mysql:host=localhost;dbname=dbname" username userpassword

Self join criteria in symfony

For Example :
SELECT tbl1.id FROM tbl1 a LEFT JOIN tbl1 b ON a.somecolumnname = b.somecolumn ORDER BY a.somecolumn DESC

The criteria would be as follow

$c=new Criteria();
$c->addAlias(‘a’, ‘tbl1’);
$c->addAlias(‘b’, ‘tbl1’);
$c->addSelectColumn(‘b.id’);
$c->addSelectColumn(‘a.tbl1_id’);
$c->addDescendingOrderByColumn(‘a.somecolumn’);
$c->addJoin(‘a.somecolumnname’,’b.somecolumn’,Criteria::LEFT_JOIN); // Specify your criteria LEFT, RIGHT or INNER
$rs = tbl1Peer::doSelect($c);

Thanks Cheers 🙂

select multiple column from multiple table in cakePHP

$markers = $this->Post->find(‘all’, array( ‘joins’ => array(
array(
‘table’ => ‘cp_user’,
‘alias’ => ‘users’,
‘type’ => ‘left’,
‘foreignKey’ => false,
‘conditions’=> array(‘Post.postauthor = users.id’)
)
),
‘conditions’=>’Post.poststatus = 1’,
‘fields’=>array(‘Post.postid’,
‘Post.postdate’,
‘Post.posttitle’,
‘Post.postmessage’,
‘Post.poststatus’,
‘Post.commentstatus’,
‘Post.commenttotal’,
‘Users.id’,
‘Users.firstname’
)
)
);

$this->set(‘cp_post’,$markers);

This will generate the below query in mysql
—————————————————-
SELECT `Post`.`postid`, `Post`.`postdate`, `Post`.`posttitle`, `Post`.`postmessage`, `Post`.`poststatus`, `Post`.`commentstatus`, `Post`.`commenttotal`, `Users`.`id`, `Users`.`firstname` FROM `cp_posts` AS `Post` left JOIN cp_user AS `users` ON (`Post`.`postauthor` = `users`.`id`) WHERE `Post`.`poststatus` = 1