Print data from a UNION SQL query

Question

Print data from a UNION SQL query

Navigation

#1 by (2 votes)

1

I have the following query to two tables with UNION . The problem is that when trying to print any data from the 2nd query it says undefine X-field , but the one from the first query does print me data.

This is my code:

<?php
  $query=mysqli_query($conexion,

    "SELECT de,para,nombre,apellido,fecha,avatar,post_id FROM comentarios 
       WHERE para='$id'
       UNION ALL
      SELECT de,para,nombre_e,apellido_e,fecha_e,avatar_e,id_post FROM notificaciones
     WHERE para='$id'
     ORDER BY GREATEST(fecha,fecha )DESC");

Then I print data with while :

if ($query) {
   while($row=mysqli_fetch_array($query)){

      echo $row["nombre"];//imrprime nombre de la primera consulta sussccefully
      echo $row["nombre_e"];//me dice undefined index nombre_e
   }
}

I do not understand why or why the error is due.

Thanks @OscarGarcia for understanding my problem and what I try to do the typical bell with a notification system therefore try to show two tables in the same content sorted by date for comment notifications and evaluations I am sure that this will serve as a guide in the future since it is one of the codes that I have had the most problems show two tables together in order of date , returning to the subject your code gave me the following results ..

with the first fragment of code works but I get content in white bone empty and it shows me just as I do not know what is due and with the second code fragment I get the same undefined index X-field Thank you for taking the time to help me. I hope you can continue to help me and clarify these doubts.

mysql php

asked by andy gibbs 05.09.2018 в 10:19

source

1 answer

How can I create a gif from the linux terminal? Problems with Safari and Google Maps API

score 2 · Accepted Answer

A union adds records to a query by keeping the initial name of the fields (and, as an additional requirement, it must have the same number of fields), so the data in the second query "will be fused" with the name of the fields of the first, sharing name as follows:

notificaciones.de         => comentarios.de       => de
notificaciones.nombre_e   => comentarios.nombre   => nombre
notificaciones.apellido_e => comentarios.apellido => apellido
etc...

So you will not notice difference between the records obtained from the table notificaciones and comentarios if you do not use any field for it or assign space in the first query.

Your code would be:

while($row=mysqli_fetch_array($query)){
      echo $row["nombre"];//imrprime nombre de la primera consulta sussccefully
      /* No necesitas esto */
      //echo $row["nombre_e"];//me dice undefined index nombre_e
   }
}

Example reserving space:

SELECT
  de, para, nombre, apellido, fecha, avatar, post_id,
  null de_e, null para_e, null nombre_e, null apellido_e,
  null fecha_e, null avatar_e, null id_post
FROM comentarios 
WHERE para='$id'
UNION ALL
SELECT
  null, null, null, null, null, null, null,
  de, para, nombre_e, apellido_e, fecha_e, avatar_e, id_post
FROM notificaciones
WHERE para='$id'
ORDER BY GREATEST(fecha, fecha_e) DESC

I have assigned static values null to the fields *_e during the first query, which will be filled by the second (and, in turn, the fields not *_e first will be filled with values null ).

I had to invent the alias de_e for the field de of the table notificaciones so that there is no conflict of names with the field de of the table comentarios .

Although the following query would also be valid and simpler:

SELECT
  de, para, nombre, apellido, fecha, avatar, post_id, 'comentarios' tabla
FROM comentarios
WHERE para='$id'
UNION ALL
SELECT
  de, para, nombre_e, apellido_e, fecha_e, avatar_e, id_post, 'notificaciones'
FROM notificaciones
WHERE para='$id'
ORDER BY GREATEST(fecha) DESC

In which I have created a field called tabla that will have the value comentarios or notificaciones depending on the table to which the record belongs.