Insert datetime type into created column from PHP file from module/component

Question

I can't get the created column filled when inserting items into the database. I am using this code:

function createRecordInLSFCDBTable($id, $userId) {

    echo 'createRecordInLSFCDBTable: ' . $id . ', ' . $userId . '<br>';

    // Collect current date and time
    // Try 1
    // $date = date("Y-m-d h:i:s");
    // $date = strtotime($date);

    // Try 2
    // $date = JFactory::getDate('now', new DateTimeZone('Europe/Berlin'))->format('Y-m-d h:i:s');
    // $date = strtotime($date);

    // Try 3
    // $date = JFactory::getDate()->toSql();

    var_dump($date);

    // Get a db connection.
    $db = JFactory::getDbo();

    // Create a new query object.
    $query = $db->getQuery(true);

    // Insert columns.
    $columns = array('listingid', 'user', 'created_by', 'created');

    // Insert values.
    $values = array($id, $userId, $userId, $date);

    // Prepare the insert query.
    $query
        ->insert($db->quoteName('#__table'))
        ->columns($db->quoteName($columns))
        ->values(implode(',', $values));

    // Set the query using our newly populated query object and execute it.
    $db->setQuery($query);
    $db->execute();

}

I first tried without using Joomla functions with result error 1064.

Then tried after some SO & J! forum searching try 2 and result error 1064.

Some more searching brought me by try 3 and this resulted into Call to a member function toSql() on string.

When I remove the date from the insert columns and insert values lines then it goes through without any issues. Record is created but I need the created column also be filled.

The full output for 1064 is 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 '22:55:38)' at line 3

Answer 1

You can improve a bit your code and abrace the best practice of J4

<?php

use Joomla\CMS\Factory;
use Joomla\Database\DatabaseAwareTrait;
use Joomla\Database\ParameterType;

class YOUR_CLASS 
{
   function createRecordInLSFCDBTable($id, $userId) 
   {
      $db    = Factory::getContainer()->get(DatabaseInterface::class);
      $query = $db->getQuery(true);

      // Get the current date
      $date = Factory::getDate()->toSql();

      $columns = ['listingid', 'created_by', 'created'];
      $values  = [':rowid', ':createdby', ':created']
      
      // Insert the new records into the table
      $query->insert($db->quoteName('#__table'))
         ->columns($db->quoteName($columns))
         ->values(implode(', ', $values))
         ->bind(':rowid', $id, ParameterType::INTEGER)
         ->bind(':createdby', $userId, ParameterType::STRING)
         ->bind(':createddate', $date , ParameterType::INTEGER);
         
         $db->setQuery($query);
         $db->execute();
   }
}

Answer 2

I forgot that it needed to have ' around the string. So I solved it by using

    $date = date("Y-m-d h:i:s");
    $date = "'" . $date . "'";

Just remove everything from try 1, 2, 3 and replace with the above and it should work.