Level 73
Why not just pass the collection on to the next function?
Oct 25, 2020
9
Level 1
How to get each column of my DB :: Select query
Hello everyone, I need to get each data from each column of my query from the database. In this query I will obtain more than one row and then I will have to generate all the information obtained in a string to deliver to another function
It's my code
$querycon="SELECT c.nombre AS acuerdo_nombre,
c.fecha_inicio,
c.fecha_fin,
ce.numero_mantenciones AS mantenciones_contratadas,
cl.nombre AS cliente,
ce.cliente_final,
mar.nombre AS marca,
m.modelo AS modelo,
e.serie,
st.nombre AS tipo_servicio,
CONCAT(sc.year_programado,'-',sc.month_programado) AS servicio_contratado,
s.fecha_programada,
IF (s.pendiente=1,'SI','NO') AS pendiente,
IF (t1.mantenciones_realizadas IS NULL,0,t1.mantenciones_realizadas) AS mantenciones_realizadas
FROM contrato c
JOIN contrato_equipo ce ON (c.id=ce.contrato_id AND ce.deleted_at IS NULL)
JOIN equipo e ON (e.id=ce.equipo_id AND ce.deleted_at IS NULL)
JOIN modelo m ON (e.modelo_id=m.id)
JOIN marca mar ON (m.marca_id=mar.id)
JOIN servicio_contratado sc ON (sc.contrato_equipo_id=ce.id)
LEFT JOIN servicio s ON (s.contrato_equipo_id=ce.id AND s.servicio_contratado_id=sc.id )
LEFT JOIN servicio_tipo st ON (s.servicio_tipo_id=st.id AND st.nombre='PR')
LEFT JOIN users asig ON (asig.id=s.user_id)
JOIN cliente cl ON (cl.id=c.cliente_id)
LEFT JOIN (
SELECT
ce.id,
count(*) as mantenciones_realizadas
FROM contrato_equipo ce
JOIN equipo e on (e.id=ce.equipo_id)
JOIN servicio s on (s.contrato_equipo_id=ce.id)
JOIN servicio_tipo st on (s.servicio_tipo_id=st.id)
WHERE st.nombre='PR' and s.fecha_realizado!='0000-00-00'
GROUP BY ce.id) as t1 ON (t1.id=ce.id)
WHERE (c.estado_ebp2='APROBADO' OR c.estado_ebp2='') AND (s.fecha_realizado='0000-00-00' OR (s.fecha_realizado IS NULL AND s.id IS NULL))";
$queryif=" SELECT
c.nombre AS acuerdo_nombre,
c.fecha_inicio,
c.fecha_fin,
ce.numero_mantenciones AS mantenciones_contratadas,
cl.nombre AS cliente,
ce.cliente_final,
mar.nombre AS marca,
m.modelo AS modelo,
e.serie,
st.nombre AS tipo_servicio,
'' AS servicio_contratado,
s.fecha_programada,
IF (s.pendiente=1,'SI','NO') AS pendiente,
IF (t1.mantenciones_realizadas IS NULL,0,t1.mantenciones_realizadas) AS mantenciones_realizadas
FROM contrato c
JOIN contrato_equipo ce on (c.id=ce.contrato_id AND ce.deleted_at IS NULL)
JOIN equipo e on (e.id=ce.equipo_id AND ce.deleted_at IS NULL)
JOIN modelo m on (e.modelo_id=m.id)
JOIN marca mar on (m.marca_id=mar.id)
JOIN servicio s on (s.contrato_equipo_id=ce.id AND s.servicio_contratado_id IS NULL AND s.fecha_realizado='0000-00-00')
LEFT JOIN servicio_tipo st on (s.servicio_tipo_id=st.id AND st.nombre='PR')
LEFT JOIN users asig on (asig.id=s.user_id)
JOIN cliente cl on (cl.id=c.cliente_id)
LEFT JOIN (
SELECT
ce.id,
count(*) as mantenciones_realizadas
FROM contrato_equipo ce
JOIN equipo e on (e.id=ce.equipo_id)
JOIN servicio s on (s.contrato_equipo_id=ce.id)
JOIN servicio_tipo st on (s.servicio_tipo_id=st.id)
WHERE st.nombre='PR' and s.fecha_realizado!='0000-00-00'
GROUP BY ce.id) as t1 ON (t1.id=ce.id)
WHERE (c.estado_ebp2='APROBADO' OR c.estado_ebp2='')";
$and = " AND c.id=$id";
$union=" UNION ALL";
$querycon=$querycon.$and;
$queryif=$queryif.$and;
$query=$querycon.$union.$queryif;
$results=db::select($query);
It's a result of my query:
acuerdo_nombre,fecha_inicio,fecha_fin,mantenciones_contratadas,cliente,cliente_final,marca,modelo,serie,tipo_servicio,servicio_contratado,pendiente,mantenciones_realizadas
acuerdo1,2020-10-25 00:02:03,2021-10-25 00:02:03,5,AS Maquinarias,BLueprient,KODAK,SE730,08759562,RM,AP,2,3
acuerdo1,2020-10-25 00:02:03,2021-10-25 00:02:03,5,AS Maquinarias,Saturday,KODAK,i390,08759563,RM,AP,1,4
$cliente=$results->cliente->toArray();
$cliente_final=$results->cliente_final->toArray();
$marca=$results->marca->toArray();
Or should I do a while loop to get each field in order to get the required result?
while($result in $results){
$cliente=$result['cliente'];
$cliente_final=$result['cliente_final'];
$marca=$results['marca'];
$clientes[]=$cliente;
$cliente_finals[]=$cliente_final;
$marcas[]=$marca;
}
Level 1
The next function send key and value into a rest api
Level 1
I only need to obtain those fields ... I don't know if it is possible in fact it was my query if it was possible to obtain each field of each column or the fields of an entire column
Level 73
Not sure what you mean by field and column,
To get a column out of the database you do something like this,
SELECT title
FROM books
That will give you a list of titles
- The Eye Of The World
- The Great Hunt
- The Dragon Reborn
To get all columns from a table you can either specify them one by one or use the asterix wild card
SELECT title, released, pages. blub
FROM books
Is the same as
SELECT *
FROM books
With two tables joined you can do this
SELECT b.*,
g.*
FROM books b,
genres g
WHERE b.genre_id = g.id
Or you can use the more modern approach
SELECT b.*,
g.*
FROM books b
JOIN genres g ON b.genre_id = g.id;
Level 1
I understand what you mean but really what I want to achieve is to obtain from $ results some field of my database, so I asked if it was possible
Level 73
Still don't know what you mean, so I can't help you there.
Level 56
So this returns an array of stdClass instances
$results = DB::select($query);
And from what I understood you want arrays of only the cliente, cliente_final and marca columns.
You could combine array_map and data_get methods:
$cliente = array_map(function ($record) {
return data_get($record, 'cliente');
}, $results);
$cliente_final = array_map(function ($record) {
return data_get($record, 'cliente_final');
}, $results);
$marca = array_map(function ($record) {
return data_get($record, 'marca');
}, $results);
If you are using PHP 7.4 you can simplify the code above with short closures:
$cliente = array_map(fn ($record) => data_get($record, 'cliente'), $results);
$cliente_final = array_map(fn ($record) => data_get($record, 'cliente_final'), $results);
$marca = array_map(fn ($record) => data_get($record, 'marca'), $results);
Or, you can convert the results into a Collection and use the collection's pluck method:
$results = collect($results);
$cliente = $results->pluck('cliente')->toArray();
$cliente_final = $results->pluck('cliente_final')->toArray();
$marca = $results->pluck('marca')->toArray();
More info on Collections' methods: https://laravel.com/docs/8.x/collections
Level 1
Thanks @rodrigo.pedra
1 like
Level 56
You're welcome. Have a nice day =)
Please or to participate in this conversation.