Announcement

Collapse
No announcement yet.

Select for years

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • aymg01
    started a topic Select for years

    Select for years

    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

  • aymg01
    replied
    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);

    2. 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

    Leave a comment:


  • aymg01
    replied
    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

    Leave a comment:

Working...
X