Multi-table mysql query and repeated data

Question

Multi-table mysql query and repeated data

Navigation

#1 by (0 votes)

2

first of all thank you very much in advance for your help, I am learning php and queries based on data and I have some doubts, I am doing a multitasking query that is something like the following:

SELECT t.Tourname, c.FechaConcierto, can.nombreCancion, b.nombreBanda, bi.nombreBandaInvitada, bi.nombreCantante
FROM Tour AS t
LEFT JOIN Concierto AS c
ON c.ConciertoID=t.TourID
LEFT JOIN Cancion AS can 
ON c.Canciones=can.cancionID
LEFT JOIN Bandas AS b
ON can.cancionID=b.BandaID
LEFT JOIN BandaInvitada AS bi 
ON can.cancionID=bi.bandaInvitadaID
LEFT JOIN Cantante AS ca 
ON bi.Cantante=ca.cantanteID
WHERE t.TourID = 2
ORDER BY c.FechaConcierto ASC;

The first question is whether this query is correct, as I am learning, I do not know if it is wrong, the general problem I have is that this query gives me results, for example:

Sol 12--10-16 Amame Null Los toños Pepe Sierra
Sol 12--10-16 La luz Null Los toños Pepe Sierra
Sol 12--10-16 No se Null Los toños Pepe Sierra
Sol 12--10-16 El pasado Null Los toños Pepe Sierra
Sol 12--10-16 Como fue Banda caravana Null Null
Sol 12--10-17 Amame Null Los toños Pepe Sierra
Sol 12--10-17 La luz Null Los toños Pepe Sierra
Sol 12--10-17 No se Null Los toños Pepe Sierra
Sol 12--10-17 El pasado Null Los toños Pepe Sierra
Sol 12--10-17 Como fue Banda caravana Null Null

As you realize, there is a lot of data that is repeated, I do not know if you can optimize the query so that it does not happen or how to order the array or execute the foreach so that the data is shown, for example, like this:

Sol 
12--10-16
Los toños Pepe Sierra
    Amame (Cancion)
    La luz (Cancion)
    No se (Cancion)
    El pasado (Cancion)
Como fue (Cancion)
    Banda caravana

12--10-17
Los toños Pepe Sierra
    Amame (Cancion)
    La luz (Cancion)
    No se (Cancion)
    El pasado (Cancion)
Como fue (Cancion)
    Banda caravana

Thank you very much for your collaboration

php mysqli foreach

asked by gary 31.01.2018 в 06:51

source

1 answer

Sort Relationships (ManyToMany) without duplicating records What does this WARN mean in the Node terminal?

score 0 · Answer 1

The query is fine, the only thing that I miss is that you do a LEFT JOIN instead of a normal JOIN, to avoid the results with Null , such as:

Sol 12--10-16 Amame Null Los toños Pepe Sierra

To format the output result, you can use the CONCAT function. This function allows you to concatenate a list of strings.

An example using your query:

SELECT CONCAT("Tour": + t.Tourname, char(13), c.FechaConcierto + " - " + b.nombreBanda)

The result would be:

Tour:Sol 
12--10-16 - Banda caravana

MySQL offers many functions to treat texts, here is a list with all the functions

To make a line break, I used CHAR(13) as I read in this Stack OVerflow post in English , but I could not check if it works. In this same post they also say to use CHAR(13) + CHAR(10) , it depends if you work on Windows or not.