<?php
require 'PHPExcel/Classes/PHPExcel.php';

// Connect to the database
$db = mysqli_connect("localhost", "root", "", "shangrilaperfumes");

// Check connection
if (!$db) {
    die("Error: Unable to connect to the database");
}

// Query to fetch data from test_table and vendor table
$query = "SELECT t.Id, t.UPC, t.SKU, t.Product, t.Price, t.Brand, t.Stock, v.VendorName
          FROM test_table t
          JOIN vendor v ON t.VendorId = v.id";

$result = mysqli_query($db, $query);

if (!$result) {
    die("Error: Unable to fetch data");
}

// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setTitle("Exported Data")->setDescription("Data exported from test_table and vendor table");

// Add data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Id')
            ->setCellValue('B1', 'UPC')
            ->setCellValue('C1', 'SKU')
            ->setCellValue('D1', 'Product')
            ->setCellValue('E1', 'Price')
            ->setCellValue('F1', 'Brand')
            ->setCellValue('G1', 'Stock')
            ->setCellValue('H1', 'VendorName');

$rowCount = 2;
while ($row = mysqli_fetch_assoc($result)) {
    $objPHPExcel->getActiveSheet()
                ->setCellValue('A' . $rowCount, $row['Id'])
                ->setCellValue('B' . $rowCount, $row['UPC'])
                ->setCellValue('C' . $rowCount, $row['SKU'])
                ->setCellValue('D' . $rowCount, $row['Product'])
                ->setCellValue('E' . $rowCount, $row['Price'])
                ->setCellValue('F' . $rowCount, $row['Brand'])
                ->setCellValue('G' . $rowCount, $row['Stock'])
                ->setCellValue('H' . $rowCount, $row['VendorName']);
    $rowCount++;
}

// Set headers for download
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="exported_data.xls"');
header('Cache-Control: max-age=0');

// Write file to the browser
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

// Close database connection
mysqli_close($db);
?>
