How To Save Records Having Autoincrement Primary Key?

[cypherinfo]

Hello, what about this:

nuRunQuery("ISERT INTO conservazione SET mg = '$_POST['mg']', idg = '$_POST['idg']', campo_molt = '$_POST['campo_molt']'");
(mg, idg, anno, campo_molt) are the fields of the table that has currently been inserted from the user in the nuBuilder form.
In the table has to added another field that has to be seen in the nuBuilder form as the primary key of the table. See the attached images.
That way at every save click, a new record is inserted with an autoincrement field generated by MySQL itself.

Thank you.



Cypherinfo.

[steven]

Cypherinfo,

The reason we use db_fetch_row() (a function in nuBuilder's library) instead of mysql_fetch_row() is so that in the future we can develop some libraries that use other dbs than mysql.

My guess is that LAST_INSERT_ID() would return the Primary Key, but you would still have to do an insert query first.

#recordID# and #newID# haven't been very well explained in the wiki, so I have added this http://wiki.nubuilder.com/tiki-index.ph ... and_newID_

If you are going to use nuBuilder you will need to STOP wanting to use auto_increment and find another way to populate a field with a unique number.

Your issue seems to be that you feel numbers are getting wasted, by checking #recordID# you can decide whether or not to add a record to the next_number table. (so it doesn't do it every time).

Steven

[cypherinfo]

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

Hello, well; this thread is about a sample.sql db implementation of nuBuilder not the sampledebtors.sql of the latest nuBuilder release as you are referring in your answers.

I keep on wondering what DB (sample or sampledebtors) to use for the kind of application I'm developing. Only the sampledebtors (nubuilder last realease) contains the next_number table.

Is there a way to port a nuBuilder application from a sampledebtors.sql DB to a sample.sql? If not have you ever thinked about it please?


Thank you.



Cypherinfo.

[steven]

Why do you want to import one db to another just to use next_number?

Use the sql I gave earlier to make it.

Code: Select all

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`))

The only reason I used sampledebtors is to show a sample of how you could do it yourself.

If this doesn't make sense to you then I think you need to take a step back and become more familiar with mysql and using sql.

Steven