Problem creating new forms.

[steven]

Merrill,

Are any of the PKs in these tables auto IDs

Steven

Posts: 16
Joined: Mon Jun 15, 2009 5:33 pm
Private message

[mkingston]

Steven,

No. Most of the values, like invoice number, order number, etc. are contained in a control table. The existing programs grab that value and increment it for the next unique key value required. Those values are all type int. Other unique keys, like identification values (as in the msctp100 table) are assigned by the user and the program insures the user has entered a unique value. Those tables, used for identification, are relative small... maximum of 6 fields.

[steven]

Merrill,

If you create a Procedure - Activity, in nuBuilder with the following PHP code, it will add a new PK to every mySQL table in a database, allowing a sort of "handle" nuBuilder can use for displaying, adding, deleting and editing a record on a nuBuilder Form.

(this will fall over if you have an autoID field on any of the tables)

Code: Select all

//---start code


$a    = nuRunQuery('');

// $a[0]=$DBHost;
// $a[1]=$DBName;
// $a[2]=$DBUserID;
// $a[3]=$DBPassWord;



$sql  = "SHOW TABLES FROM $a[1]";
print $sql;
$tabs = nuRunQuery($sql);

while ($r = db_fetch_row($tabs)) {

   if(substr($r[0], 0, 6) != 'zzsys_'){

      nuRunQuery("ALTER TABLE `$r[0]` DROP PRIMARY KEY", false);
      $s  = "ALTER TABLE `$r[0]` ADD `$r[0]_nuautoid` INT NOT NULL AUTO_INCREMENT ";
      $s .= "PRIMARY KEY FIRST ,ADD `$r[0]_nuid` VARCHAR( 15 ) NOT NULL AFTER `$r[0]_nuautoid`";
      nuRunQuery($s);
      $T = nuRunQuery("SELECT * FROM `$r[0]`");
      while($R = db_fetch_array($T)){
         $newid   = uniqid('1');
         $update  = 'UPDATE `' . $r[0] . '` SET `' . $r[0] . '_nuid' . "` = '$newid' WHERE `"; 
         $update .= $r[0] . "_nuautoid` = '" . $R[$r[0] . "_nuautoid"] . "'";
         nuRunQuery($update);
      }
      $drop    = "ALTER TABLE `$r[0]` DROP `$r[0]_nuautoid`";
      nuRunQuery($drop);
      $index    = "ALTER TABLE `$r[0]` ADD PRIMARY KEY (`$r[0]_nuid`)";
      nuRunQuery($index);
   }
}


//--end code

Hope this helps

Steven