php - Getting results from a database -

i'm working phpexcel have problem getting results database.

i have next code :

    require_once 'phpexcel/classes/phpexcel.php'; include "phpexcel/classes/phpexcel/writer/excel2007.php";  session_start(); include("config.php"); global $kon; ob_start(); $excel = new phpexcel; $excel->getproperties()->setcreator('boris jelic'); $excel->getproperties()->setlastmodifiedby('boris jelic'); $excel->getproperties()->settitle('orders'); $excel->removesheetbyindex(0);   $cols = array('tijd' => 'a', 'shop' => 'b', 'products' => 'c', 'naam' => 'd', 'adres' => 'e', 'gemeente' => 'f', 'telefoonnummer' => 'g', 'email' => 'h', 'leeggoed' => 'i'); $list = $excel->createsheet(); $list->settitle('users'); $list->getcolumndimension('a')->setwidth(20); $list->getcolumndimension('b')->setwidth(25); $list->getcolumndimension('c')->setwidth(40); $list->getcolumndimension('d')->setwidth(40); $list->getcolumndimension('e')->setwidth(40); $list->getcolumndimension('f')->setwidth(20); $list->getcolumndimension('g')->setwidth(15); $list->getcolumndimension('h')->setwidth(40); $list->getcolumndimension('i')->setwidth(40); $list->setcellvalue('a1', 'tijd'); $list->setcellvalue('b1', 'shop'); $list->setcellvalue('c1', 'products'); $list->setcellvalue('d1', 'naam en voornaam'); $list->setcellvalue('e1', 'adres'); $list->setcellvalue('f1', 'gemeente'); $list->setcellvalue('g1', 'telefoonnummer'); $list->setcellvalue('h1', 'email'); $list->setcellvalue('i1', 'leeggoed');   //za background $excel->getactivesheet()->getstyle('a1:i1')->getfill() ->setfilltype(phpexcel_style_fill::fill_solid) ->getstartcolor()->setargb('ffe8e5e5');  //stavljamo naslove bold $excel->getactivesheet()->getstyle('a1:i1')->getfont()->setbold(true);  //povecavamo velicinu slova $excel->getactivesheet()->getstyle('a1:i1')->getfont()->setsize(13);   //moramo prvo uzeti sve orders sa statusom 1 $rezorders = mysqli_query($kon, "select                                   shops.naam shopnaam,                                   group_concat(producten.naam separator '\r') prodnaam, group_concat(producten.id separator '\r') prodid,                                  order_details.aantal kolicina, order_details.leeggoed leeggoed, order_details.id orddetid, sum(order_details.aantal) prodcount,                                   korisnici.ime kornaam, korisnici.email kormail, korisnici.id korid, korisnici.prezime korprez, korisnici.mobitelbroj broj, korisnici.mjesto adres, korisnici.huisnummer hn,                                  korisnici.gemeente gemeente,                                   orders.besteld_op tijd                                   orders                                  inner join korisnici on korisnici.id = orders.user_id                                   inner join order_details on order_details.order_id = orders.id                                  inner join product_shop_tt on order_details.product_shop_tt_id = product_shop_tt.id                                  inner join producten on producten.id = product_shop_tt.product_id                                  inner join shops on shops.id = product_shop_tt.shop_id                                   orders.status = 1 group korisnici.id");  $rowcounter = 2;  while ($row = mysqli_fetch_assoc($rezorders)){       $prod_id = $row['prodid'];     $rez = mysqli_query(                 $kon,                  "                 select producten.naam prodnaam                    producten                  inner join product_shop_tt on product_shop_tt.product_id = producten.id                  inner join order_details on order_details.product_shop_tt_id = product_shop_tt.id                  producten.id in (" . str_replace("\r", ", ", $prod_id) . ")                 "             ); /*echo "product kolicina : " . $redaan["productnaam"] . " - " . $redaan["kolicina"] . "<br />";*/   $tijd = $row["tijd"];   $ime = $row["kornaam"] . " " . $row["korprez"];   $email = $row["kormail"];   $telnummer = $row["broj"];   $gemeente = $row["gemeente"];   $adresa = $row["adres"] . " " . $row["hn"];   $leegoed = $row["leeggoed"];    $list->setcellvalue('a'.$rowcounter, $tijd);   $list->setcellvalue('b'.$rowcounter, $row['shopnaam']);      while($red = mysqli_fetch_assoc($rez)){         echo $red["prodnaam"] . "<br />";          $list->setcellvalue('c'.$rowcounter, $red["prodnaam"]."\r");         $list->getstyle('c'.$rowcounter)->getalignment()->setwraptext(true);    }     $list->setcellvalue('d'.$rowcounter, $ime);   $list->setcellvalue('e'.$rowcounter, $adresa);   $list->setcellvalue('f'.$rowcounter, $gemeente);   $list->setcellvalue('g'.$rowcounter, $telnummer);   $list->setcellvalue('h'.$rowcounter, $email);   $list->setcellvalue('i'.$rowcounter, $leegoed);   $rowcounter++; }   $writer = new phpexcel_writer_excel2007($excel); $writer->save('files/users.xlsx'); 

