1

So I know if I build a dynamic SQL string I can do something like the following

$SQL = "SELECT * FROM " . $table;

$first = 1;
foreach($items as $key => $val)
{
   if($first) $SQL .= " WHERE ";
       else $SQL .= " AND ";
   $SQL .= $key . " LIKE " . $VAL;
   $first = 0;
}

and then call DB::Query($SQL);

But this does not automatically protect the user input.

If I used the Query Builder in laravel the user input would automatically be cleaned but I do not know how I can use the Query builder to create a query with a dynamic number of where clauses at runtime. Is this possible?

WildBill
  • 9,143
  • 15
  • 63
  • 87
  • How do I do that where the number of WHERE clauses is defined at runtime and (possibly) unbounded? – WildBill Apr 03 '14 at 23:21
  • How do you know that for building the query string? `$query->where($key, '=', $val);`? PS: why do you use `LIKE` here? – zerkms Apr 03 '14 at 23:22

2 Answers2

2
$query = $tableModel->newQuery();

foreach($items as $key => $val) {
    $query->where($key, "LIKE", '%'.$val.'%');
}
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • So doesn't this execute a different query for EACH element in the array? Or does this ultimately execute only ONE statement with all of the where clauses filtering data at once? If the latter, how do you make that call? I thought you had to use Query builder in the following format "DB::table('mytable')->.....'" – WildBill Apr 03 '14 at 23:30
  • No, it builds up a query, that you then execute using `$query->all()` (or similar).... each use of `$query->where()` simply adds a new where clause to that query (matched AND by default unless you use orWhere()) – Mark Baker Apr 03 '14 at 23:36
  • I don't see where you can do a $query->all() in laravel's documentation http://laravel.com/docs/queries – WildBill Apr 09 '14 at 14:44
  • `public function newQuery($excludeDeleted = true)` is a method defined for `Illuminate\Database\Eloquent\Model`. `public static function all($columns = array('*'))` method is defined in the same model class. All of your own models should extend `Illuminate\Database\Eloquent\Model` - [documentation is here](http://laravel.com/api/class-Illuminate.Database.Eloquent.Model.html) – Mark Baker Apr 09 '14 at 14:54
  • If you were after an answer for an old version of Laravel, then you should have specified that in your question... my answer is valid for Laravel 4+, but I've never used Laravel 3 – Mark Baker Apr 14 '14 at 13:05
  • I'm editing that now. I needed to do this for both 3 and 4. I take it this is not possible in 3... – WildBill Apr 14 '14 at 13:06
  • It may well be possible in Laravel 3, but I've never used Laravel 3 so I can't tell you that answer – Mark Baker Apr 14 '14 at 13:07
1
foreach( $items as $key => $value )
{
    $query->where( $key, '=', $value );
}

Is something like that what you're after?

EDIT: Looks like Mark beat me to it.

Johnathan Barrett
  • 546
  • 2
  • 7
  • 24
  • So doesn't this execute a different query for EACH element in the array? Or does this ultimately execute only ONE statement with all of the where clauses filtering data at once? If the latter, how do you make that call? I thought you had to use Query builder in the following format "DB::table('mytable')->.....'" – WildBill Apr 03 '14 at 23:39