CODEWITHSUNDEEP
phpmysql
Boky
Boky

Reputation: 12064

Getting results from a database

I'm working with phpexcel but I have problem by getting results from a 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 as shopNaam, 
                                 GROUP_CONCAT(producten.naam SEPARATOR '\r') as prodNaam, GROUP_CONCAT(producten.id SEPARATOR '\r') as prodID,
                                 order_details.aantal as kolicina, order_details.leeggoed as leeggoed, order_details.id as ordDetId, SUM(order_details.aantal) as prodCount, 
                                 korisnici.ime as korNaam, korisnici.email as korMail, korisnici.id as korId, korisnici.prezime as korPrez, korisnici.mobitelBroj as broj, korisnici.mjesto as adres, korisnici.huisnummer as hn,
                                 korisnici.gemeente as gemeente, 
                                 orders.besteld_op as tijd 
                                 FROM 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 
                                 WHERE orders.status = 1 GROUP BY korisnici.id");

$rowcounter = 2;

while ($row = mysqli_fetch_assoc($rezOrders)){


    $prod_id = $row['prodID'];

   $rez = mysqli_query(
                $kon, 
                "
                SELECT producten.naam as prodNaam 
                  FROM 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
                 WHERE producten.id IN (" . str_replace("\r", ", ", $prod_id) . ")
                "
            );
/*echo "Product i 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 a while loop I get all id's of a products but because I use GROUP_CONCAT in my query (I use it because I need such a display in my excel file) I do not get them separately, but grouped. And now I can't get the quantity of those product.

My tables in a database are as follows :

Order_details table (there is the quantity(aantal) which I 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 my product_shop_tt table which is connected with order_details table and 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 at the end my 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 I do not fetch anything because my $prod_id is grouped. How can I get all id's of the products separately, but my $rezOrders query must give the same result?

Result which I get in my excel file is as follows(this is result that I want to 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 to do is to write next to each product his quantity which is located in order_details table.

When I change in my first query GROUB BY order_details.id then I can get quantity values from order_details for each product separately, but now i do not get the right display in my excel file. I get for each product a new row but I want to get it like on the first photo. Now I get it as fallows enter image description here

Upvotes: 2

Views: 156

Answers (1)

Matijs
Matijs

Reputation: 2553

Your prodId is a string containing "id1\rid2\rid3". If replace all "\r"'s wit ", " and use the SQL IN () statement instead of =.

Replace:

WHERE producten.id = " . $prod_id . "

with:

WHERE producten.id IN (" . str_replace("\r", ", ", $prod_id) . ")

Upvotes: 1

Related Questions