mysql database related help needed

Posted on 2011-05-13
Last Modified: 2012-05-11

I have the database like this

id auto_increment

every record insert with the office code which user logged in and im using id to edit,delete,search records and there is around 16000 records currently in db

now the my client ask me to add a auto number office vise

how to do this? seems like using mysql auto increment is impossible though

any ideas?

Question by:3xtr3m3d
    LVL 27

    Expert Comment

    by:Lukasz Chmielewski
    Why not second autoincrement ?
    LVL 39

    Expert Comment

    by:Pratima Pharande
    Insert new column seq in table .You have to use a temporary table because MySQL will not let you use a subselect from the same table you are updating.

    CREATE temporary table seq ( id int, seq int);  

    INSERT INTO seq ( id, seq )
    SELECT id,(SELECT count(*) + 1 FROM table_name c WHERE < AND = as seq  FROM table_name t;  

    UPDATE table_name INNER join seq ON table_name .id = SET table_name.seq = seq.seq;
    LVL 3

    Author Comment

    Why not second autoincrement ?

    ya but seems impossible since all office's records are in one table
    LVL 8

    Accepted Solution



    Create table:
    CREATE TABLE `db_sequence` (
      `seq_name` varchar(40) NOT NULL default '',
      `nextid` int(10) NOT NULL default '0',
      PRIMARY KEY  (`seq_name`)

    Open in new window


    Insert office vide record:
    INSERT INTO `db_sequence` (`seq_name`, `nextid`) VALUES ('office_vise', 0);

    Open in new window


    In PHP add functions to work with db_sequence table:
    function nextid($seq_name, $seq_cnt = 1) {
       if (T_lock("db_sequence")) {
          /* get sequence number (locked) and increment */
          $q  = "select `nextid` from `db_sequence` where `seq_name` = '".$seq_name."'";
          $id  = @mysql_query($q);
          $res = @mysql_fetch_array($id);
          /* No current value, make one */
          if (!is_array($res)) {
             $currentid = 0;
             $q = "insert into `db_sequence` values('seq_name', ".$currentid.")";
             $id = @mysql_query($q);
          } else {
             $currentid = $res["nextid"];
          $nextid = $currentid + 1;
          $seq_cnt --;
          $q = "update `db_sequence` set `nextid` = '".($nextid+$seq_cnt)."' where `seq_name` = '".$seq_name."'";
          $id = @mysql_query($q);
       } else {
          return ("cannot lock `db_sequence` - has it been created?");
       return $nextid;
    // --------------------------------------------
    function T_lock($table, $mode="write") {
       $query="lock tables ";
       if (is_array($table)) {
          while (list($key,$value)=each($table)) {
             if ($key=="read" && $key!=0) {$query.="$value read, ";
             } else { $query.="$value $mode, ";}
       } else {
          $query.="`$table` $mode";
       $res = mysql_query($query);
       if (!$res) { return 0; }
       return $res;
    // --------------------------------------------
    function T_unlock() {
       $res = @mysql_query("unlock tables");
       if (!$res) { return 0; }
       return $res;

    Open in new window


    Now insert data to your table
    $theid = nextid("office_vise");
    $sql ="insert into table_name (subject, details, office, stamp) values ('subject text', 'details text', ".$theid.", 'stamp')";

    Open in new window

    LVL 12

    Expert Comment

    by:Mohamed Abowarda
    Increase the value each time you insert a record by getting the number in the last row and add the value in the last row +1 in a variable then insert it to the new row.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Showing your events from Google Calendar in Google Maps Why? I travel all week and I thought it would be ideal if staff in office knew where I was based on my calendar. (OK real reason: my son wanted to see where I would be working, and I thoug…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    In this tutorial viewers will learn how to position overlapping items using z-index in CSS. They will also learn the restrictions on the z-index property.  Create a new HTML document with an internal stylesheet.: Create a div in CSS and name it Red.…
    The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now