Multiple Select Checkboxes, retrieve data from db and write to excel

[smilla]

Hi everyone,

 

This is my first comment on PHPFreaks so be gentle......

 

I am relatively new to PHP, I have an asp.net background but here is my problem...

 

I have a webpage with rows of data from a mysql database.

 

I have added a checkbox by each row (which is identified by $id)

<input type='checkbox' name='row[]' value='".$data['id']."'>

 

The user checks multiple checkboxes for export. this is where I get stuck.

 

I know that I need to create a dump of the selected rows from database where id = $id, put this into another array and then write this array to an excel file.

 

I have spent many an hour researching this, but it seems that there is nothing (that I can find) which helps with the selection of the checkboxes first.

 

I would appreciate any help on this.

 

Regards

Samidoo

 

[jonsjava]

I had a headache with this one myself. My dirty work-around: I gave each checkbox an integer as a name, so I could say:

<?php
foreach($sanitized_post as $key=>$value){
if (is_numeric($key)){
	$checkbox[$key] = $value;
}
}

 

[smilla]

Thanks for your reply, still a little confused though,

 

This is retrieving the data from web page but how do I retrieve the data from the database with this?

 

[jonsjava]

When you set checkboxes, you use the primary key (id number, for example) as the integer to increment the keys.  This way, you can say something like this

foreach ($checkbox as $key=>$val){
   $sql = "DELETE FROM `table` WHERE `id`=$key;";
   mysql_query($sql);
}

[smilla]

Thanks, that figures.

 

I actually want to select more data from the database based on the $id values.

 

What would be the best way to write the data retrieved to excel?

[jonsjava]

Here's the actual code I use when I use this:

foreach($_POST as $key => $value) {
         if ($value != "DELETE"){
        $removal_sql = "DELETE FROM `{$_SESSION['username']}_table_{$list_name}` WHERE id={$value} LIMIT 1;";
                 mysql_query($removal_sql);
                }

So, as you can see, what I do is different, but you could take the ID, and query agains them:

<?php
foreach($sanitized_post as $key=>$value){
$countme = 0;
if (is_numeric($key)){
	if ($countme == 0){
		$q_values = "'$key'";
	}
	else{
		$q_values .= ",'$key'";
	}
}
}
$sql = "SELECT * FROM `tables` WHERE `id` IN ($q_values);";

[saviola]

Thanks, that figures.

 

I actually want to select more data from the database based on the $id values.

 

What would be the best way to write the data retrieved to excel?

 

Maybe something like this :

if($_POST["submitMyForm"]) {
  $checked = implode(", ", $_POST["cb"]);
  echo $query = "SLECT * FROM table WHERE id IN($checked)";

}

 

<form id="myform"  method="POST" action="">

<input type="checkbox" name="cb[]" value="1">
<input type="checkbox" name="cb[]" value="2">
<input type="checkbox" name="cb[]" value="3">
<input type="checkbox" name="cb[]" value="4">
<input type="checkbox" name="cb[]" value="5">
<input type="submit" name="submitMyForm" value="Export">
</form>

[jonsjava]

I should have told him to implode. need to have more caffeine.

[smilla]

ok, thanks guys this bit is fine.

 

does anyone know how I loop through the query to write the data to an excel spreadsheet?

[jonsjava]

I don't write to excel spreadsheets, per se, but I do write to CSV files, which excel...excels at reading.

<?php
//sql goes here
$newcount = 0;
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)){
if ($newcount == 0){
	$out = implode("\",\"",array_keys($row))."\n";
	$newcount++;
}
$out .= implode("\",\"",$row)."\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"my-data.csv\"");
echo $out;
?>

 

[saviola]

Also can try this :

 

function cleanData(&$str) {

  $str = preg_replace("/\t/", "\\t", $str);
  $str = preg_replace("/\r?\n/", "\\n", $str);

  if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';

}
// file name for download
$filename = "website_data_" . date('Ymd') . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");

$flag = false;

