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.

Mailing with Joomla: HTML and Text

Joomla has its own built in version of PHPMailer called JMail. Hey, we set all these email configurations in the setup, might as well use those.

$mail =& JFactory::getMailer();
$config =& JFactory::getConfig(); //this is where we grab info from the config file
$mail->useSMTP(
 $config->getValue('config.smtpauth'),
 $config->getValue('config.smtphost'),
 $config->getValue('config.smtpuser'),
 $config->getValue('config.smtppass')
);

Now we can define more stuff for useSMTP, but unless your server settings aren't standard, you don't really need to. But it's all in the config file too, easy way to see it all is just to print_r($config).

Now, I want to send this email as html because it looks nicer, but I have to make sure to set a text only one for those people reading the emails on their phones, etc.

$HTMLmsg = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Message title</title>
</head>
<body>
<p>Here is some text. It looks nice.</p>
</body></html>';
$textmsg = "Here is some text. It looks ok.";
$mail->setBody(&HTMLmsg);
$mail->isHTML(true);
$mail->AltBody=&textmsg;

AltBody is a variable, not a function. It was carried over from PHPMailer. Setting this automatically sets the email to multipart/alternative. It is read by clients that don't have HTML email (like my phone), but clients that can read HTML will see the normal body.

Notice I used double quotes on $textmsg. This is in case I want to use newlines, php will escape them (I mentioned this earlier).

We're almost done. Just need a recipient, sender and subject. We can use another Joomla config value to set who the email is sent from.

$mail->addRecipient('example@example.com');
$mail->setSender($config->getValue('config.mailfrom'));
$mail->setSubject('Test email');

Well this seems good right? I spent a few minutes with this all set up, testing, but no email was sent. Oh yeah, forgot one very important line.

$mail->send();

Resources:

Joomla Framework
Joomla Docs
ProDevTips

Ajax Username Check

I don't know about you, but I love cool ajaxy stuff. A lot of times, it's really simple to add this to a form. All I'm doing is checking to see if the requested username is in use or not.

So first, the html is the simple part. Just give your input box and id and add a section after it to hold the results:

<input type="text" name="uname" id="uname"><span id="checkuname">

Then I make a little php file with the to check for the username.

$u = $_GET['u'];
require_once('db.php');
if($u <> ""){ //we don't want to display anything if the field is blank
  $checkname = query("SELECT * FROM table WHERE username = '".$u."'");
  if(num_rows($checkname) <> 0){
    $desc = 'Already in use';
  }else{
    $desc = 'Ok to use!';
  }
}
echo $desc;

I style $desc to be something nice looking, or use an image of a checkbox or something, depending on how the rest of the form looks.

So then in the scripts, I look in the uname id and display in the checkuname id.

$('#uname').keyup(function(){
  $('checkuname').load('checkuser.php?u='+$(this).val(), function(response, status, xhr){
   if(status == "error"){
    var msg = "There was an error: ";
    $('#checkuname').html(msg + xhr.status + " " + xhr.statusText);
   }
  });
});