How to use regular expression in the WHERE clause of query in Laravel?

Question

I have a table named "Shows". There is a column "show_date". I want to retrieve the shows whose show_date is todays date.

Following is my query

  $s = DB::table('shows')->get();
  $a = DB::table('shows')->select('show_date')->get();
  foreach ($s as $key => $value) 
 {
    $date_test = date('Y-m-d');
    $s_test = DB::table('shows')->where('show_date',preg_grep('/"'.$value->show_date.'"./',         $a->show_date))->get();
    echo "<pre>"; var_dump($s_test);
   if(explode(" ",$value->show_date)[0] == date('Y-m-d'))
  {
    $shows1 = DB::table('shows')->where('id',$value->id)->get();
    $s1 = DB::table('transactions')
        ->select(DB::raw("GROUP_CONCAT(selected_seats SEPARATOR '') as selected_seats"),'userid','amount','show_id')
        ->where("show_id","=",$value->id)  
        ->groupBy('userid')
        ->groupBy('amount')
        ->orderBy('userid','ASC')
        ->orderBy('amount', 'DESC')
        ->get();

        if($s1 != null)
       {

        echo $value->id;
        $c = count($s1); 

        $sub_count1 = 0; $next_id = ""; $total_array = 0;
       for($i=0;$i<$c;$i++)
      {

        $first_character = $s1[$i]->selected_seats;

        $sub_count = substr_count($s1[$i]->selected_seats, ',');

        $sub_count1 = $sub_count1 + $sub_count;//to get the total no. of seats



       for($j=0,$k=0;$j<$sub_count;$j++,$k++)
       {
        // split the string with comma.
        $s = explode(',',$first_character);



       // get total no. of seat names listed in one row in table.eg A 1,B 2. Then $sub_count would be 2


        $p = $s[$j][0];

       }

    }

  // get seats for each show from transaction table.

  $demo = DB::table('theater_setting')->select('row_seats_selling_price','row')->where('show_id',$value->id)->get();
   foreach ($demo as $key => $val) {
    $categoryArr[$val->row]=$val->row_seats_selling_price;
  }
 $demo4 = DB::table('theater_setting')->select('row_seats_selling_price','row')->where('show_id',$value->id)->get();

 $demo3 = DB::table('transactions')->where('show_id',$value->id)->select('selected_seats','userid')->get();

  for($p=0;$p<count($demo3);$p++)
  { 
    $arr = explode(',', substr($demo3[$p]->selected_seats,0,-1)); 
    $trans[] = $demo3[$p]->userid;

    foreach ($arr as $k => $v) 
    { 
      $seats[$demo3[$p]->userid][]=$v;
    }

  }

  foreach ($seats as $user_id=>$v)
  {  

    for ($h=0; $h < count($v); $h++) 
    { 

      $e = explode(" ", $v[$h]);

      $p = $e[0];
      $demo_array[$p][$user_id][] = $v[$h];          

    }
    $users = DB::table('users')->where('id',$user_id)->get();          

  }   

  return view('Backend.NewReportByShowsCategory2')->with([
        's1'=>$s1,'shows1'=>$shows1,'demo'=>$demo,'categoryArr'=>$categoryArr,'demo3'=>$demo3,'demo4'=>$demo4,'demo_array'=>$demo_array]);
  }
  else
  {
   return view('Backend.NewReportByShowsCategory2')->with([
        's1'=>$s1]);
  }

}

}

I am getting the following error:

Object of class stdClass could not be converted to string

What is $value->show_date? My guess is that it is a Carbon object so you need to get the date as string from it — rypskar
– rypskar, Commented Jun 24, 2016 at 7:19
@rypskar, yes it is the object through which i am accessing the column. — prajakta
– prajakta, Commented Jun 24, 2016 at 7:20
Have you tested $value->show_date->toDateString() instead of your regexp? Or you can try something like ->where('show_date', Carbon::today())->get(); — rypskar
– rypskar, Commented Jun 24, 2016 at 7:28

haofly · Accepted Answer · 2017-10-13 05:55:56Z

13

There is alternative way:

DB::table('shows')->where('show_date', 'REGEXP',  Carbon\Carbon::now()->toDateString())->get();

answered Oct 13, 2017 at 5:55

haofly

91313 silver badges15 bronze badges

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

Comments

Rahul Govind · Accepted Answer · 2016-06-24 07:46:03Z

10

You could convert show_date to a DATE and compare it with the current date -

$s_test = DB::table('shows')->whereRaw('DATE(show_date)=CURRENT_DATE')->get()

However, here's the regex query for selecting rows with a particular date (the current date in this case),

DB::table('shows')->whereRaw("show_date REGEXP '". Carbon\Carbon::now()->toDateString() . "'")->get()

edited Jun 24, 2016 at 7:46

answered Jun 24, 2016 at 7:30

Rahul Govind

5895 silver badges9 bronze badges

1 Comment

Vedmant Over a year ago

Don't need to use raw, just where('show_date', 'REGEXP', 'somedate)

Collectives™ on Stack Overflow

How to use regular expression in the WHERE clause of query in Laravel?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related