Help with a query

[gmc1103]

Hi

 

Can someone help me with a query?

I have a table named (ex order) and the related one named (order_detail)

My main table has the order_id and the user_id and the related table has  the order_id, the product_id etc.

What i need i the get all the products from that order_id, each time i try it gives me several lines

This is what i have

idreserva  idutilizador  data        atividade  
---------  ------------  ----------  -----------
       23           670  2016-05-14      

and the related table

idreserva  idequipamento  idtempoInicio  idtempoFim  idsala  registado   
---------  -------------  -------------  ----------  ------  ------------
       23              1              1           2       6  2016-05-12  
       23              6              1           2       6  2016-05-12  
       23             11              1           2       6  2016-05-12  

And i have this query

SELECT t6.`idreserva`, t5.`sala`, t6.`data`,t3.`inicio`, t4.`fim`, t6.`atividade`, t2.`nome`
FROM `req_reserva_detail` AS t1
INNER JOIN `req_material_equipamento` AS t2 ON (t1.`idequipamento` = t2.`idequipamento`)
INNER JOIN `req_material_tempo` AS t3 ON (t1.`idtempoInicio` = t3.`idtempo`)
INNER JOIN `req_material_tempo` AS t4 ON (t1.`idtempoFim` = t4.`idtempo`)
INNER JOIN `req_material_sala` AS t5 ON (t1.`idsala` = t5.`idsala`)
INNER JOIN `req_reservas` AS t6 ON (t1.`idreserva` = t6.`idreserva`)
INNER JOIN `utilizador` AS t7 ON (t6.`idutilizador` = t7.`idutilizador`) WHERE t6.`idutilizador` = 670 AND t6.`data` >= CURDATE() ORDER BY  DATA ASC

Gives me this 

idreserva  sala     data        inicio    fim       atividade  nome              
---------  -------  ----------  --------  --------  ---------  ------------------
       23  6 - INF  2016-05-14  08:15:00  09:45:00             Leitor / CD/ USB  
       23  6 - INF  2016-05-14  08:15:00  09:45:00             Projetor          
       23  6 - INF  2016-05-14  08:15:00  09:45:00             TV+DVD+VHS   

And i would like to have only one idreserva, sala, data, inicio, fim and all the "nome" like an invoice

 

Any help?

[Psycho]

You start by talking about two tables (ex order and order_detail), but the query you've provided has neither table listed. So, I'm not sure what you are wanting. Looking at the query you provided, it's difficult to understand since the names are not in English.

 

The example results you've provided have the same data in all columns except the last. I presume that the columns with the same data are specific to the "order" whereas the last column is the name of the products in the order. If that is the case, then the results are exactly how you would want them to be returned. You don't set up queries to return data constructed for output. That is the job of the code that processes the data. So, if you wanted to create an invoice from that result set, it might look something like this:

 

 

//Parse all return data into logical array
$reservationsAry = array();
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)
{
    $reservationID = $row['dreserva'];
 
    if(!isset($reservationsAry[$reservationID]))
    {
        $reservationsAry[$reservationID]['room']      = $row['sala'];
        $reservationsAry[$reservationID]['date']      = $row['data'];
        $reservationsAry[$reservationID]['timeStart'] = $row['inicio'];
        $reservationsAry[$reservationID]['timeEnd']   = $row['fim'];
        $reservationsAry[$reservationID]['items'] = array();
    }
    //Add product
    $reservationsAry[$reservationID]['items'][] = $row['nome'];
}
 
//Output the data in appropriate format
foreach($reservationsAry as $reservationID => $reservation)
{
    echo "Reservation ID: {$reservationID}<br>\n";
    echo "Room: {$reservation['room']}<br>\n";
    echo "Date: {$reservation['date']}<br>\n";
    echo "Start Time: {$reservation['timeStart']}<br>\n";
    echo "End Time: {$reservation['timeEnd']}<br>\n";
    echo "Items:<br>\n";
    echo "<ul><li>" . implode("</li>\n<li>", $reservation['items']) . "</li></ul>";
    echo "<br><br><br>\n";
}