1

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

4
  • PHP Error logs gives out this by try 1 and 2 Invalid status line from script 'index.php': 1064 for try 3 it does not give an errorline in the log. Jan 10, 2023 at 21:49
  • 1
    Quoting strings is necessary; 1, 2, 3, 4, 5, 6 Jan 11, 2023 at 1:24
  • @mickmackusa You are exactly right about that! I had forgotten that, but when I remembered it, I immediately answered it. I could have deleted the question, but decided to keep it for future users who Google search something related to this question title. As you can see, with all the links you posted (thank you for that!) none of these titles are related to datetime type or created. Now is hoping I don't get downvoted like crazy. Thank you for your contribution, even after an answer is already posted. That shows some true dedication, and I salute you for that. Have a great day. Jan 11, 2023 at 2:05
  • 1
    I don't think you should delete this page. While searching, I found quite a few answers that did not include quoting BECAUSE all columns were integers. This is somewhat misleading to the untrained eye. For this reason, this page should be beneficial to future researchers. My point in providing those links was to help stitch related content together. Now those pages are easily referenced on the list in the right-side panel of this page (or bottom of the page if you are on a smalls device). Jan 11, 2023 at 2:20

2 Answers 2

2

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();
   }
}
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.