gmc1103 Posted May 13, 2016 Share Posted May 13, 2016 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? Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted May 13, 2016 Solution Share Posted May 13, 2016 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"; } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.