How To Save Records Having Autoincrement Primary Key?

[cypherinfo]

Hello, I'm building up a data base interface on a table that has an autoincrement primary key.
When I try to save a new record with nuBuilder, I get the following error message:

"An error has occurred while running this query. Please contact technical support and quote error reference: 93ec528.
The following information is only provided for users logged on as globeadmin.

SQL
INSERT INTO conservazione (PK, mg, idg, anno, campo_molt) VALUES ("14bfe8493e95b6", '23', '22', '0000', 'Gaudiano')

Error
1062: Duplicate entry '14' for key 'PRIMARY' ... "

See the attached images for: the error and the table structure.
Mine is a legacy table and it is supposed I may not alter its fields; how may I save records without altering its structure?

More, my table has a timestamp field (named: download_data; see the attached image); its value, as for my autoincrement primay key, is set by Mysql automatically at every record inserting; is it possible to keep on using those kind of fields with nuBuilder? If yes how?


Thank you.



Cypherinfo.

[steven]

cypherinfo,

With nuBuilder you cannot use tables with autoincrement primary key as with mysql if you use autoincrement, you can only do so if it is the primary key, and nuBuilder uses php's uniqid() function to create the PK. (http://wiki.nubuilder.com/tiki-index.ph ... uilderDocs)

Just one of the many reasons we do this is... In the case of an invoice, if this number is used as the invoice number as well as the PK that invoice items link to, and for some reasons you wanted to change the number, you'd lose the relationship between invoice and invoice items.
Creating incrementing numbers to use for things like invoice numbers should be done some other way** in a field with a unique (but not primary) key.
A PK should never be seen by the user.

** the way we create incrementing numbers is..

Create a table called next_number eg. CREATE TABLE IF NOT EXISTS `next_number` (`next_number_id` int(11) NOT NULL AUTO_INCREMENT, `nxn_key` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`next_number_id`))

In the text object that will hold the new number,

SQL Run Before Display gets INSERT INTO next_number SET nxn_key = '#TT#'
Default Value SQL gets SELECT next_number_id FROM next_number WHERE nxn_key = '#TT#'

Steven

[cypherinfo]

Hello, I tried to do what you suggested me and even when I run the INSERT INTO conservazione SET KEY = '#TT#' statement in phpmyadmin I get a syntax error. I wonder what is '#TT#'?
May be I'm missing something...


Thank you.




Cypherinfo (Giuseppe C.)

P. S.: the message error is: "An error has occurred while running this query. Please contact technical support and quote error reference: be1b516.
The following information is only provided for users logged on as globeadmin.

SQL
INSERT INTO conservazione SET KEY = '___nu14bffccbe1909a___'

Error
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'KEY = '___nu14bffccbe1909a___'' at line 1... "

[steven]

Cypherinfo ,

KEY is a Key word. You can't use a key word as a field name unles you do it this way 'KEY`

Steven

[cypherinfo]

Hello, it seems to work but one thing.
When I access the search screen and click on a record there - see the images attached - I see with phpmyadmin a new record has been created!
That happens every time I access a record! Every access a new record is created; it is all right because of the SQL I added, as you suggested me.

I would like instead, that a new record has to be created only after I click the save button. I need to display one record in a single page too. There, have to be displayed all its fields but the primary key and the timestamp.

I tried to manage to get it. It is very difficult to reach that; may you help me please?
I guess my implementation may be used as a case in your wiki or tutorial when it comes to implement an autoincrement primary key or a timestamp field as well.


Thank you.





Cypherinfo.

[steven]

Cypherinfo,

The example I gave earlier (the table named next_number) is a seperate table, that only has 2 fields.

It is used for the sole purpose of getting an incremented number that can be placed in another table eg. an invoice number in the invoice table.

I suggest you use the exact example I gave.

Steven

[cypherinfo]

Hello, in my case I need the record has to be inserted only after the user click on save.
So even using another table I do need it does not have to grow with a new record every time a user simply click on a item in the browse screen.

How to do that please?


Thank you.




Cypherinfo.

[cypherinfo]

Hello, I was thinking about the possibility to save the record in the table only after the user click on save button of the edit form.
I attached the image of the possible place where I think thye PHP code has to be inserted in order to get that: a new record will be inserted only after clicking on save button.

The way you suggested me to create a record, happens every time the user access the search form and click on a record in order to edit it, and when he/she click on the add button.

When he/she has to update or to see the records I do not need a new record creation, even if that happens in a new table.
I do not know how to manage the deletion of all of those records created without the user will.

The point is: I do not know what is the PHP code to add in order to insert a new record with its primary key with all of the fields have been filled in the edit form by the user.

May you help me please? My case I'm sure may be useful for all of the users - and are not few - with applications similar to mine.


Thank you.



Cypherinfo.

[steven]

Cypherinfo,

If you want to do it this way (and frankly this is more complex and achieves the same thing), you can try this..


In Save After

Code: Select all

if(#recordID# == -1){  // is a new record

   $id = uniqid();
   nuRunQuery("INSERT INTO next_number SET nxn_key = '$id'");
   $t = nuRunQuery("SELECT next_number_id FROM next_number WHERE nxn_key = '$id'");
   $r = db_fetch_row($t);
   $new_incremented_number = $r[0];
   nuRunQuery("UPDATE thetable SET thenumberfield = '$new_incremented_number' WHERE theprimarykey = '#newID#'");

}

(I haven't tested it but this should work.)

Steevn

[cypherinfo]

Hello, thank you for your interest that may be useful for all of the users of nuBuilder when they will come to use a primary key.

From the attached image you may see my "translation" (may be wrong) of your PHP code.
Once again I would like to point out my application has to create (insert) a new record with a primary key (it does not care with what value in it) in a table called conservation.

The steps are the following: (you may see the images attached in the previous posts)
a user may choice to: add a new record or see a record when he/she clicks on it in the search screen;

when adding a record:
1 - after having clicked on the form button he/she click on the add button in the search screen;
2 - enter the value for all of the fields in the edit screen;
3 - click on save to save the new record. (Here, it may happens the user does not want to add the record; that case no record has to be inserted before having clicked on the save button).

when seeing a record:
1 - after having clicked on the form button he/she click on the record of interest;
2 - he/she see the record details in the edit screen (no record here have to be inserted).


Your code does not reach that goal. See the attached image.

How to do that please?


Thank you.



Cypherinfo.


P.S.: Is the hash variable: #newID# corresponding to the MySQL function: LAST_INSERT_ID() ?