Solved

create csv file from recordset  in php

Posted on 2016-10-14
4
30 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 108

Expert Comment

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

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 51

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PHP Startup Error 12 30
php image upload 3 25
Wordpress update causing pages to crash 1 18
delete database record with modal 21 29
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now