[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysql database related help needed

Posted on 2011-05-13
5
Medium Priority
?
374 Views
Last Modified: 2012-05-11
Hi

I have the database like this

id auto_increment
subject
details
office
stamp

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?


Thanks
0
Comment
Question by:3xtr3m3d
5 Comments
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35752800
Why not second autoincrement ?
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35752803
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 c.id < t.id AND c.office = t.office) as seq  FROM table_name t;  

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

Author Comment

by:3xtr3m3d
ID: 35752844
Roads_Roads:
Why not second autoincrement ?

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

Accepted Solution

by:
kivan24 earned 2000 total points
ID: 35752846

1

Create table:
CREATE TABLE `db_sequence` (
  `seq_name` varchar(40) NOT NULL default '',
  `nextid` int(10) NOT NULL default '0',
  PRIMARY KEY  (`seq_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window


2

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

Open in new window


3

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);
      T_unlock();
   } 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, ";}
      }
      $query=substr($query,0,-2);
   } 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


4

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

Open in new window

0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 35752911
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

873 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