Jump to content

Help with a query


gmc1103
Go to solution Solved by Psycho,

Recommended Posts

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?

Link to comment
Share on other sites

  • Solution

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";
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.