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());

Vinod Ram

Vinod Ram has been in Software Industry since 2006 and has experience of over 16 years in Software Development & Project Management domain specialised majorly in LAMP stack & Open Source Technology, building enterprise level Web based Application, Large Database driven and huge traffic Websites and Project Management.

He loves to write information articles and blog to share his knowledge and experience with the outside world and help people to find solution for their problems.

www.linkedin.com/in/vinodkram