Page 1 of 3
Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 4:14 pm
by 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!
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 4:26 pm
by benritter
btw I also tried adding this same php to the custom code of the form.
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 5:49 pm
by 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?
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 6:31 pm
by 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?
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 6:56 pm
by 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);
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 7:55 pm
by 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.
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 8:19 pm
by 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')");
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 8:54 pm
by 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.
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 9:08 pm
by Janusz
can you post in txt form your latest php code
Re: Questions about running nuAddnuID
Posted: Wed Nov 11, 2020 9:20 pm
by 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`)");
}