Solved

create csv file from recordset  in php

Posted on 2016-10-14
4
49 Views
Last Modified: 2016-10-15
I have a table named packings with two fields.
 boxno Item
records are like this
 boxno Item
1 X0016SFS03
1 X0017Y11U7
1 X000YF7GWH
2 X0018C6JU5
2 X0018C5D9D
2 X0018C5D9D
2 X0018C9YQB
2 X0018C5D9D
2 X0018C5D9D
3 X0018C51I1
3 X000WIVZPF
3 X0018AQ2B3
3 X0018C5D9D
3 X0018C5D9D
3 X0018C5D9D
3 X0018C5D9D

The boxno can be any int value - not limited to 3
Now I required to get output file in csv/text format using fputcsv like this
Box no in header and items below that as records
box 1,box2,box3,box4,box5
X0016SFS03,X0018C6JU5,X0018C51I1
X0017Y11U7,X000YF7GWH,

I want help in php to do so.

Thanks and regards,
Rajesh.
0
Comment
Question by:Rajesh Joshi
  • 2
4 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 41844265
Is the table a SQL database table?
0
 
LVL 55

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 41844444
Assume  MySQL

<?php
// GET THE DB HANDLE
$mysqli = new mysqli('localhost','user','password','database');

// GET DATA FROM DB
$query = "SELECT * FROM packings ORDER BY boxno";
$result =  $mysqli->query($query);

// SOMEWHERE TO COLLECT THE RESULTS
$boxes = array();
// LOOP THROUGH RESULTS
while($row = $result->fetch_object()) {
  // HAVE WE SEEN THIS BEFORE
  // IF NOT CREATE A SPACE FOR IT
  if (!isset($boxes[$row->boxno])) {
    $boxes[$row->boxno] = array();
  }
  // ADD THE item TO THE RIGHT BOX
  $boxes[$row->boxno][] = $row->item;
}
// OPEN csv FILE
$file = fopen('t1715.csv','wt');

// GET THE KEYS OF THE BOX ARRAY
// TO BUILD OUR HEADER
$header = array_keys($boxes);

// PREFIX HEADERS WITH `box`
foreach($header as &$h) {
  $h = "box {$h}";
}
// WRITE HEADER
fputcsv($file, $header);

// WE ARE NOT DONE YET
$moredata = true;
while($moredata) {
  // START A NEW ROW
  $current = array();
  
  // ASSUME THIS IS THE LAST ROW
  $moredata = false;
  foreach($boxes as &$box) {
    // BOX NOT EMPTY SO
    // WE ARE NOT DONE
    // SHIFT THE VALUE OFF AND ADD IT 
    // TO THE CURRENT ROW
    if (!empty($box)) {
      $moredata=true;
      $current[] = array_shift($box);
    }
    // BOX IS EMPTY SO ADD A SPACE
    else {
      $current[] = '';
    }
  }
  // DON'T OUTPUT THE BLANK ROW AT THE END
  if ($moredata) {
    fputcsv($file, $current);
  }
}
// CLEAN UP
fclose($file);

Open in new window

1
 
LVL 1

Author Closing Comment

by:Rajesh Joshi
ID: 41844704
Excellent. Thank you Dear Mr.Julian Hansen
0
 
LVL 55

Expert Comment

by:Julian Hansen
ID: 41844725
You are most welcome.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question