in $prod_id in while loop id's of products because use group_concat in query (i use because need such display in excel file) not them separately, grouped. , can't quantity of product.

my tables in database follows :

order_details table (there quantity(aantal) need)

+---+----------+--------------------+----------+--------+ |id | order_id | product_shop_tt_id | leeggoed | aantal | +---+----------+--------------------+----------+--------+ |180|       87 |                51  |          |    2   | |181|       87 |                52  |          |    3   | |182|       88 |                32  |          |    2   | |183|       88 |                35  |          |    5   | |184|       88 |                55  |          |    3   | +---+----------+--------------------+----------+--------+ 

then product_shop_tt table connected order_details table , products table :

+--+------------+---------+-------+ |id| product_id | shop_id | price | +--+------------+---------+-------+ |32|          1 |      20 |  25.8 | |33|          5 |      20 |  13.6 | |34|          4 |      18 |       | |35|         26 |      20 |       | |36|         27 |      18 |       | +--+------------+---------+-------+ 

and @ end products table :

+--+-------------------------+-------------------------+-----------------+---------+--------+ |id| naam                    | details                 | keywords        | type_id | active | +--+-------------------------+-------------------------+-----------------+---------+--------+ | 1| test promjena           | ovdje idu detalji       | ovo su keywords |       2 |      1 | | 4| test                    | ovo je opis proizvoda:) | rijec. rijec1   |       2 |      1 | | 5| ime_proizvoda           | detalji                 | keywords        |       2 |      1 | |26| neko meso recimo (50gr) |                         |                 |       2 |      0 | +--+-------------------------+-------------------------+-----------------+---------+--------+ 

in $red not fetch because $prod_id grouped. how can id's of products separately, $rezorders query must give same result?

result in excel file follows(this result want get) :

+---------------------+-------------------------+-------------------------+------------------+-------------------+----------+----------------+ | tijd                | shop                    | products                | naam en voornaam | adres             | gemeente | telefoonnummer | +---------------------+-------------------------+-------------------------+------------------+-------------------+----------+----------------+ | 2015-08-15 14:11:56 | bakkerìj van hecke      | test promjena           | borís jelic      | krijgsgraat 130   | gent     |      486386069 | |                     | neko meso recimo (50gr) |                         |                  |                   |          |                | |                     | buffon                  |                         |                  |                   |          |                | +---------------------+-------------------------+-------------------------+------------------+-------------------+----------+----------------+   | 2015-08-15 14:11:14 | lidl                    | prod1                   | mesanovic maida  | krihuisstraat 130 | brussel  |      486742185 | |                     |                         | prod2                   |                  |                   |          |                | +---------------------+-------------------------+-------------------------+------------------+-------------------+----------+----------------+   

what i'm trying write next each product quantity located in order_details table.

when change in first query groub order_details.id can quantity values order_details each product separately, not right display in excel file. each product new row want on first photo. fallows

your prodid string containing "id1\rid2\rid3". if replace "\r"'s wit ", " , use sql in () statement instead of =.

replace:

where producten.id = " . $prod_id . " 

with:

where producten.id in (" . str_replace("\r", ", ", $prod_id) . ")