Group HTML form results into a single SELECT FROM query for an .XLS export?

sleepyw

Every time I think I have something figured out, another issue makes itself known.

I am trying to export selected fields to an Excel (.xls) file. I know how to do it and have several "reports" set up based on a static set of fields to pull from.

However, I also need to create a custom report where the user selects which fields they want to export. So I have an HTML form with checkboxes where the user selects what they want. What I want it to do on the submitting PHP page is collate those selected values into a single string, and then use that string in the export query SELECT $collatedstring FROM table_name....

I'm trying to do something like this, which isn't working.

$collatedstring = $_POST['Item_1'] AND $_POST['Item_2'] AND$_POST['Item_3'] AND$_POST['Item_4'];

The idea is that only fields the user selected will show in the export.

Would it also work if i just listed the individual strings in SELECT $Item_1, $Item_2... FROM table_name...? Will it ignore blank values, or will that cause a problem because I have to separate each value with a comma and I can't have a blank space separated by commas there?

ixalmida

This is how I'd do it based on the info you've given:

$keys = array_keys($_POST);
$i = 0;
foreach ($keys as $key)
  {
  // See if the POST name matches a field name (can also use NOT matching, ie: "if ($key != "submit") continue;)
  if(substr($key, 0, 5) == "Item_") $fields[$i] = $_POST[$key];
  $i++;
  }

$field_string = implode(", ", $fields);   // ie: "Item_1, Item_2, Item_3"
$query = "SELECT $field_string FROM table WHERE conditions";

If I've misinterpreted your query issue and you actually need "AND" in there, you can change the implode statement to:

implode(" AND ", $fields);

sleepyw

The code I'm using doesn't seem to like have a $ value after SELECT. It yields nothing, even if it's just a standard field like $item1 that I've already defined above it with $item1=$_POST['item1'].

if(substr($key, 0, 5) == "Item_") $fields[$i] = $_POST[$key];

I also don't know what the 0,5 represents and if that whole line needs to be repeated for each item#.

ixalmida

substr() manual page

I was assuming you were being literal with your POST keys. In other words, I assumed you were checking for a form field called "Item_1" from the referring page. If that is not the case, it may be easier to check for form fields that AREN'T going to be query fields (like "submit") and process everything else.

I forgot to mention that the easier way is just to pass your form fields using an array. Then you don't have to check key names in your processing form - you just process the original array. Here's some info on array passing.

Then on your processing page, you'd only need:

$field_string = implode(", ", $_POST['MyArray']);
$query = "SELECT $field_string FROM table WHERE conditions";

Notice that in this case, you'd need the value of the checkbox on your referring page to contain the field name, not a boolean value (0/1), as in:

<input type="checkbox" name="MyArray[]" value="field_1">

Note that checkboxes that are not checked do not get passed in POST, so if you have an array of checkboxes and the user checks box 1, 3, and 7, your array should look like this:

MyArray[0] => "Field_1"
MyArray[1] => "Field_3"
MyArray[2] => "Field_7"

ixalmida

I should add that the simplified method I suggested isn't really "web safe" as-is. Someone could build a form with their own field names and potentially get information you don't want them to have (or some other SQL injection attack).

You should have some kind of validation that the field names are valid and only in the range you want the user to access. One easy way to do that is to create an array of valid field names then use array_intersect() to return only the valid fields.

sleepyw

I followed your lead, but I'm still running into the same issue.

I added the

name="MyArray[]"

to the html form page inputs.

On the php file that creates the Excel report, I have this:

$Status = $_POST['Status'];
$start_date = $_POST['start_date'];
$end_date = $_POST['end_date'];

$field_string = implode(",", $_POST['MyArray']);

$result = mysql_query('SELECT $field_string FROM table_name WHERE Status="$Status" AND
Revised_Completion_Date >= "$start_date" AND Revised_Completion_Date <= "$end_date"');
$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}

............

It doesn't like the $field_string after SELECT. I get this:

Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /....php on line 47

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /....php on line 53

...and it continues on.

if I put a static value there instead of $field_string, it works.

ixalmida

I'm not sure where my post from yesterday went, but I'll repost:

Let's add some troubleshooting code. At the top of your code, print out the POST array:

print_r($_POST['MyArray']);
exit;

If the array looks okay, then delete the above code, then validate the field names (weed out the bad values) and check the field string for problems:

// Take the union of a known valid array and the POST array...
$possible_fields = array("Field_1", "Field_2", "Field_3");
$scrubbed_array = array_intersect($_POST['MyArray'], $possible_fields);

// Create the field string...
$field_string = implode(", ", $scrubbed_array);

// Output the field string to make sure it is valid...
echo $field_string;
exit;

If the field string looks good, then kill the last two lines above and let's check the entire query string for problems:

$query = "SELECT $field_string FROM table_name WHERE Status='$Status' ".
  "AND Revised_Completion_Date >= '$start_date' ".
  "AND Revised_Completion_Date <= '$end_date'"; 
echo $query;
exit;