Joomla 3.x insert query attempts don't work as desired

Question

Needing a little help to point me to the issue. I am building a component and need to insert a row into the Joomla DB.

This line works, but I have to create the table name myself and I'd rather use the built-in Joomla functions.

$config = JFactory::getConfig();
$dbprefix = $config->get('dbprefix');
$db_arr = array($dbprefix,'conquest_ctg_players');
$table_name = implode("",$db_arr);  

$sql = "INSERT INTO $table_name (id, ordering, state, checked_out, checked_out_time, created_by, modified_by, player_name, player_uuid, player_bann, player_level, player_xp, player_gold, resource_food, resource_supplys, resource_population, quest_energy, troops_basic, troops_advanced, jid)
          VALUES ('null', 1, '1', '0', '0000-00-00 00:00:00', '$userId', '$userId','$nUser', '$myuuid', '0', '1', '0', '50', '50', '50', '100', '100', '100', '0', '$userId')";

but this one errors out:

$sql
->insert($db->quoteName('#__conquest_ctg_players'))
->columns($db->quoteName('id', 'ordering', 'state', 'checked_out', 'checked_out_time', 'created_by', 'modified_by', 'player_name', 'player_uuid', 'player_bann', 'player_level', 'player_xp', 'player_gold', 'resource_food', 'resource_supplys', 'resource_population', 'quest_energy', 'troops_basic', 'troops_advanced', 'jid'))
->values(null, 1, 1, 0, $db->quote('0000-00-00 00:00:00'), $userId, $userId,$nUser, $myuuid,0, 1, 0, 50, 50, 50, 100, 100, 100, 0, $userId);

The error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS ordering) VALUES ()' at line 2

This one will create a new table entry but does not have any values.

$sql
->insert($db->quoteName('#__conquest_ctg_players'))
->columns(id, ordering, state, checked_out, checked_out_time, created_by, modified_by, player_name, player_uuid, player_bann, player_level, player_xp, player_gold, resource_food, resource_supplys, resource_population, quest_energy, troops_basic, troops_advanced, jid)
->values('null', '1', '1', '0', '0000-00-00 00:00:00', '$userId', '$userId','$nUser', '$myuuid', '0', '1', '0', '50', '50', '50', '100', '100', '100', '0', '$userId');

If anyone can tell me why they think it is adding a row, but not inputting info, I would be eternally grateful.

Answer 1

1. #_ will spare you having to use a literal prefix value. I recommend that you use this consistently throughout your project.

2. Your first attempt to use Joomla's query building syntax broke because $db->quoteName() accepts a maximum of two parameters. You have provided ...many.

   $db->quoteName('id', 'ordering', 'state', ...)
   

   Using an array as the first/only parameter would have saved you.

   $db->quoteName(['id', 'ordering', 'state', ...])
   

3. When inserting a new row that will generate a new auto-incremented value and you are naming columns, you may simply omit that column and its null value. Alternatively, if you are filling EVERY column with a value, then you can omit the column names entirely.

4. As for the column and value methods:
   

   • columns() expects a column or an array of columns.
     
   • values() expects a string.

5. See https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase#Using_an_Object for an example of object syntax. You may find it easier to manage.

Answer 2

Sorry to waste time but i figured it out.

This way worked

$columns = array('id', 'ordering', 'state', 'checked_out', 'checked_out_time', 'created_by', 'modified_by', 'player_name', 'player_uuid', 'player_bann', 'player_level', 'player_xp', 'player_gold', 'resource_food', 'resource_supplys', 'resource_population', 'quest_energy', 'troops_basic', 'troops_advanced', 'jid');
$values = array(0, 1, 1, 0, $db->quote('0000-00-00 00:00:00'), $userId, $userId,$db->quote($nUser), $db->quote($myuuid), 0, 1, 0, 50, 50, 50, 100, 100, 100, 0, $userId);
$sql
->insert($db->quoteName('#__conquest_ctg_players'))
->columns($db->quoteName($columns))
->values(implode(',', $values));