0

## LARAVEL 原生SQL 帶變數

`````` public function month(Request \$request)
{
\$month=

\$sqlTmp=sprintf('select A.area,A.id,A.name,A.vendor,A.stock,A.unit,
B.d,C.e,D.f,E.g
from total as A

left join (
select total_id,sum(num) as f
from warehouse2

where month(purchase_at) < 9
group by total_id
) as D on D.total_id=A.id

left join (
select total_id,sum(outnum) as g
from warehouse2
where month(ship_at) < 9
group by total_id
) as E on E.total_id=A.id

left join (
select total_id,sum(num) as d
from warehouse2
where month(purchase_at) = 9
group by total_id
) as B on B.total_id=A.id

left join (
select total_id,sum(outnum) as e
from warehouse2
where month(ship_at) = 9
group by total_id
) as C on C.total_id=A.id
where A.area>=19
and A.area<=55
order by A.area,A.id');

\$a=DB::select(\$sqlTmp);

return view('Report/month',compact('a'));
}
``````

### 3 個回答

1
jasonchien
iT邦新手 5 級 ‧ 2021-10-25 10:27:16

`\$month = \$request->input('month')` 就會拿到你要的數值

p0625 iT邦新手 5 級 ‧ 2021-10-25 11:29:05 檢舉

``````\$num = 5;
\$location = 'tree';

\$format = 'There are %d monkeys in the %s';
echo sprintf(\$format, \$num, \$location);  // There are 5 monkeys in the tree
``````

p0625 iT邦新手 5 級 ‧ 2021-10-25 12:34:56 檢舉

``````
public function fgc(Request \$request)
{

\$dt = Carbon::now();

\$b = \$dt->month;

\$sqlTmp = sprintf('select A.area,A.id,A.name,A.vendor,A.stock,A.unit,
B.d,C.e,D.f,E.g

from total as A
left join (
select total_id,sum(num) as f
from warehouse2

where month(purchase_at) <\$b
AND year(purchase_at) = 2020
group by total_id
) as D on D.total_id = A.id

left join (
select total_id,sum(outnum) as g
from warehouse2

where month(ship_at) <\$b
AND year(ship_at)=2020
group by total_id
) as E on E.total_id=A.id

left join (
select total_id,sum(num) as d
from warehouse2
where month(purchase_at) = \$b
AND year(purchase_at) = 2020
group by total_id
) as B on B.total_id=A.id

left join (
select total_id,sum(outnum) as e
from warehouse2
where month(ship_at) =\$b
AND year(ship_at) = 2020
group by total_id
) as C on C.total_id = A.id
where A.area >= 19
and A.area <= 55
order by A.area,A.id');

\$a=DB::select(\$sqlTmp);

return view('Report/month',compact('a'));
}
``````
1

iT邦大師 1 級 ‧ 2021-10-25 13:00:37

1.你的sprintf根本就是多餘的,

2.如果要在PHP字串中直接帶變數,

p0625 iT邦新手 5 級 ‧ 2021-10-25 13:32:05 檢舉

1

``````\$users = DB::select('select * from users where active = ?', [1]);
``````

``````\$results = DB::select('select * from users where id = :id', ['id' => 1]);
``````

p0625 iT邦新手 5 級 ‧ 2021-10-25 13:33:04 檢舉

p0625 iT邦新手 5 級 ‧ 2021-10-25 21:24:22 檢舉

ORM，是沒有JOIN的處理。因為ORM是使用MODEL的觀念運行的。

p0625 iT邦新手 5 級 ‧ 2021-10-26 08:27:58 檢舉