1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Amateur Hour: Forms, php plugin, accessing and modifying dates

Discussion in 'Community' started by SoilentRed, Dec 5, 2018.

  1. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    Hello Friends! Long time no speaky.

    I have an issue similar to what we worked on ad nauseam here: https://fabrikar.com/forums/index.p...separate-list-based-on-radio-selection.47433/

    Like in the previous post, I'm trying to access a date and modify it in a loop. Basically, insert a row for each year and modify the date by one year in each. My current task calls for much less, than the work we did in the aforementioned thread, but even using our previous work as a "template" my form is still failing.

    I'm using php form plugin with onAfterProcess

    Here is my code:
    Code (Text):

    $myDb = JFactory::getDbo();
    $myQuery = $myDb->getQuery(true);

    $anniversaryDate =  $formModel->getElementData('afab_10_yearly_royalties___anniversary_date');
    $anniversaryDate = new DateTime($anniversaryDate);

    // start building the query ..
    $myQuery->insert('afab_10_yearly_royalties');
    $myQuery->columns(
       array(
          $myDb->quoteName('date_time'),
          $myDb->quoteName('cb'),
          $myDb->quoteName('cbname'),
          $myDb->quoteName('cbusername'),
          $myDb->quoteName('cbemail'),
          $myDb->quoteName('cbid'),
          $myDb->quoteName('group'),
          $myDb->quoteName('bridge_id'),
          $myDb->quoteName('group_name'),
          $myDb->quoteName('js_group_id'),
          $myDb->quoteName('joomla_group_id'),
          $myDb->quoteName('contract_dade'),
          $myDb->quoteName('anniversary_date'),
          $myDb->quoteName('total_minimum')
       )
    );

    // loop around and insert the relevant number of times
    for ($step = 1; $step <= 10; $step += 1) {
       $myQuery->values(
          implode(',',
            array(
            $myDb->quote('{afab_10_yearly_royalties___date_time}'),
                $myDb->quote('{afab_10_yearly_royalties___cb}'),
                $myDb->quote('{afab_10_yearly_royalties___cbname}'),
                $myDb->quote('{afab_10_yearly_royalties___cbusername}'),
                $myDb->quote('{afab_10_yearly_royalties___cbemail}'),
                $myDb->quote('{afab_10_yearly_royalties___cbid}'),
                $myDb->quote('{afab_10_yearly_royalties___group}'),
                $myDb->quote('{afab_10_yearly_royalties___bridge_id}'),
                $myDb->quote('{afab_10_yearly_royalties___group_name}'),
                $myDb->quote('{afab_10_yearly_royalties___js_group_id}'),
                $myDb->quote('{afab_10_yearly_royalties___joomla_group_id}'),
                $myDb->quote('{afab_10_yearly_royalties___contract_dade}'),
                $moddedanniversaryDate,
                $myDb->quote('{afab_10_yearly_royalties___total_minimum}')
            )
          )
       );
        //add a year
    $moddedanniversaryDate = $anniversaryDate->modify('+1 year')->format('Y-m-d H:i:s');
    }

    // run the query
    $myDb->setQuery($myQuery);
    // uncomment this line if you need to debug
    // var_dump((string) $moddedanniversaryDate);exit;
    $myDb->execute();
     
    This is the error I'm getting:

    Code (Text):

    1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''),('2018-12-05 12:05:53','','','','','','','','','','','2018-12-14 12:05:00',2' at line 3
     
    When I do a vardump on this variable it returns this:

    Code (Text):

    string(19) "2028-12-28 12:00:00"
     
    I've been axing at this since Friday, trying a whole lot of different stuff, but this is the closest I've gotten. I hope I'm not too far off. Any help would be valued and appreciated. I hope everyone is well with your team.

    Cheers!
     
  2. achartier

    achartier Active Member

    Level: Community
    Try to echo (string)$myQuery; exit; and see what the sql looks like. Something didn't get set correctly.
     
    SoilentRed likes this.
  3. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    achartier, thanks for chiming in. Here's what that gives me.
    Code (Text):

    string(1190) " INSERT INTO afab_10_yearly_royalties (`date_time`,`cb`,`cbname`,`cbusername`,`cbemail`,`cbid`,`group`,`bridge_id`,`group_name`,`js_group_id`,`joomla_group_id`,`contract_dade`,`anniversary_date`,`total_minimum`) VALUES ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2019-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2020-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2021-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2022-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2023-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2024-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2025-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2026-12-28 10:07:00,''),('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2027-12-28 10:07:00,'')"
     
     
  4. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    I cleaned that up a bit to make it more readable

    Code (Text):

    string(1190) "
    INSERT INTO afab_10_yearly_royalties (`date_time`,`cb`,`cbname`,`cbusername`,`cbemail`,`cbid`,`group`,`bridge_id`,`group_name`,`js_group_id`,`joomla_group_id`,`contract_dade`,`anniversary_date`,`total_minimum`)
    VALUES
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2019-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2020-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2021-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2022-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2023-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2024-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2025-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2026-12-28 10:07:00,''),
    ('2018-12-06 10:13:09','','','','','','','','','','','2018-12-20 10:07:00',2027-12-28 10:07:00,'')
    "
     
     
  5. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    Thanks for the tip!
    It looks like my $moddedanniversaryDate was not wrapped in quotes for the `anniversary_date` column.

    I just changed the line to this

    Code (Text):

    $myDb->quote($moddedanniversaryDate),
     
    Working now!

    Thanks Achartier! Kudos to you!

    Cheers
     
  6. achartier

    achartier Active Member

    Level: Community
    Glad you got it working.
     

Share This Page