Extracting date, phone, zip and currency after validation

[ljungers]

After doing some testing with setting a patern and using the ( if (preg_match($pattern, $field) I now have a good idea of editing/validating date, phone, zip and currency test boxes from user input frond on a form.

I now realise that I need to know how to properly extract that data into a variable that I can use in a MySQL table INSERT.

What is the best way when all of these fields are text strings, and can the length of the extraction be controlled. The bigest concern for me is the currency field then followed by the rest.

Thanks in advance for all advice.

[ronverdonk]

When needed, you can store all data in VARCHAR fields. Such as:

• a telephone no is never > 15 chars, so define that as VARCHAR(15),

• A ZIP code VARCHAR(10),

• A currency can be stored as a VARCHAR(15) (all currency symbol, comma's, decimals points included) or as a DECIMAL(15,2) but then you have to strip all comma's and currency symbol before storing it into the db. On the other hand: you can calculate directly with a decimal type field.

• A DATE defined MySQL field has standard format YYYY-MM-DD. If you want to store your MMDDYY(YY) in a standard type DATE field, you must convert before inserting it. You can also store that MMDDYY(YY) in a VARCHAR(8) type field.
  Same as for currency, when you store in in the standard MySQL DATE format it is easier to use calculations on it.

In general: you can store it all in VARCHAR fields, but when you want to perform arithmetic on it without need to convert first, you should stick to the DATE and DEC formats.

Ronald :cool:

[ljungers]

Thanks Ronald for the very good summary of what and how to store in MySQL. I found what you said very useful.

The phone & zip I will leave as a varchar filed, and if the edit/validation is good then I'll store them.

After giving some thought the currency I wish to store as a DECIMAL(15,2) what is the best way to strip all comma's and currency symbol before I store it.

The date I would like to store it in the MySQL format YYYY-MM-DD. I wish to allow either format MMDDYY(YY) to be entered. What is the cleanest way to convert that format into the MySQL standard date format for storing.

Like you said, going this way allows me to calculate and directly perform arithmetic on it without the need to convert.

[ronverdonk]

[php]<?php
// currency
$tests = array(" $ 42.52 ",
"$ 4232395",
"4112412",
"239583.52",
"$ 3223.23",
"$123,234.53",
"$123,235",
"$ 123,234,325.23");
$pattern = '/^\s*[$]?\s*((\d+)|(\d{1,3}(\,\d{3})+))(\.\d{2})?\s*$/';
foreach ($tests as $test) {
// US currency
if (preg_match_all($pattern, $test, $m)) {
$curr=str_replace(array(',', ' ', '$'), array('', '', ''), $test );
}
else {
echo "Failed match on $test<br />";
}
}

// MMDDYY : how do you determine the century??
// MMDDYYY to YYYY-MM-DD
$date='10262005';
$dat=sprintf('%4s-%2s-%2s', substr($date,4),substr($date,2),substr($date,0));
echo $dat;
?>[/php]
Ronald :cool:

[ljungers]

Thanks for the code. How would I test for a 2 digit year vers a 4 digit year.
I figure that I'll let the user enter the date in either format MMDDYY or MMDDYYYY

[ronverdonk]

In that case you have to make an assumption like: any year 40 and higher is 19xx otherwise it is 20xx.
(the following is based on this assumption and on the fact that you already have validated the date):
[php]
<?php
// MMDDYY : convert to MMDDYYY
if (strlen($date) == 6) {
if (substr($date,4,2) < '40')
$cent = '20';
else
$cent = '19';
$date = substr($date,0,4).$cent.substr($date,4,2);
}
// MMDDYYY to YYYY-MM-DD (MySQL DATE() format)
$dat=sprintf('%4s-%2s-%2s', substr($date,4,4),substr($date,2,2),substr($date,0 ,2));
// $dat is the var you insert into your table field with DATE format
?>
[/php]
Ronald :cool:

[ljungers]

Thank you Ronald, and again you come through with a good solution.