How can I pass parameters to the query?

Question

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.

[Click here](https://laravel.com/docs/5.3/queries#unions) to find out your problem use union. — Khetesh kumawat, Oct 26 '16 at 07:33
Check this http://stackoverflow.com/questions/20864872/how-to-bind-parameters-to-a-raw-db-query-in-laravel-thats-used-on-a-model answer about parameters binding. — aleksejjj, Oct 26 '16 at 07:37

score 3 · Accepted Answer · edited May 23 '17 at 10:34

3

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)
    ->get();

NOTE:

With union you won't be able to do paginate out of the box. You will need to create the paginator object by yourself as shown here: Laravel - Union + Paginate at the same time?

edited May 23 '17 at 10:34

Community

1
1

answered Oct 26 '16 at 07:32

Lionel Chan

7,894
5
40
69

Perfect ... just as I said, I need to use `paginate()`, So please replace `get()` with `paginate()`. upvote – stack Oct 26 '16 at 07:38
After some tests, I figured out `->get()` works, but `->paginate()` doesn't work. It throws this error: `SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns ...`. Any suggestion? – stack Oct 26 '16 at 07:57
To use union, the select statement must has extract the same number of columns. Are you sure you are not changing the column length of two builders? – Lionel Chan Oct 26 '16 at 08:02
Yes ... both `news` and `productions` tables have the same schema. – stack Oct 26 '16 at 08:03
Ahhh.. http://stackoverflow.com/questions/25338456/laravel-union-paginate-at-the-same-time – Lionel Chan Oct 26 '16 at 08:03

Your Common Sense · Answer 2 · 2016-10-26T09:20:22.047

2

Your "pure PHP code" won't work either. You have to respect SQL and PDO syntax

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

will do.

The same with Laravel: you have to define a placeholder in the query and then send it as a parameter

$sql = 'SELECT * FROM news WHERE title LIKE :q OR description LIKE :q';
$results = DB::select($sql, ['q' => "%$q"]);

edited Oct 26 '16 at 09:20

answered Oct 26 '16 at 07:39

Your Common Sense

156,878
40
214
345

Guys, this answer is the answer of my question *(how to pass parameters in laravel)*. I've marked another answer as accepted one because that one has explained how to use `union` in Laraval *(what exactly I was looking for)*. anyway, this answer is the exact answer of my question *(regardless my needs)*. – stack Oct 26 '16 at 08:31

How can I pass parameters to the query?

2 Answers2