Hello to you all,
I am a complete (and almost hopeless) nuB, so please be kind to me even if this question is too trivial.
Problem:
I completed the setting up of all the needed tables with phpMyAdmin. Data has been imported successfully into tables. Now, after reading documentation, I realized that nuBuilder recommends to use a primary key field with the following method : name of field = nameoftable_id, with varchar 25 char long, primary key. With this setup, nuBuilder will populate automatically this field on entry.
Now, I am sure you see me coming, what happends with my tables with data already in via phpMyAdmin. After I added the recommended primary key _id field and browse the table, for sure there is no data in the table_id field.
What can or should I do to correct this problem?
Thank you for reading me. Regards, Alexdp
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
nubuilder populating primary key field in table with data
-
- Posts: 10
- Joined: Sat Sep 24, 2011 3:49 am
nubuilder populating primary key field in table with data
Last edited by alexdp on Sat Mar 25, 2017 1:20 pm, edited 1 time in total.
-
- Site Admin
- Posts: 2781
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 1 time
Re: nubuilder populating primary key field in table with dat
Alexdp,
You'll need to populate each of these fields manually, with a unique string.
Steven
You'll need to populate each of these fields manually, with a unique string.
Steven
-
- Posts: 10
- Joined: Sat Sep 24, 2011 3:49 am
Re: nubuilder populating primary key field in table with dat
Thank you for your reply.
The problem is that I am working with a dummy database and the number of records is limited. If I decide to use my actual database, I have tables containing more than 600 K records. I do not see myself or a member of our staff entering 600 000 unique primary key manually.
There must be a way to populate the pk field with some data that follows the nu(id) format. This could be done, I presume via phpMyAdmin or via nuBuilder procedures. With very limiting knowledge, I will try to find a simple solution.
Note : I am pretty sure my situation is not unique and that prospective users of nuBuilder would be interested.
Regards
The problem is that I am working with a dummy database and the number of records is limited. If I decide to use my actual database, I have tables containing more than 600 K records. I do not see myself or a member of our staff entering 600 000 unique primary key manually.
There must be a way to populate the pk field with some data that follows the nu(id) format. This could be done, I presume via phpMyAdmin or via nuBuilder procedures. With very limiting knowledge, I will try to find a simple solution.
Note : I am pretty sure my situation is not unique and that prospective users of nuBuilder would be interested.
Regards
-
- Posts: 10
- Joined: Sat Sep 24, 2011 3:49 am
Re: nubuilder populating primary key field in table with dat
Hello to you all,
I finally found an easy way to populate the primary key field in a nuBuilder table in which data was aready imported (csv file).
This procedure will generate a unique field content for every records in the table.
The content will be an alphanumeric string (lower case letters and digits) with a length of 15 characters (just like what nuBuilder is generating within the edit forms).
Everything is done within phpMyAdmin
----------------------------------------------------------
In the table already populated with data (tablename), create a new field named tablename_id with ( varchar (25), do not set primary key yet)
Go into SQL tab of phpMyAdmin and type : update `tablename` set `tablename_id`=(SELECT SUBSTR( MD5( UUID( ) ) , 1, 25 ));
Check the result in the Browse tab
Make the tablename_id field primary key
----------------------------------------------------------
I finally found an easy way to populate the primary key field in a nuBuilder table in which data was aready imported (csv file).
This procedure will generate a unique field content for every records in the table.
The content will be an alphanumeric string (lower case letters and digits) with a length of 15 characters (just like what nuBuilder is generating within the edit forms).
Everything is done within phpMyAdmin
----------------------------------------------------------
In the table already populated with data (tablename), create a new field named tablename_id with ( varchar (25), do not set primary key yet)
Go into SQL tab of phpMyAdmin and type : update `tablename` set `tablename_id`=(SELECT SUBSTR( MD5( UUID( ) ) , 1, 25 ));
Check the result in the Browse tab
Make the tablename_id field primary key
----------------------------------------------------------
Code: Select all
UPDATE `team` SET `team_id` = ( SELECT SUBSTR( MD5( UUID( ) ) , 1, 25 ) )
Last edited by alexdp on Thu Mar 23, 2017 12:55 pm, edited 1 time in total.
-
- Site Admin
- Posts: 2781
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 1 time
Re: nubuilder populating primary key field in table with dat
Nice answer alexdp..
In nuBuilder 3 we suggest 25 characters, just so automatic new ids created by nuBuilder still fit.
(it probably won't matter though).
Steven
In nuBuilder 3 we suggest 25 characters, just so automatic new ids created by nuBuilder still fit.
(it probably won't matter though).
Steven
-
- Posts: 26
- Joined: Wed Feb 15, 2017 8:50 am
- Location: Salt Spring Island, British Columbia, Canada
- Contact:
Re: nubuilder populating primary key field in table with dat
Is your existing PK referenced elsewhere in your database?alexdp wrote:I do not see myself or a member of our staff entering 600 000 unique primary key manually... There must be a way to populate the pk field with some data that follows the nu(id) format.
If not, you could simply remove that field's PK designation (it could still be a UNIQUE key), and add a new PK field that is unsigned int auto-increment. MySQL will then go through and automagically give each record a unique, numeric PK.
The allure of meaningful primary keys is huge. But I find the world is against that point-of-view, and that it is generally best to have your PK be an unsigned int opaque value, unrelated to anything in the record.
:::: Jan Steinman EcoReality Co-op ::::