iT邦幫忙

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'));
}

利用原生sql帶查詢資料,但想帶入前端查詢,數字變數為9,$month接前端查詢值為9,$month帶入,要如何寫呢?

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

建議你可以先 dd($request->all()); 看看前端送過來的月份的變數是什麼

如果是 month 的話

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

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

這我有試過了,但卡在把他帶入原生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 第二、第三個變數

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

感謝,我剛測試是有的喔

我先用這月的帶進去測試,$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
小魚
iT邦大師 1 級 ‧ 2021-10-25 13:00:37

1.你的sprintf根本就是多餘的,
先找一下語法怎麼用.

2.如果要在PHP字串中直接帶變數,
要用雙引號不能用單引號,
單引號就會照原樣輸出.

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

好的,感謝我在試試

1

雖然並不太建議這樣的用法。最好還是使用方法的應用會比較好。
不過還是教你一下好了

這是變數代入法。有多少個?就用多少個值。基本跟用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會哭的。因為你會塞痛它。

看更多先前的回應...收起先前的回應...
p0625 iT邦新手 5 級 ‧ 2021-10-25 13:33:04 檢舉

感謝,我轉成ORM試試

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

不好意可以請教一下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這裡要怎麼改orm嗎

ORM,是沒有JOIN的處理。因為ORM是使用MODEL的觀念運行的。
不會有所謂的JOIN應用。
如果還不熟ORM。就先記好,「ORM不是DB物件」
如果你ORM還是當用DB運行的話,你會學不會的。

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

好的感謝/images/emoticon/emoticon08.gif

我要發表回答

立即登入回答