Questions about running nuAddnuID

[Janusz]

https://drive.google.com/file/d/1e2FrBH ... sp=sharing
and the adjusted/test code:

Code: Select all

nuAddnuID_a('Test');

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(`$id`)");

}

[benritter]

Hi Janusz, I really appreciate your help here. But I'm still not sure what the problem is. I pasted your adjusted code and changed 'Test' to 'client'.
I am still getting the error in nuDebug:
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'client_id' doesn't exist in table

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

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

If I manually add the column 'client_id VARCHAR (25)' in phpmyadmin, then run the sql above (which should create a PK on the column) I get the error message:
#1062 - Duplicate entry '' for key 'PRIMARY'

[Janusz]

If you just want to add unique id for the table maybe try to do it directly from phpmyadmin.
1. remove primary key from the table you want to modify (if primary key exists)
2. use the following code (tested on MariaDB) - replace only table name in the first line of the code (currently: client table)
3. paste to the SQL box, execute - and it should be OK.
(and before make copy of your DB)

Code: Select all

SET @tab = 'client';

SET @id = concat(@tab,"_id");
SET @code=concat("ALTER TABLE ", @tab , " DROP IF EXISTS ", @id);
EXECUTE IMMEDIATE @code;
SET @code=concat("ALTER TABLE ", @tab , " ADD ", @id , " VARCHAR(25) NOT NULL FIRST");
EXECUTE IMMEDIATE @code;
SET @code = concat("UPDATE ",@tab," SET ",@id,"=concat('x',LEFT(MD5(UUID()),15))");
EXECUTE IMMEDIATE @code;
SET @code=concat("ALTER TABLE ", @tab , " ADD PRIMARY KEY (",@id,")");
EXECUTE IMMEDIATE @code;

added x before uid to avoid risk of collision with nuID afterwards

[benritter]

Woohoo, great success! I suppose it doesn't matter that those unique id's aren't exactly the same as the nuAutoid. Everything on my nuBuilder app seems to work just fine.
I have so many questions like:
Is "(MD5(UUID())" a built in unique id feature of phpmyadmin?
And considering all this, what would be the benefit of using the php procedure we were trying before?
Thank you so much, I am almost done with this project now.
-Ben

[Janusz]

Great , the unique ID has to be just unique - and its the only condition - you can mix all possibilities like: 1, 2, ggfasdkassdhk, jksdfh; sdaasd, 3, abc, .... as long as they are different/unique and not bigger then defined in the table - currently varchar(25).
Inside unBuilder you have nuID() function responsible for generation of unique ID and there is no need to use other ways - but of course it's possible to implement any other methodology ensuring unique id. In normal applications you do not even think of nuID - everythink is done automatically.
If you need to update very few tables it's probably easier to add unique id directly in phpmyadmin - but in case it would be more frequent - then with procedure in nuBuilder you can make it automatic - and both methodologies are fine.
If you have existing table let say client with autonumber column - then you can alter column name - (for example to client_id, other name is fine as well - but -t's good to keep some standards in names) - change from INT to varchar(25) remove autonumber parameter and it should be enough to work later with nuBuilder.

[benritter]

Yeah that all makes sense.
This is more of a phpmyadmin question than a nuBuilder question. But since stackoverflow isn't helping.....
I am still getting the "#1062 Duplicate entry '' for key PRIMARY" error whenever I try to INSERT anything into my client table. I have tried an INSERT INTO SELECT as well as a simple INSERT VALUES.
Any suggestions?

[Janusz]

do you have client_id defined?
in your insert query it must be defined for example:
INSERT INTO client(client_id, c2,...) VALUES ('wqerewqrweq',v2,...);
can you please post exact sql query you use?

[benritter]

I'm not trying to insert the PK column/values. Just some other fields from another table.
INSERT INTO client(day)
SELECT `CSVday` AS `day`
FROM csv;

And this gives the same error:
INSERT INTO client(day)
VALUE ('Tuesday');

'client.day and csv.CSvday are both set as TEXT, NULL, nullable.

[Janusz]

the client_id has no default value, and it's not with auto-increment - therefore it must be defined as unique for every single record you want to insert.
If all other columns has default values or can be null the following should work:

Code: Select all

INSERT INTO client(client_id,day) VALUES ('r1','Tuesday');

but following for sure not:
INSERT INTO client(day) VALUE ('Tuesday');

[benritter]

That gives me :
#1062 - Duplicate entry 'r1' for key 'PRIMARY'

#(?!)