stack stack - 1 month ago 7
MySQL Question

How can I pass parameters to the query?

I use Laravel. As you know, Laravel doesn't support

UNION
clause for the query. So I have to write it as
raw
when I want to paging the whole results. Something like this:

$results = DB::select('SELECT id, title, description, imgPath
FROM news n
WHERE n.title LIKE %$q OR n.description LIKE %$q
UNION ALL
SELECT id, title, description, imgPath
FROM productions p
WHERE p.title LIKE %$q OR p.description LIKE %$q
');


As I said, I use Laravel, So how can I pass
$q
to the query in Laravel? All I'm trying to do is making the query safe against SQL injections. That's why I'm trying to pass the parameters to the query rather that using them directly in the query.




In pure PHP I can do that like this:

$st = $dbh->prepare('SELECT ... WHRER col LIKE %:q');
$st->bindParam(':q', $q, PDO::PARAM_INT);


I want something like this ^ in Laravel.

Answer

Yes, there is union: https://laravel.com/docs/5.3/queries#unions

I didn't test it out, but it should looks something like this:

$first = DB::table('news')
    ->select(['id', 'title', 'description', 'imgPath'])
    ->where(function($query) use ($q) {
        $query->where('title', 'like', "%$q")
              ->orWhere('description', 'like', "%$q");
    });

$result = DB::table('productions')
    ->select(['id', 'title', 'description', 'imgPath'])
    ->where(function($query) use ($q) {
        $query->where('title', 'like', "%$q")
              ->orWhere('description', 'like', "%$q");
    })
    ->unionAll($first)
    ->paginate();