Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 394
prajakta Avatar asked Jun 24 '16 07:06

prajakta


People also ask

What is the syntax of REGEXP in the where clause?

The following illustrates the syntax of the REGEXP operator in the WHERE clause: This statement performs a pattern match of a string_column against a pattern. If a value in the string_column matches the pattern, the expression in the WHERE clause returns true, otherwise it returns false.

How do I use regular expressions in SQL?

The database provides a set of SQL functions that allow you to search and manipulate strings using regular expressions. You can use these functions on any datatype that holds character data such as CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2. A regular expression must be enclosed or wrapped between single quotes.

What does regexp_replace do in SQL?

REGEXP_REPLACE This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern you specify. See the Oracle Database SQL Referencefor syntax details on the REGEXP_REPLACE function.

How to join multiple where conditions in Laravel using or operator?

Laravel multiple where conditions - [AND]: By default, where conditions are chaining with AND operator. To join via OR operator, you will have to use orWhere which we will talk next. 1) Simple Syntax:


1 Answers

There is alternative way:

DB::table('shows')->where('show_date', 'REGEXP',  Carbon\Carbon::now()->toDateString())->get();
like image 169
haofly Avatar answered Sep 26 '22 17:09

haofly