Forums

Autoincrement index problematic when i want to make a new record by copying an old one

Permalink
This may not be of interest to [m]any people - I'm still using 5.6.3.1 (which means ADODB), as I'm still building this one website (my 'second' full time job) and am learning PHP, C5, SQL, and various other libraries along the way, and since I have no idea how long it will take me to migrate to 5.7 I'm putting it off until the dust settles.

Here's the deal: I have a table 'TEAMS' with 3 primary keys, and one 'REGID' is auto incremented.

I have a basic dashboard single page to add a new TEAMS record, which works fine.

Problem One: I want to copy an existing TEAMS record, and have it update its REGID field.

I'm using clone to do a shallow copy, then have TEAMS::__clone() to change some fields - e.g. _saved = NULL, and I'm trying to remove REGID so a new key will autogen.
I tried as part of the __clone method: unset(REGID). Nope: error on Insert: REGID is included in the field name list, and the value list showed "Array". The error was 1054: Unknown column 'Array' in 'field list'. Then, I tried REGID = NULL. Nope. REGID still shows in the name list, and the value list shows NULL, and the error was 1048: Column REGID cannot be NULL.

So I fiddled around with the Insert method in adodb_active_record.inc.php (line 744 in my version). Near the top is 
foreach($table->flds as $name=>$fld) {
         $val = $this->$name;
         if(!is_array($val) || !is_null($val) || !array_key_exists($name, $table->keys)) { // original line of code

I noticed that the recordx version of the file doesn't include the is_array test. So I took that out. I also noticed that the array_key_exists test would always return false - so my keys ALWAYS were getting included! By removing the first and third tests, leaving only is_null, I got exactly the behavior I was expecting.

Problem Two:
Later on in my logic, I need to access that auto-gen'd key field to add into another record (not as a key). However, it was always showing up as zero in the object in the program, even though the database was fine. Towards the end of the insert method I found this:
$autoinc = false;
         foreach($table->keys as $k) {
            if (is_null($this->$k)) {
               $autoinc = true;
               break;
            }
         }
         if ($autoinc && sizeof($table->keys) == 1) {
            $k = reset($table->keys);
            $this->$k = $this->LastInsertID($db,$k);
         }

For some reason although the array_key_exist test would return false, down here the code would find REGID as an auto_increment field, but it wouldn't retrieve the LastInsertID because sizeof returns 3!! Boo...

So, I changed the code at the top of the Insert method like so:
$autoincKeyCount = 0; //sss 5.24.15
        $autoincKeyName = '';
      foreach($table->flds as $name=>$fld) {
         $val = $this->$name;
//         if(!is_array($val) || !is_null($val) || !array_key_exists($name, $table->keys)) { // original line of code
            if(is_null($val)) {         // sss
                if (!empty($fld->auto_increment)) {
                    $autoincKeyCount++;
                    $autoincKeyName = $name;
                }
            } else { // end new block
                $valarr[] = $val;
            $names[] = $this->_QName($name,$db);
            $valstr[] = $db->Param($cnt);
            $cnt += 1;
Viewing 15 lines of 17 lines. View entire code block.

and then at the bottom part, replaced the original autoincrement field update with this:
if ($autoincKeyCount == 1) {    // sss
            $this->$autoincKeyName = $this->LastInsertID($db,$autoincKeyName);
         }


I can't tell if I'm creating another (possibly worse) problem. I could resort to simply doing $team = new TEAMS(); and then adding only the fields I want, to solve problem One, and just reload the record to solve Problem 2. But I'm far too stubborn to accept that as the original strategy.

As to why I'm posting this - if someone knows why the changes I've made might be dangerous, i'd appreciate a heads-up! And that might save someone else who tries to use clone...
ssdscott