Select for years

Scriptcase 8 Discussion
#1

Hi All,

I have a field in mysql defined by YEAR, but I need to fill a select field in form with 10 years more and less of actual year. I know how I can to do a PHP function for this, but, how I can associate the result of this function to select list? or in other way, I can to change the field not like select, if not like Date but that only appear the year?

Thanks and regards,

AYMG

#2

Dear all,

How I can put this array result in a select field?

<?php
$min=5;
$max=5;
$ano = intval(date(‘Y’));
$anos = array();
for ($i=$ano-$min; $i<=$ano+$max; $i++) {
$anos[]=$i;
}
print_r($anos);
?>

Result:
Array
(
[0] => 2011
[1] => 2012
[2] => 2013
[3] => 2014
[4] => 2015
[5] => 2016
[6] => 2017
[7] => 2018
[8] => 2019
[9] => 2020
[10] => 2021
)

I tested put the field like date with year format, but is not working.

Thanks for any help.

AYMG

#3

Hi,

In order to solve my problem, I did this (I hope that help to others):

  1. in onScriptinit event, I did create a temporary table with year lists:
    sc_exec_sql(“CREATE TEMPORARY TABLE AnosList (
    ano_sel YEAR NOT NULL,
    ano_mos YEAR NOT NULL)”);

$sql_insert = "INSERT INTO AnosList VALUES ";
$min = 5;
$max = 5;
$ano = intval(date(‘Y’));
for ($i=$ano-$min; $i<=$ano+$max; $i++) {
$sql_insert.=’(’.$i.’,’.$i.’),’;
}

$sql_insert=trim($sql_insert, ‘,’);
echo $sql_insert;

sc_exec_sql($sql_insert);

  1. change the field to select type, in “Lookup Settings”, put a select for the temporary table:
    select ano_sel, ano_mos from AnosList

The minor issue with this option, is: when the form is saved, appear a error message informing that fields is not exists but run fine.

Other way to create the list (to prevent the minor issue) is create a view in SQL with union selects, something like this:
CREATE
ALGORITHM = UNDEFINED
DEFINER = aymg01@%
SQL SECURITY DEFINER
VIEW Anos AS
SELECT
YEAR((CURDATE() - INTERVAL 1 YEAR)) AS ano_val,
YEAR((CURDATE() - INTERVAL 1 YEAR)) AS ano_mos

UNION SELECT 
    YEAR(CURDATE()) AS `year(curdate())`,
    YEAR(CURDATE()) AS `year(curdate())`

UNION SELECT 
    YEAR((CURDATE() + INTERVAL 1 YEAR)) AS `year(date_add(curdate(), interval 1 year))`,
    YEAR((CURDATE() + INTERVAL 1 YEAR)) AS `year(date_add(curdate(), interval 1 year))`

Regards,

AYMG