1

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.

  • Christopher, we don't label pages as [SOLVED] in the title here. Please do not edit your pages in this manner. Awarding the green tick to the most helpful, resolving answer is the way to signify the resolution of a question. – mickmackusa Dec 26 '19 at 0:36
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:

  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.

| improve this answer | |
0

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));
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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