Tag: laravel

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

We can get the SQL query from DB sql query in Laravel 5

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

$dataQuery->toSql(); // this will give you the actual query but this will not give the result with actual data values.

Now What ??

To get the Data binded with the sql statements , you should use the below function

$dataQuery->getBindings(); // This will return an array of data binded to the sql statement.

Now to get the final sql statement, you can use the below function and pass $dataQuery->toSql() and $dataQuery->getBindings() to the functions written below , which you can define it in your helper class or common functions class file.

function getSqlWithBinding($sql,$bindDataArr){
foreach($bindDataArr as $binding)
{
$value = is_numeric($binding) ? $binding : “‘”.$binding.”‘”;
$sql = preg_replace(‘/\?/’, $value, $sql, 1);
}
return $sql;
}

$sqlwithData = getSqlWithBinding($dataQuery->toSql(),$dataQuery->getBindings());
or something like below if you have defined getSqlWithBinding function in common class.

$sql = CommonFunctionClass::getSqlWithBinding($dataQuery->toSql(),$dataQuery->getBindings());