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'));
}
利用原生sql帶查詢資料，但想帶入前端查詢，數字變數為9，$month接前端查詢值為9，$month帶入，要如何寫呢?
已邀請的邦友 {{ invite_list.length }}/5
建議你可以先 dd($request->all()); 看看前端送過來的月份的變數是什麼
如果是 month 的話
$month = $request->input('month') 就會拿到你要的數值
這我有試過了，但卡在把他帶入原生sql
看你有使用 sprintf
可以參考
https://www.php.net/manual/en/function.sprintf.php
建議你先 echo $sqlTmp 看看 sql 是否有把變數帶入
範例:
$num = 5;
$location = 'tree';
$format = 'There are %d monkeys in the %s';
echo sprintf($format, $num, $location); // There are 5 monkeys in the tree
你需要把 sql 語法中的 9 或者你要替換掉的變數 換成 相對應的參數, 再把對應的 $month 放置 sprintf 第二、第三個變數
感謝，我剛測試是有的喔
我先用這月的帶進去測試，$b會是10，但我這樣變數帶進去會出錯
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.你的sprintf根本就是多餘的,
先找一下語法怎麼用.
2.如果要在PHP字串中直接帶變數,
要用雙引號不能用單引號,
單引號就會照原樣輸出.
雖然並不太建議這樣的用法。最好還是使用方法的應用會比較好。
不過還是教你一下好了
這是變數代入法。有多少個?就用多少個值。基本跟用sprintf相同。
$users = DB::select('select * from users where active = ?', [1]);
如果是同樣變數多重使用。可以用以下的方式
$results = DB::select('select * from users where id = :id', ['id' => 1]);
最後，不免找一下麻煩。
其實DB已經有對應的LEFTJOIN用法。如果真想要用LARAVEL的話。
最好還是學會ORM用法。
要不然也得了解一下DB的相關用法。
最好還是不要只想用 SQL 用法硬塞。這樣LARAVEL會哭的。因為你會塞痛它。
iThome鐵人賽
看影片追技術