0

I have a temp table created with a list of "master tasks". These need to be duplicated so they can be assigned to users as needed. The problem I have is the table column 'id' is a primary key set to auto increment...how can I update the id column for all rows within the table to something like ++$nid ($nid is the max from another table so I do not have duplicate id's). Here is the code used to update the ID, but it only works when I lock the table, remove the auto increment and drop the primary key..but then the id's are all 206 (first id should be 187 - 206). Any way to update multiple rows where the column is ai and a primary key?

$db    = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('id'))
    ->from('#__temp_tasks');
$db->setQuery($query);
$rows =$db->loadObjectList();

    foreach ($rows as $row)
{
  // Insert into destination table
  $query->clear();
  $query->update('#__temp_tasks');
  foreach ($row as $rows)
  {
      $query->set('id' . ' = ' . (int) ++$tasksMaxID);

  }
  $db->setQuery($query);
  $db->execute();
  $query->clear();
} 

enter image description here

1

If I understand you correctly, you should use $query->insert('#__temp_tasks') rather than $query->update('#__temp_tasks'), as you want to insert new rows in the table, is that correct?

I would simplify your task:

// Your first two lines
$db    = JFactory::getDbo();
$query = $db->getQuery(true);
// ...then:
$sql="insert into #__temp_tasks (id, col2, col3) ".
"select id+".$MaxID.", col2, col3 from #__temp_tasks ".
"where id between 0 and 100"; // I guess you need something like this
// ...otherwise your table will grow awfully fast...
$query->setQuery($sql);
// ...your last three lines, more or less
$db->setQuery($query);
$db->execute();
$query->clear();
  • Thank you jonasfh. I will streamline that code. Still learning. I also figured out the reason I couldn't insert the new items was because there was a unique field between columns. Being new to php and Joomla -- so much to learn :/ I simply modified some of the details to be unique and now insert functions properly. – Michael Brown Jan 12 '16 at 15:55
  • Good luck, Michael. But please do not post so many similar questions. I have looked back and you have a lot of questions all really regarding this issue, and many "solved" by yourself where the problem was a typo or something. Regards Jonas – jonasfh Jan 12 '16 at 21:19

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.