Convert SQL Query into Laravel query

Question

I am trying to check the given input is already existed between the c_start and c_end through query, but it is not working. Here is my controller.. for example if person 1 booked a room from 10 to 12 then person can't book the same room at same date from 11 to 12 or any time between the existing booked time.

/* namespace App\booking; */

namespace App\Http\Controllers;

use App\booking;
use Illuminate\Http\Request;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Auth;
use DB;
use App\Quotation;
use DateTime;

class RoombookController extends Controller {

    public function insert_check(Request $request) {

        $roomname = $request->input('roomname');
        $book = $request->input('bookdate');

        $dbstime = explode(':', $request->input('starttime'));
        $dbetime = explode(':', $request->input('endtime'));

        $dbstime[0] = $dbstime[0] * 60;
        $dbetime[0] = $dbetime[0] * 60;

        $dbsum = array_sum($dbstime);
        $dbesum = array_sum($dbetime);

        $users = DB::table("bookings")
                 ->select("id")
                 ->where('bookdate', '=', $request->input('bookdate'))
                 ->where('roomname', '=', $request->input('roomname'))
                 ->where(function ($query){
                           $query->where('c_start', '<', $dbsum);
                           $query->orWhere('c_end', '>', $dbsum);
                         })
                 ->orWhere(function($query){
                             $query->where('c_start', '<', $dbesum);
                             $query->orWhere('c_end', '>', $dbesum);
                           });
        if ($users->count() == 0) {
            $booking = new booking;
            $booking->bookdate = $request->input('bookdate');
            $booking->roomname = $request->input('roomname');
            $booking->starttime = $startTime = $request->input('starttime');
            $booking->endtime = $finishTime = $request->input('endtime');
            $booking->purpose = $request->input('Purpose');
            $booking->booked_by = Auth::user()->id;



            $smstime = explode(':', $startTime);
            $emstime = explode(':', $finishTime);


            $smstime[0] = $smstime[0] * 60;
            $emstime[0] = $emstime[0] * 60;

            $startsum = array_sum($smstime);
            $endsum = array_sum($emstime);


            $booking_time = $startTime . ' to ' . $finishTime;

            $booking->c_start = $startsum;
            $booking->c_end = $endsum;
            $booking->booking_time = $booking_time;

            $time = ($etime - $stime) / 60;
            $booking->total_duration = $time;

            $booking->save();

            return view("bookmessage");
        } else {
            return view("duplicateValue");
        }
    }

}

Raghav Patel · Accepted Answer · 2016-12-27 10:54:20Z

1

Change query to:

$users = DB::table("bookings") 
            ->select("id") 
            ->where('bookdate', '=', $request->get('bookdate')) 
            ->where('roomname', '=', $request->get('roomname'))
            ->where(function ($query) use ($dbsum, $dbesum){
                $query->where(function ($query) use ($dbsum){
                    $query->where('c_start','<',$dbsum);
                    $query->Where('c_end','>',$dbsum);
                 })
                 ->orWhere(function($query) use($dbesum){
                    $query->where('c_start','<',$dbesum);
                    $query->orWhere('c_end','>',$dbesum);
                }); 
            });

edited Dec 27, 2016 at 10:54

answered Dec 27, 2016 at 10:30

Raghav Patel

8537 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

incorporeal Over a year ago

Parse error: syntax error, unexpected '}' this is showing..but i can't see any error like this here

Raghav Patel Over a year ago

my bad, i missed ; after }). please check again.

incorporeal Over a year ago

always showing my room is booked...no matter what i give input at start time and endtime

Rahul · Accepted Answer · 2016-12-27 10:56:38Z

1

Try this code and let me know if it works:

   $whereCondition = array('bookdate'=>$request->input('bookdate'),'roomname'=>$request->input('roomname'));
$users = DB::table("bookings") 
               ->select("id") 
               ->where($whereCondition) 
               ->where(function ($query use $dbsum){
                   $query->where('c_start','<',$dbsum);
                   $query->orWhere('c_end','>',$dbsum);
               })
               ->orWhere(function($query use $dbesum){
                  $query->where('c_start','<',$dbesum);
                  $query->orWhere('c_end','>',$dbesum);
               })
               ->get();

edited Dec 27, 2016 at 10:56

answered Dec 27, 2016 at 10:29

Rahul

2,4802 gold badges12 silver badges17 bronze badges

19 Comments

incorporeal Over a year ago

Undefined variable: dbsum i don't know why this error is showing

Rahul Over a year ago

was your query running fine because I have made few edits only. Please post what value you are getting as $dbsum

incorporeal Over a year ago

if i give input my start time as 10 then dbsum will be 10*60=600 and by dd() i get that value before the query but in query it is not finding

Rahul Over a year ago

were you getting any errors before you changed the query and try both the variants

Rahul Over a year ago

is there any other variable with same name in any other function on the same controller, also check for the line number in which the error is generated.

|

Nazmul Hasan · Accepted Answer · 2016-12-27 11:09:40Z

0

You can run your sql query directly in laravel query builder .You can try this:

$sql = "SELECT * FROM `bookings` WHERE `bookdate`='2016-12-27' AND `roomname`='3' AND (($dbsum>`c_start` AND $dbsum<`c_end`) OR ($dbesum>`c_start` AND $dbesum<`c_end`))";

$result = DB::select(DB::raw($sql))

$result variable contains your output. You must be use your DB class

use DB;

answered Dec 27, 2016 at 11:09

Nazmul Hasan

1,99716 silver badges21 bronze badges

Comments

incorporeal · Accepted Answer · 2016-12-28 11:25:55Z

0

this is the query from which i got my correct result

 $whereCondition = array('bookdate'=>$request->input('bookdate'),'roomname'=>$request->input('roomname'));
    $users = DB::table("bookings") 
           ->select("id") 
           ->where('bookdate', '=', $request->get('bookdate')) 
           ->where('roomname', '=', $request->get('roomname'))
           ->whereRaw(DB::raw("((c_start<$dbsum and c_end>$dbsum) or (c_start<$dbesum and c_end>$dbesum ))"))
           ->get();

answered Dec 28, 2016 at 11:25

incorporeal

1771 gold badge4 silver badges15 bronze badges

Collectives™ on Stack Overflow

Convert SQL Query into Laravel query

4 Answers 4

3 Comments

19 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

19 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related