Number results of a query in Laravel and MySQL

Question

Number results of a query in Laravel and MySQL

Navigation

#1 by (1 votes)

0

I'm starting in Laravel and I need your help please. I want to number a column after making a query to the BD. If someone can help me, it would be great:

My driver is as follows:

$participantes = DB::table('participante')
        ->select('idparticipante','nombres','estado')
        ->where('estado', '=', 'Participante')
        ->orderBy('idparticipante', 'desc')
        ->paginate(15);

I found that with the following code I can list in MYSQL but it does not work out, example:

'SELECT @rownum:=@rownum+1 AS rownum, frutas.* FROM (SELECT @rownum:=0) r, frutas;'

I put it in my controller as follows:

        $participantes = DB::table('participante as p', '(select @rownum=1) as r')
        ->select(DB::raw('(@rownum:=@rownum+1) AS posicion'), 'idparticipante','nombres','estado')
        ->where('estado', '=', 'Participante')
        ->orderBy('idparticipante', 'desc')
        ->paginate(15);

laravel-5 controller

asked by Anddy Cabrera Garcia 19.12.2016 в 18:58

source

1 answer

Does anyone know how to pass data from a class to a td tag to form a table? Update only the field that has been sent using PHP and MySQL

score 1 · Accepted Answer

I think the problem is the way you initialize the @rownum variable.

Try the following code that initializes the variable in a separate query:

// Inicializa @rownum
   DB::statement(DB::raw('SET @rownum = 0')); 

// Realiza la consulta
   $participantes = DB::table('participante')
       ->select(DB::raw('idparticipante','nombres','estado', '@rownum := @rownum + 1 as rownum'))
       ->where('estado', '=', 'Participante')
       ->orderBy('idparticipante', 'desc')
       ->paginate(15);