// execute the query
$result = mysql_query("SELECT * FROM tableName ORDER BY columnName") or die('Query failed!');

while(false !== ($row = mysql_fetch_assoc($result))) {
  if(!$flag) { // display field/column names as first row
    echo implode("\t", array_keys($row)) . "\n";
    $flag = true;
  }
  array_walk($row, 'cleanData');
  echo implode("\t", array_values($row)) . "\n";

}

[smilla]

Thanks saviola, I tried that bit of code but could not get it to work.

 

ok this is what I have so far:-

 

foreach ($_POST['row'] as $k=> $c)

      {

        $sql = mysql_query("SELECT comments.nominee as Nominee, company.company as Company, comments.department as Department FROM comments INNER JOIN company ON comments.cid=company.id WHERE comments.id = '$c'") or die('Query failed!');

       

$newcount = 0;

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res)){

 

if ($newcount == 0){

 

$out = implode("\",\"",array_keys($row))."\n";

 

$newcount++;}

 

$out .= implode("\",\"",$row)."\n";

}

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=\"my-data.csv\"");

echo $out;

   

}

 

Here is the output:-

 

<br />

<b>Warning</b>:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

<b>Notice</b>:  Undefined variable: out

<b>Warning</b>:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

<b>Notice</b>:  Undefined variable: out

<b>Warning</b>:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in

<b>Notice</b>:  Undefined variable: out

 

This is because I check boxed 3 records?

 

 

[saviola]

No isn't because you check boxed 3 records... what is the result of query ?

Maybe it's empty? Even with one result  function mysql_fetch_assoc($res) working.

I think you don't return result.

Did you see how you can buid and get values of group of check boxes and execut query for more than one rows?

It's in my previous post.

[Psycho]

No need to rebuild the wheel here. Creating a CSV file (you can give it an xls extension) will be much easier than a true Excel file. But, you don't have to create it programatically. PHP has the built in function fputcsv() which will output a record in array format as a properly formatted line. By trying to build your own CSV creation routine you are going to run into problems to overcome if the data has commas or quote marks.

 

http://php.net/manual/en/function.fputcsv.php

[Psycho]

Here is a tested script based upon your previous posts. Note that since the file name is "static" for each day that there could be a problem if two users request an export at the same time. You can avoid this problem by utilizing a random name for the temp file stored on the server but still use the "static" name in the two disposition headers.

 

<?php 

//Sanitize and prepare the input
$recordIDs = array();
foreach ($_POST['row'] as $id)
{
    $recordIDs[] = (int) $id;
}
$idList = implode(',', $recordIDs);

//Query the data
$query = "SELECT cm.nominee, co.company, cm.department
          FROM comments cm
          INNER JOIN company co ON cm.cid=co.id
          WHERE cm.id IN ($idList)";
$result = mysql_query($query);

if(!$result)
{
    echo "Problem running query";
}
else if(mysql_num_rows($result)<1)
{
    echo "There were no records returned";
}
else
{
    //Create the output file (will get rewritten for each user).
    $outputFileName = "Export_".date('Y-m-d').".csv";;
    $outputFileObj = fopen($outputFileName, 'w+');
    //Create header row
    fputcsv($outputFileObj, array('Nominee', 'Company', 'Department'));
    //Ouput records
    while($record = mysql_fetch_assoc($result))
    {
        fputcsv($outputFileObj, $record);
    }
    fclose($outputFileObj);

    //Output file to user
    header("Content-type: application/force-download"); 
    header("Content-Disposition: inline; filename={$outputFileName}"); 
    header("Content-Transfer-Encoding: Binary"); //
    header("Content-length: ".filesize($outputFileName)); 
    header('Content-Type: application/excel'); 
    header("Content-Disposition: attachment; filename={$outputFileName}"); 
    readfile($outputFileName);
    //Delete temp file
    unlink($outputFileName);
}

?>

[smilla]

What can I say, that's perfect.  Thanks for all your help - I just couldn't work out the logic myself.