Moving

I'm moving this blog over to Posterous. Mainly because of their Markdown support.

Warning: Not Enough Brain Space

Embarking on a new endeavor. Learning Spring MVC.

I learned how to make Models, Views AND Controllers! They don't do much... yet, but I'm getting there.

This is very, very different from PHP.

Export Query to CSV

It's been requested that I generate reports from the data stored in the database. They want to open them in Excel, so I decided to generate CSVs. Php5 added a nice function, fputcsv, that will format an array as a CSV and write it to the file.

$file = fopen('php://temp/maxmemory:'. (5*1024*1024), 'r+');
while($data = mysql_fetch_assoc($query)){
fputcsv($file, $data);
}

rewind($file);
$export = stream_get_contents($file);
fclose($file);
header('Content-type: application/csv');
header('Content-Disposition: atachment; filename="report.csv"');
echo $export;

This works great in most cases, but I was having a problem if the field contained a comma and a double quote. But wait, this function says it escapes double quotes, what's going wrong? What was going wrong is that the data had already been escaped by mysql (\") so wasn't being escaped correctly by fputcsv ("").

Since what I pull out of the database and what go into fputcsv are both arrays, I didn't want to split the array up, remove the slashes then put it back together again by hand. Instead, I found a function that will do it!

function unstrip_array($array){
foreach($array as &$val){
if(is_array($val)){
 $val = unstrip_array($val);
}else{
 $val = stripslashes($val);
}
}
return $array;
}

So with this, the fputcsv line becomes:

fputcsv($file, unstrip_array($data));

It should be noted that this function calls itself on the child array to an infinite depth, so this could cause memory issues on arrays with an excessively large depth. In this case, we only have a single level array, so we should be fine.

Resources:

fputcsv

Aaha Creative

Timestamp Field not Auto Update

I have a registration table with a timestamp field that I want to use the current timestamp when someone registers. If you just create a timestamp field in mysql, it acts as though you did:

`regdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

This is fine and dandy for some applications, but any time you update the record, it will update the timestamp. I did not want this. So, you can just create the table with:

`regdate` timestamp NOT NULL default CURRENT_TIMESTAMP

But, if like me, you have to alter the table, it' simple enough to do with:

ALTER TABLE registration CHANGE regdate regdate timestamp default CURRENT_TIMESTAMP;

Resources:

Timestamp

Alter Table

Physical Address same as Mailing

This project has a need to know your physical location, but might need to mail you something too. Sometimes these are not the same, so we make input boxes for both. In most cases, they are the same, so we create a check box that says "Same as physical address." When they check it, the second set of address inputs are automatically filled and disabled.

So we start with a simple form.

<strong>Physical Address</strong>
<label for="pstr">Street *</label><input type="text" name="pstr" id="pstr" />
<label for="pstr2">Street 2</label><input type="text" name="pstr2" id="pstr2" />
<label for="pzip">Zip *</label><input type="text" name="pzip" id="pzip" />
<strong>Mailing Address</strong>
<label for="same">Same as Physical Address</label><input type="checkbox" name="same" id="same" value="same" />
<label for="mstr">Street *</label><input type="text" name="mstr" id="mstr" />
<label for="mstr2">Steet 2</label><input type="text" name="mstr2" id="mstr2" />
<label for="mzip">Zip *</label><input type="text" name="mzip" id="mzip" />

The jQuery has to not only populate and disable the fields when the box is checked, it has to make sure the validation passes. Then if someone changes their mind and unchecks the box, it has to un disable the boxes and clear them out.

$(document).ready(function(){
  $('input:checkbox[name=same]').change(function(){
    if($(this).is(':checked')){
      $('#mstr').val($('#pstr').val());
      $('#mstr2').val($('#pstr2').val());
      $('#mzip').val($('#pzip').val());
      $('#mstr').attr('disabled', 'disabled');
      $('#mstr2').attr('disabled', 'disabled');
      $('#mzip').attr('disabled', 'disabled');   
      $('#mstr').removeClass('error');
      $('#mstr2').removeClass('error');
      $('#mzip').removeClass('error');
    }else{
      $('#mstr').removeAttr('disabled');
      $('#mstr2').removeAttr('disabled');
      $('#mzip').removeAttr('disabled');
      $('#mstr').removeAttr('value');
      $('#mstr2').removeAttr('value');
      $('#mzip').removeAttr('value');
    }
  });
});

And with some formatting, it looks like this.