Laravel 5.2 select data unknown variable

Question

My function in controller for get data when user search ajax.

This is my code:

$receiptNum = Request::get('receiptNum');
// echo $receiptNum = 123456
DB::connection()->enableQueryLog();
$q = DB::table('tbl_receipt AS r')
            ->join('company AS com', 'r.com_id', '=', 'com.com_id')
            ->join('branches AS b', 'com.b_id', '=', 'b.b_id')
            ->join('employee AS e', 'r.created_by', '=', 'e.e_id')
            ->where('r.receipt_code','=',$receiptNum)
            ->get();
$query = DB::getQueryLog();
var_dump($query);

I don't know what missing in my code, it's show raw sql like this:

SELECT *
FROM       "tbl_receipt" AS "r"
INNER JOIN "company"     AS "com" ON "r"."com_id" = "com"."com_id"
INNER JOIN "branches"    AS "b"   ON "com"."b_id" = "b"."b_id"
INNER JOIN "employee"    AS "e"   ON "r"."created_by" = "r"."e_id"
WHERE "r"."receipt_code" = ?

I trying to replace $receiptNum by num 123 for test, it's also show ? in raw sql, please help me to solve it.

Have you seen this? http://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string — LorenzoBerti, Aug 24 '16 at 09:50
I think it's not problem with variable `$receiptNum` because when I replace my code with number `123` direct to SQL ,it's still show log `?` the same — ching, Aug 24 '16 at 09:51
yes, I try ready,it's still show `?` at SQL and sure `$receiptNum` have value. I search a lot a bout it but still not help — ching, Aug 24 '16 at 09:52
No, It isn't problem with variable because it will return error, but the ? is because query builder make a statement — LorenzoBerti, Aug 24 '16 at 09:52
if this solution resolved your problem, mark it, if other people have same problem can find solution. — LorenzoBerti, Aug 24 '16 at 12:49

LorenzoBerti · Answer 1 · 2016-08-24T11:10:38.637

The "?" there are because it is a Statement. If you want a string you have to replace.

I was inspired by https://gist.github.com/JesseObrien/7418983

and for example I make this.

    $q = DB::table('tbl_receipt AS r') 
->join('company AS com', 'r.com_id', '=', 'com.com_id') 
->join('branches AS b', 'com.b_id', '=', 'b.b_id') 
->join('employee AS e', 'r.created_by', '=', 'e.e_id') 
->where('r.receipt_code','=','123') ; 

$sql = $q->toSql(); 
// $bindings = $q->getBindings(); 
foreach($q->getBindings() as $binding) 
{ 
$value = is_numeric($binding) ? $binding : "'".$binding."'"; 
$sql = preg_replace('/\?/', $value, $sql, 1); 
} 
var_dump($sql);

Laravel 5.2 select data unknown variable

1 Answers1