PHP Export Two Mysql Tables Data To Excel Or CSV

Question

I have two mysql tables in following format

Table 1 - Institute Basic Details (institute_basic_details)

id  | institute_unique_id  |  institute_name  | institute_city
 1      ABCD1234                NAME-1               AAA
 2      EFGH4567                NAME-2               BBB
 3      IJKL8999                NAME-3               CCC

Table 2 - Institute Owner Details (institute_owners_details)

id  | institute_unique_id  | owner_name  |    owner_email_id  | owner_mobile
 1       ABCD1234             OWNER-A-1      [email protected]   98811xxxxx
 2       ABCD1234             OWNER-B-2      [email protected]   87454XXXXX
 3       ABCD1234             OWNER-C-3      [email protected]   54785XXXXX
 4       EFGH4567             OWNER-D-1      [email protected]   47874XXXXX
 5       EFGH4567             OWNER-E-2      [email protected]   45455XXXXX
 6       IJKL8999             OWNER-F-1      [email protected]   78478XXXXX
 7       IJKL8999             OWNER-G-2      [email protected]   87478XXXXX

Table 2 Contains Un-Even numbers of owners per Institute...

I want to export combined data from both table in excel / csv in following format...

CELLs In A Row - for example

LIKE : In A Row - Institute Name, Institute City, Owner 1 Name , Owner 1 Email, Owner 1 Mobile, Owner 2 Name (if Exists), Owner 2 Email (if Exists), Owner 2 Mobile (if Exists),.... And So on for owner 3, owner 4, owner 5 ......

Currently I am using following Code to export data to csv from One Table only....

<?php
 include("db.php");
 
 $query = "SELECT * FROM institute_basic_details ORDER BY id ASC"; 
 $numrows = $database->num_rows($query); 
  if($numrows > 0){ 
     $delimiter = ","; 
  
     $filename = "INSTITUTE-LIST-" . date('d-F-Y-H-i-s') . ".csv";
     $count = 1; 

     $f = fopen('php://memory', 'w'); 
   
     $fields = array("SERIAL NUMBER", "INSTITUTE NAME", "INSTITUTE CITY");

     fputcsv($f, $fields, $delimiter); 

     $result = $database->get_results($query);
         foreach($result as $row){ 
            $lineData = array($count, $row['institute_name'], $row['institute_city']);
         }

     fseek($f, 0); 
     
     header('Content-Type: text/csv'); 
     header('Content-Disposition: attachment; filename="' . $filename . '";'); 
 

     fpassthru($f); 

   }
  ?>

@ErgestBasha It's not that simple because a dynamic pivot of the owner rows is needed. — Barmar
– Barmar, Commented Dec 12, 2024 at 15:56
For each row you read from institute_basic_details you can do a query for the corresponding owners. Then you can loop over those results, using array_merge() to add them to the output array. — Barmar
– Barmar, Commented Dec 12, 2024 at 15:58
Or you can do it all in a single query that joins the two tables. Start a new output row whenever the institute changes. — Barmar
– Barmar, Commented Dec 12, 2024 at 15:58
@Barmar - "It's not that simple because a dynamic pivot of the owner rows is needed." - Yes , you are right... I am stuck there.... — Dr M L M J
– Dr M L M J, Commented Dec 12, 2024 at 16:24
@Barmar - "Or you can do it all in a single query that joins the two tables. Start a new output row whenever the institute changes." This is making me positive.... Can you provide some sample code for that along with Row Headings (e.g. Owner 1 Name, Owner 1 Email, Owner 1 Mobile, Owner 2 Name, Owner 2 Email, Owner 2 Mobile... and so on..) — Dr M L M J
– Dr M L M J, Commented Dec 12, 2024 at 16:25

lemon8de · Accepted Answer · 2024-12-13 00:57:54Z

0

Following Barmar's suggestion we can do a query for each institution name and city.

$sql = "SELECT * FROM institute_basic_details ORDER BY id ASC";

get the basic details stored in an array because we are going to array merge everything together.

then loop on its details and query again:

while ($data = $stmt -> fetch(PDO::FETCH_ASSOC)) {
    $sql = "SELECT * FROM institute_owner_details where institute_unique_id = ?";

$data['institute_unique_id'] pass this as the variable

while inside that while loop we could start stitching the data you want, by yet another while loop.

$second_query -> execute();
$while ($owner_details = $second_query -> fetch(PDO::FETCH_ASSOC)) {
    $names[] = $owner_details['names'];
    $email[] = $owner_details['email'];
    $phone[] = $owner_details['phone'];
}

Getting those information as an array will help us loop over them and start building an actual row of data.

$array = [];
for ($i = 0; $i < count($names); $i++) {
    $array = array_merge($array,[$containers[$i], $dates[$i]]);
}

Code above starts piling these owner details on top of each other, essentially building up the final look of one row. $array would contain owner information.

And hopefully you saved the basic details at the start because we are merging it with this $array to complete your csv / excel row

array_merge($basic_details, $array); // one complete row

answered Dec 13, 2024 at 0:57

lemon8de

1173 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dr M L M J Dec 13, 2024 at 10:06

ok..thnx will try with your above code and let u know...

Collectives™ on Stack Overflow

PHP Export Two Mysql Tables Data To Excel Or CSV

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related