Raw SQL query with variable as data

I have SQL query with WHERE IN statement where I use variable that I pass by ajax data. The value of variable is fine, I check that under network and also by simply returning it back. So the value of variable is for example "1" or "1,3". It works fine in both cases if I execute query on phpmyadmin and it works when there is only one number like "1 " in Laravel, but when there are two it doesn't work, response is empty data.

    $scores=DB::select("SELECT user.username, points, sport.sportName
                    FROM scores
                    JOIN users AS user ON user.id = scores.user_id
                    JOIN sports AS sport ON sport.id = scores.sport_id
                    WHERE scores.sport_id IN (:sport_id)
                    ORDER BY points DESC",array('sport_id' => "$sport_id"));  
    return json_encode(( ['data' => $scores] ));

Cronix

7 years ago

Level 67

I don't think you can bind an array like that.

If you had 2 items, you'd probably have to do something like

WHERE scores.sport_id IN (?,?) // 2 placeholders
// ...
ORDER BY points DESC",array(1,3));  // 2 values

You could make something will will create the ? placeholders depending on how many values you pass to it, and bind the individual values in the array for each one.

It's best to use eloquent for this kind of thing instead of a raw query. Then you could just do ->whereIn('sport_id', [1,3])

Miko55

7 years ago

Level 1

Well I changed my query to:

$scores=DB::table('scores')->join('users', 'users.id', '=', 'scores.user_id') 
                                       ->join('sports', 'sports.id', '=', 'scores.sport_id')
                                       ->join('leagues', 'leagues.id', '=', 'scores.league_id')
                                       ->whereIn('scores.sport_id',array($sport_id))
                                       ->select('users.username','points','sports.sportName','leagues.leagueName')->get();                  
            return json_encode(( ['data' => $scores] ));

But it's same result. But if I put for example [1,3] instead of variable I get right results. So I think problem is in variable

Cronix

7 years ago

Best Answer

Level 67

do a dd($sport_id);.

It needs to be an array like [1, 2, 3].

If it's a string, like "1,2,3" and you put that in an array like you are array($sport_id), that won't work. If it's a comma separated string, maybe you should use

->whereIn('scores.sport_id', explode(',', $sport_id))

to turn it into an actual array.

Miko55

7 years ago

Level 1

I made it work by using explode(',',$sport_id) instead of array. I was using raw SQL because I wanted to create row numbers, any idea how I could make that work with Eloquent ?

Cronix

7 years ago

Level 67

I wanted to create row numbers

Not sure what you mean by "create row numbers"

Miko55

7 years ago

Level 1

I mean that when I order user by their points there would be another row with numbers from 1 to user count(). So user with most points would have row number 1 etc. In Microsoft SQL there is a function, for MySQL here is a explanation: http://www.mysqltutorial.org/mysql-row_number/

Miko55

7 years ago

Level 1

Okey solved it with ->addIndexColumn()

Please or to participate in this conversation.