Question 58173 in Laravel Question posted in
The official Laravel Documentation can be found here.

Substituting variable into raw SQL in Laravel

I have a Laravel controller and here is a piece of my code for the SQL query that I made:

        $query = <<<SQL
        SELECT day, date, product1_shift1, product1_shift2, product1_shift3, 
        COALESCE(product1_shift1, 0) + COALESCE(product1_shift2, 0) + COALESCE(product1_shift3, 0) AS total_product1_qty
        FROM (
            SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date,
                (SELECT SUM(qty) AS product1_shift1
                FROM bill_details 
                JOIN bills ON bills.bill_id=bill_details.bill_id
                WHERE bills.shift_id='48c73e3d-57de-4fea-aae4-7222d7e6afed'
                AND bills.date=bills_master.date
                AND bills_master.customer_id=bills.customer_id
                AND bill_details.product_id='3516c43b-ffe3-4e90-9d93-924a9865c9cd') AS product1_shift1,
                (SELECT SUM(qty) AS product1_shift2
                FROM bill_details 
                JOIN bills ON bills.bill_id=bill_details.bill_id
                WHERE bills.shift_id='463ec995-cfad-46ff-8e65-3115e6e651c5'
                AND bills.date=bills_master.date
                AND bills_master.customer_id=bills.customer_id
                AND bill_details.product_id='3516c43b-ffe3-4e90-9d93-924a9865c9cd') AS product1_shift2,
                (SELECT SUM(qty) AS product1_shift3
                FROM bill_details 
                JOIN bills ON bills.bill_id=bill_details.bill_id
                WHERE bills.shift_id='0a792c5f-84a6-4534-818c-427cfe8cd3ca'
                AND bills.date=bills_master.date
                AND bills_master.customer_id=bills.customer_id
                AND bill_details.product_id='3516c43b-ffe3-4e90-9d93-924a9865c9cd') AS product1_shift3
            FROM bills bills_master
            WHERE bills_master.date BETWEEN '2023-04-07' AND '2023-04-09'
                AND bills_master.customer_id='d9916741-ca61-4a57-b281-15c80fad833c'
            GROUP BY bills_master.date
        ) subquery_alias
        SQL;
        dd(DB::select($query));

What should I do if I want to substitute a PHP variable into, for example, WHERE bills.shift_id='$phpVariable'?

Tags:

2

Answers


  1. 0

    You should use Named Bindings to prevent SQL injection, such as:

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

    Reference https://laravel.com/docs/10.x/database#using-named-bindings

    Login or Signup to reply.
  2. 0

    You can try using string concatenation operator ‘.’ to concatenate the variable value with the query string. For example:

    $shift_id = '48c73e3d-57de-4fea-aae4-7222d7e6afed';
    $query = <<<SQL
            SELECT day, date, product1_shift1, product1_shift2, product1_shift3, 
            COALESCE(product1_shift1, 0) + COALESCE(product1_shift2, 0) + COALESCE(product1_shift3, 0) AS total_product1_qty
            FROM (
                SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date,
                    (SELECT SUM(qty) AS product1_shift1
                    FROM bill_details 
                    JOIN bills ON bills.bill_id=bill_details.bill_id
                    WHERE bills.shift_id='$shift_id'
                    AND bills.date=bills_master.date
                    AND bills_master.customer_id=bills.customer_id
                    AND bill_details.product_id='3516c43b-ffe3-4e90-9d93-924a9865c9cd') AS product1_shift1,
                    (SELECT SUM(qty) AS product1_shift2
                    FROM bill_details 
                    JOIN bills ON bills.bill_id=bill_details.bill_id
                    WHERE bills.shift_id='$shift_id'
                    AND bills.date=bills_master.date
                    AND bills_master.customer_id=bills.customer_id
                    AND bill_details.product_id='3516c43b-ffe3-4e90-9d93-924a9865c9cd') AS product1_shift2,
                    (SELECT SUM(qty) AS product1_shift3
                    FROM bill_details 
                    JOIN bills ON bills.bill_id=bill_details.bill_id
                    WHERE bills.shift_id='$shift_id'
                    AND bills.date=bills_master.date
                    AND bills_master.customer_id=bills.customer_id
                    AND bill_details.product_id='3516c43b-ffe3-4e90-9d93-924a9865c9cd') AS product1_shift3
                FROM bills bills_master
                WHERE bills_master.date BETWEEN '2023-04-07' AND '2023-04-09'
                    AND bills_master.customer_id='d9916741-ca61-4a57-b281-15c80fad833c'
                GROUP BY bills_master.date
            ) subquery_alias
            SQL;
    dd(DB::select($query));

    So the $shift_id is concatenated to the query string using the operator ‘.’.
    See if I could help you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search