Questions about running nuAddnuID

[benritter]

Hello, there was a previous question about Data Import with SQL INSERT. A solution was given to add a nuBuilder unique id to a table with imported data. It said to run a procedure with php code to nuAddnuID. Here is what my page looks like for building the procedure.

autoid procedure.png

I tried creating a button to run the procedure but it doesn't do the trick. What am I missing?
Thanks!

[benritter]

btw I also tried adding this same php to the custom code of the form.

[Janusz]

Hi,
I did not go into details of your code, but what I would do first for easier debugging I would arrange the code a little bit differently - then it's much easier to debug with nuDebug()
for example:

Code: Select all

.....
$x="INSERT INTO connection (connection_id,con_part,con_rap) VALUES ('$uid','$pid','$rap_id')";
nuDebug($x);
nuRunQuery($x);
......

and in Debug you get something like that:
[0] : INSERT INTO connection (connection_id,con_part,con_rap) VALUES ('5fac12217edd167','5f6ddc2fb4a469f','c16051123522431003')

Normally if you would paste the output of nuDebug($x) into mysql (with phpmyadmin) it should be executed properly there.
Do you have any errors in nuDebug with your current code?

[benritter]

Hi,
nuDebug give me:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'client.client_id' in 'on clause'

because it's trying to populate the browse form using a table without a PK. Am I running the php in the right place? Is a button to "run" the procedure the right way?

[Janusz]

Is a button to "run" the procedure the right way?

Yes - you can use button to run procedure. Probably everyone has his own habbit for that.
In my case I am using button onclick where I am placing quite often link to the JS function.
(example of one real case from my database):
Besides the link to the function I am protecting from double click, checking some conditions etc. You can place everything in the button onclick function - but often it's much conveniant to keep it in JS custome code. Instead of nuHide you can use nuDisable depanding on preferences.

Code: Select all

>>> code from button onclick
var field=this.id; nuHide(field);   if (nuFORM.edited) {alert("Action stopped. Save current record first"); nuShow(field); return;}   zlecenie_freeze(); setTimeout(function(){nuShow(field);},1500);

it directs to the function: zlecenie_freeze() placed in the JS custome code and the function is following:

Code: Select all

function zlecenie_freeze() {
nuSetProperty('ZLEC_TO_FREEZE',$('#par_zlecenie').val());
nuRunPHPHidden('SUB_FREEZE','z');
} 

and the PHP procedure SUB_FREEZE looks as following:

Code: Select all

$pz=('#ZLEC_TO_FREEZE#');

$q="SELECT sus_name FROM zzzzsys_user WHERE zzzzsys_user_id='#USER_ID#';";
$w = nuRunQuery($q);
$r  = db_fetch_object($w)->sus_name;
if ($r=='') $r='admin';

$timestamp = date('Y-m-d G:i:s');
$q="UPDATE parts SET par_sub_frozen='1', par_updated_by = '$r',par_updated_on = '$timestamp' WHERE par_zlecenie='$pz';";
$w = nuRunQuery($q);
$s="nu_my_refresh();";
nuJavascriptCallback($s);
unset($pz,$q,$w,$r,$timestamp,$s);

[benritter]

Thank you Janusz,
I think it might be an error with how/where I am entering my table name into the template php code. Here is a nuDebug error message:
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column '' doesn't exist in table
'''

Code: Select all

===SQL=========== 

ALTER TABLE `client` ADD PRIMARY KEY(``)

===BACK TRACE====

C:\xampp\htdocs\nubuilder4\nucommon.php(1255) : eval()'d code - line 22 (nuRunQuery)

C:\xampp\htdocs\nubuilder4\nucommon.php(1255) : eval()'d code - line 1 (nuAddnuID_a)

C:\xampp\htdocs\nubuilder4\nucommon.php - line 1255 (eval)

C:\xampp\htdocs\nubuilder4\nucommon.php - line 402 (nuEval)

C:\xampp\htdocs\nubuilder4\nuapi.php - line 47 (nuRunPHPHidden)

'''

Basically, I took the code that Steven posted on the original question and I added my table name 'client' everywhere it said "$tab" leaving the "$". I am a beginner learning sql and python but since I don't know php there could be something else I'm supposed to do to make that piece of code work for me.

[Janusz]

If the code is still as in the picture few posts before
there is no $client_id defined; you defined $id
so the last line should be most likely:
nuRunQuery("ALTER TABLE '$client' ADD PRIMARY KEY ('$id')");

[benritter]

I have been trying a few changes to the original code. As Steven posted it, the end was like:

Code: Select all

 nuRunQuery("ALTER TABLE `$tab` DROP nuautoid");
    nuRunQuery("ALTER TABLE `$tab` ADD PRIMARY KEY(`$t_id`)");
   
}

I think that "$tab" is a parameter of the function nuAddnuID_a('aaa'); where I replaced 'aaa' with 'client'.

Using that in the procedure I get this error in nuDebug:

Code: Select all

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column '' doesn't exist in table

===SQL=========== 

ALTER TABLE `client` ADD PRIMARY KEY(``)

===BACK TRACE====

C:\xampp\htdocs\nubuilder4\nucommon.php(1255) : eval()'d code - line 22 (nuRunQuery)

C:\xampp\htdocs\nubuilder4\nucommon.php(1255) : eval()'d code - line 1 (nuAddnuID_a)

C:\xampp\htdocs\nubuilder4\nucommon.php - line 1255 (eval)

C:\xampp\htdocs\nubuilder4\nucommon.php - line 402 (nuEval)

C:\xampp\htdocs\nubuilder4\nuapi.php - line 47 (nuRunPHPHidden)

It seems like I'm just not sticking the right things in the right places in the original php.

[Janusz]

can you post in txt form your latest php code

[benritter]

nuAddnuID_a('client');

function nuAddnuID_a($tab){

$id = $tab."_id";

nuRunQuery("ALTER TABLE `$tab` ADD `nuautoid` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`nuautoid`)");

nuRunQuery("ALTER TABLE `$tab` ADD `$id` VARCHAR(25) NOT NULL AFTER `nuautoid`;");

$t = nuRunQuery("SELECT nuautoid AS id FROM $tab");

while($r = db_fetch_object($t)){

$nid = nuID();

nuRunQuery("UPDATE $tab SET `$id` = '$nid' WHERE nuautoid = '$r->id'");

}

nuRunQuery("ALTER TABLE `$tab` DROP nuautoid");
nuRunQuery("ALTER TABLE `$tab` ADD PRIMARY KEY(`$t_id`)");

}