www.webdeveloper.com
Results 1 to 4 of 4

Thread: Automatic computation of field from values of other fields during INSERT

  1. #1
    Join Date
    Jun 2009
    Location
    Miami
    Posts
    118

    Automatic computation of field from values of other fields during INSERT

    This should be basic for SQL experts:

    Every time I create a record (INSERT) in my table, I want field 1 to be unique and it will be determined values in three other fields, two which are variable length strings and one which is date type. It works by concatenating the strings (the date is cast as a string, either as a Unix timestamp integer or as readable expression of the date...it's all arbitrary) and then computing the CRC32 value of the concatentation.

    field1 = CRC32(concatenation of field2, field3, timestamp? form of field4)

    field1 becomes my "document ID" column

    But I don't want to write the SQL in the phpMyAdmin interface I am presently using to work on the records every time I do an INSERT/UPDATE. I want it to be automatic. I want the database/table/server to execute the procedure on that table when an INSERT/UPDATE is done, and the field1 (column1) should not even be presented by phpMyAdmin as modifiable to the user when the INSERT/UPDATE interface is presented, since it is computed.

    How is that set up?

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    If I understand you correctly, all you want is a unique document_id.

    If that is so, all you need to do is create a primary key column called document_id and set it as an auto-increment integer. When you then insert values into the other columns in the table, document_id will be automatically incremented by 1.

    Code:
     
    /*Table structure for table `tbldemo` */
     
    DROP TABLE IF EXISTS `tbldemo`;
     
    CREATE TABLE `tbldemo` (
      `fldDocument_id` int(11) NOT NULL AUTO_INCREMENT,
    .
    .
    .
    .
    Last edited by tirna; 03-22-2010 at 05:38 AM.

  3. #3
    Join Date
    Jun 2009
    Location
    Miami
    Posts
    118
    Quote Originally Posted by tirna View Post
    If I understand you correctly, all you want is a unique document_id.
    Actually that is not all I really want. For example, my table records represented web documents posted to a web site, the fields of the record include document titles and subtitles, dates the document was initially posted and modified, and perhaps even the entire contents of the document itself. I might want to compute the MD5 value of one or more of those fields, and have that automagically put into a hash value (MD5, SHA-1, CRC32...however it is calculated) field in the record during INSERT or UPDATE operations. And I might want to do a REPLACE on records already present to make sure the value is set properly, assuming I change the mode of computation for the field.

    If that is so, all you need to do is create a primary key column called document_id and set it as an auto-increment integer. When you then insert values into the other columns in the table, document_id will be automatically incremented by 1.

    Code:
     
    /*Table structure for table `tbldemo` */
     
    DROP TABLE IF EXISTS `tbldemo`;
     
    CREATE TABLE `tbldemo` (
      `fldDocument_id` int(11) NOT NULL AUTO_INCREMENT,
    .
    .
    .
    .
    I am familiar that operation. What I really want the AUTO INCREMENT to do is follow a custom computation, not its x = x + 1 of an int value. Indeed, the computed "doc ID" may not even be an integer.

    What is more, I don't want to have to remember the SQL myself for that field on an INSERT/UPDATE. I want it to be part of the marrow of that table when such operations are done. I am guessing that something has to be done to one of the tables in the "mysql" database to get this procedure executed....a trigger of some kind.

  4. #4
    Join Date
    Jan 2009
    Posts
    3,346
    A non-integer PK index will slow complex queries. I would recommend making an additional field if you want a combined indexed field for your own purposes. It is also a best practice to not alter the PK once the table is in use and from your explanation it sounds like you are creating a human readable short form field. Is that correct?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles