Inserting Records in another table

[emcguire]

Guys,

I have a PHP plugin I'm trying to insert a series of records into another table upon submit.

I need to first get all the user ids of all the Joomla users in the "registered" group. There are only 10.

Then I need to insert a new record in another table with each users id and the current rowid of the form they are submitting.

I have this so far, but I know its not right, it's not working.

It is inserting the proper amount of records (10) but the fields I'm inserting are just zeroes, no data.

$myDb = JFactory::getDbo();
$query = $myDb->getQuery(true);
$query->select ('*');
$query->from($myDb->quoteName('nab_user_usergroup_map'));
$query->where($myDb->quoteName('group_id'). " = 2");
$myDb->setQuery($query);
$rows = $myDb->loadObjectList();
$acct_id = $listModel->getRow($rowId);

foreach ($rows as $row)
{
$rep_id = $myDb->quoteName('user_id');
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "INSERT INTO nab_account_approvals (rep_id,related_account) VALUES ('$rep_id','$acct_id')";
$db->setQuery($query);
$db->execute();
}

So I set up a query to grab the user_id's of all users in group "registered" (2)

Then for each of those, I need to insert a new row in another table with the user_id and the current (submitted) form's id.

Any direction here, My syntax is clearly not right.

 

[cheesegrits]

$rowid = $formModel->formData['rowid'];
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
   ->select('user_id')
   ->from($myDb->quoteName('nab_user_usergroup_map'))
   ->where($myDb->quoteName('group_id') . " = 2");
$myDb->setQuery($myQuery);
$userIds = $myDb->loadColumn();

foreach ($userIds as $userId) {
   $myQuery
      ->clear()
      ->insert('nab_account_approvals')
      ->set('rep_id = ' . $myDb->quote($userId))
      ->set('related_account = ' . $myDb->quote($rowid));
   $myDb->setQuery($myQuery);
   $myDb->execute();
}

 

[emcguire]

Thanks Sir!

I tried this code onBeforeProcess and onAfterProcess, neither one wrote anything to the table. I entered a new account and submitted form, went through fine, but checked the "approval" table, nothing there. I checked all the field names, it's all correct.

Anything that we might be missing?

 

[emcguire]

Ok, I fiddled with it a bit. First thing is this particular set of data was an import and the id field is actually "ID" not rowid. So I fixed that. I reverted back to the original insert statement I had that was working. I also removed the clear statement. Can you look at this and see if there is any potential issues with this? otherwise it is working as expected now.

$rowid = $formModel->formData['ID'];
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
   ->select('user_id')
   ->from($myDb->quoteName('nab_user_usergroup_map'))
   ->where($myDb->quoteName('group_id') . " = 2");
$myDb->setQuery($myQuery);
$userIds = $myDb->loadColumn();

foreach ($userIds as $userId) {
   $query = "INSERT INTO nab_account_approvals (rep_id,related_account) VALUES ('$userId','$rowid')";
   $myDb->setQuery($query);
   $myDb->execute();
}