What is wrong with this SQL syntax in Laravel?

Question

This query uses $hotel_id, and two date variables to get some data from database. But I keep receiving this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

This is a piece of bigger query used in another project, and works perfectly there. But has some problems in my project that I can't figure out.

The $from_Date and $to_Date variables are alright.

What is wrong with this MySQL statement?

    $splitDate = explode('/', $start);
    $from_Date = jalali_to_gregorian($splitDate[0], $splitDate[1], $splitDate[2], 1);
    $splitDate = explode('/', $end);
    $to_Date = jalali_to_gregorian($splitDate[0], $splitDate[1], $splitDate[2], 1);         

    $query = DB::statement('SELECT DISTINCT t.type_id pmrtypeid ,(CASE WHEN COUNT(t.type_id)-qb.cntrttypid IS Null THEN COUNT(t.type_id) ELSE COUNT(t.type_id)-qb.cntrttypid END)cnttypeid FROM type t JOIN room r
        on r.type_id=t.type_id
        LEFT JOIN (SELECT DISTINCT rt.type_id rttypid,COUNT(rt.type_id)cntrttypid FROM reserve_type rt
        JOIN reserve re
        ON re.reserve_id=rt.reserve_id
        JOIN type ty
        ON ty.type_id=rt.type_id
        WHERE ((? BETWEEN re.from_date AND re.to_date) or (? BETWEEN re.from_date AND re.to_date)) AND ty.hotel_id=?
        GROUP BY rt.type_id)qb
        ON qb.rttypid=t.type_id
        JOIN reserve_span rsn
        ON rsn.hotel_id=t.hotel_id
        WHERE r.hotel_id=? AND ((? BETWEEN rsn.from_date AND rsn.to_date) and (? BETWEEN rsn.from_date AND rsn.to_date)) and rsn.is_enable=1
        GROUP BY t.type_id)pmr
        on pmr.pmrtypeid=tps.type_id
        join type_feture tf
        on tps.type_id=tf.type_id' , [$from_Date,$to_Date,$hotel_id,$hotel_id,$from_Date,$to_Date] );
}

Look at the position at which the statement failed. Usually it would be something like `You have an error in your SQL syntax near "" in ...`. From that point onward you should find the exact issue. — Tobias F., Aug 16 '18 at 08:14
[try here to get errors to display](https://stackoverflow.com/questions/33679996/how-do-i-catch-a-query-exception-in-laravel-to-see-if-it-fails) — IsThisJavascript, Aug 16 '18 at 08:19
print the sql query and run in phpmyadmin and then send a screenshot — Habib, Aug 16 '18 at 08:20

Erubiel · Answer 1 · 2018-08-28T11:31:43.883

1

Why are you using DB::statement() instead of DB::select()?

Also, DB::statement() does not take the binding values array parameter.

https://laracasts.com/discuss/channels/laravel/l5-bindings-is-not-working-with-dbstatement

edited Aug 28 '18 at 11:31

answered Aug 16 '18 at 10:35

Erubiel

2,934
14
32

score 1 · Answer 2 · answered Aug 16 '18 at 10:48

The problem came out to be an extra piece of the other code in mine:

        )pmr
        on pmr.pmrtypeid=tps.type_id
        join type_feture tf
        on tps.type_id=tf.type_id

The useless parentheses created an error. I also changed DB::statement() into DB::select() as suggested by @Erubiel.

What is wrong with this SQL syntax in Laravel?

2 Answers2