Looping cell value from database

Topics: Developer Forum, Project Management Forum, User Forum
Oct 31, 2009 at 7:39 AM

Hey there!

I have been looking for a solution to allow users to download search results generated by my database. I wanted to use jnlp to use the clipboard content and then I found PHPExcel. It occured to me that PHPExcel would be a great choice. I am wondering if it is possible to loop value to a spreadsheet in memory from my database one row at a time. I meant to set cell value by php variable in the URL, row by row and when the script is done running in my database I can go ahead to force download... Is this possible? Or is there any other way?

Thank you in advance!

Oct 31, 2009 at 10:12 AM

It's perfectly possible, in exactly the same way you'd loop through a database query result set and echo to the screen. At its simplest, something like:

$row = 1;
$objPHPExcel = new PHPExcel();

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
   $column = 'A';
   foreach($row as $columnData) {
      $cellRef = $column.$row;
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellRef, $columnData);

In practise, you'd need to consider your data types, particularly dates, and format them accordingly.

Oct 31, 2009 at 2:31 PM
Edited Nov 1, 2009 at 5:02 AM

Thank you very much for your response, Mark! It is much appreciated!

I am aware that it can be done if the data are being stored in a mySQL server. The problem is that it is an already made FileMaker database and users are accessing the data through iwp(Instant web publishing page that rendered from filemaker). The only way I can get the data out to php is either by URL variable one record at a time, or combine all data together and copy to the clipboard...

Also, thank you for your concern about field format, I guess I am just going to save the data in csv so it shouldn't be a problem as long as I can get all the data into the php.

Oct 31, 2009 at 5:19 PM

Can you use curl to request each row from the database in turn via the iwp?

Nov 1, 2009 at 5:00 AM

Oh that sounds viable! I will look into it and post an update. Thank you, Mark, for being so helpful and responsive!

Nov 1, 2009 at 8:10 AM

I have been having problem using php within the filemaker web viewer, that is to say I have to use filemaker to call an external php file hosted in the web server which poses the same problem as to how I can transfer all the data to php. Fortunately, I have just learned that Filemaker has released API for php so I guess I will go that way instead of rebuilding the whole front-end with php. Thank you again for all your help, Mark! Have a great weekend!


Nov 6, 2009 at 7:07 AM

Just posting an update. Everything has been doing great and I have got it done. With the FileMaker API for php I could loop data into variables/arrays, perform search, sort or even run a filemaker script in my php script. I am happy with how it turned out.