I have table named ITEMS where there should be 2 auto increment columns.  So the table structure would be:  ID   |   ITEM_ID --------------------------------- 1    |   ITEM201521080225-001 2    |   ITEM201521080301-002 3    |   ITEM201521080345-003   The column.ITEM_ID structure is ITEMYearDayMonthHourMinute-Increment Value  I managed to Generate the ITEMYearDayMonthHourMinute value thru POST(PHP POST/ PHP Generated).  I am using MySQL and PHPMyAdmin at the moment, and I'm seeing errors if I set column.ITEM_ID to AUTO_INCREMENT    How can I make column.ITEM_ID to AUTO_INCREMENT? Is it Possible?
122704
I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. I know this is technically not possible, but I guess this is a problem others have tackled before, so I'd like to know if there is a well-known 'solution' for this issue?   What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.   This is my current setup:  CREATE TABLE `invoices` (   `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,   `invoicenumber` mediumint unsigned NOT NULL,   `branch` enum('A','B') NOT NULL,   `date` date NOT NULL,   `client` varchar(100) NOT NULL ) COMMENT='' ENGINE='InnoDB';   To check the lateset invoice, I run:  SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'
90792
I have a couple tables in which I created an object ID as either an Int or Bigint, and in both cases, they seem to autoincrement by 10 (ie, the first insert is object ID 1, the second is object ID 11, the third is object ID 21, etc).  Two questions:   Why does it do that? Is that a problem?
30707
I'm using MySQL's AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this?
18644
I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to change that the primary key starts at 6?  CREATE TABLE animals (  id MEDIUMINT NOT NULL AUTO_INCREMENT,  name CHAR(30) NOT NULL,  PRIMARY KEY (id) ) ENGINE=MyISAM;  INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');   Result: id  name 1   dog 2   cat 3   penguin 4   lax 5   whale 6   ostrich    DELETE FROM animals WHERE id = 6; INSERT INTO animals (name) VALUES ('x');   Result: id  name 1   dog 2   cat 3   penguin 4   lax 5   whale 7   x    Thanks for advice.
15142
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.  I have a self-referencing table with a primary key, id and a foreign key parent_id.  +------------+--------------+------+-----+---------+----------------+ | Field      | Type         | Null | Key | Default | Extra          | +------------+--------------+------+-----+---------+----------------+ | id         | int(11)      | NO   | PRI | NULL    | auto_increment |  | parent_id  | int(11)      | YES  |     | NULL    |                |  | name       | varchar(255) | YES  |     | NULL    |                |  | notes      | text         | YES  |     | NULL    |                |  +------------+--------------+------+-----+---------+----------------+   Given a name, how can I query the top-level parent?  Given a name, how can I query all of the id's associated with a record of name = 'foo'?  context: I am not a dba, but am planning to ask a dba to implement this type of hierarchical structure and would like to test some queries. The motivation for doing so is described by .    Here is an example of the relationships among ids in the table:    -- ----------------------------------------------------- -- Create a new database called 'testdb' -- ----------------------------------------------------- SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';  CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `testdb` ;  -- ----------------------------------------------------- -- Table `testdb`.`observations` -- ----------------------------------------------------- CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (   `id` INT NOT NULL ,   `parent_id` INT NULL ,   `name` VARCHAR(45) NULL ,   PRIMARY KEY (`id`) ) ENGINE = InnoDB;  SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;  -- ----------------------------------------------------- -- Add Example Data Set -- -----------------------------------------------------   INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10),     (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);
109033
In mysql, how do I get the primary key used for an insert operation, when it is autoincrementing.  Basically, i want the new autoincremented value to be returned when the statement completes.  Thanks!
37578
Interesting challenge. I was given a piece of code earlier, which I have modified as follows for the Posts page.  /* add time stamp */ add_filter('manage_posts_columns', 'posts_columns', 5); function posts_columns($defaults){     $defaults['your_date_col'] = __('Date');     $defaults['week_number'] = __('Wk#');     $defaults['week_in_quarter'] = __('Wk/Qtr');     return $defaults; } add_action('manage_posts_custom_column', 'posts_custom_columns', 5, 2); function posts_custom_columns($column_name, $id){     if($column_name === 'your_date_col'){     echo the_date('d M'),' ',the_time('Ha');     }     if($column_name === 'week_number'){     echo 'Here: ',the_date();     } }   Strangely, although the date format that I have given shows up in the first column ("your_date_col"), only "Here: " shows up in the second column ("week_number"). Any suggestions as to why the_date function doesn't work in the second column?
24424
I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like:  CREATE TABLE user  (   id int(11) UNSIGNED AUTO_INCREMENT,   name varchar(30) NOT NULL,   PRIMARY KEY id );  CREATE TABLE photo (   id int(11) UNSIGNED AUTO_INCREMENT,   user_id int(11) UNSIGNED,   filename varchar(50) NOT NULL,   PRIMARY KEY id );   A query like:  SELECT p.filename, u.name FROM user AS u INNER JOIN photo AS p ON u.id = p.user_id   Would result in all records of photos and their user's name.   So if it is possible to create a relation on query, why would I use foreign keys and define NO ACTION, considering I want to keep a history of photos, if there is no need for that? Foreign keys helps on performance or just make data more organized?
82615
On PHP 5.3.13/MySQL 5.5.21 the following code doesn't work:  if($check_custom_fields_form!=1){     $sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (                 `form_name` longtext NOT NULL,                 `field_id` bigint(20) NOT NULL,                 FOREIGN KEY (`field_id`) REFERENCES $table_custom_fields (`ID`) ON DELETE CASCADE ON UPDATE CASCADE     ) CHARACTER SET utf8 COLLATE utf8_general_ci";     dbDelta($sql); } if($check_subscribe_cat!=1){     $sql = "CREATE TABLE IF NOT EXISTS ". $table_subscribe_cat ." (                 `subscribe_id` bigint(20) NOT NULL,                 `cat_id` bigint(20) NOT NULL,                 FOREIGN KEY (`subscribe_id`) REFERENCES ".$wpdb-&gt;prefix."tgt_subscription (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,                 FOREIGN KEY (`cat_id`) REFERENCES ".$wpdb-&gt;prefix."terms (`term_id`) ON DELETE CASCADE ON UPDATE CASCADE     ) CHARACTER SET utf8 COLLATE utf8_general_ci";     dbDelta($sql);  }   The  suggested a downgrade to MySQL 5.1.37 (no, thanks) or the following update:  if($check_custom_fields_form!=1){         $sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (                     `form_name` longtext NOT NULL,                     `field_id` bigint(20) NOT NULL,                     KEY(field_id)         ) CHARACTER SET utf8 COLLATE utf8_general_ci";         dbDelta($sql);     } if($check_subscribe_cat!=1){       $sql = "CREATE TABLE IF NOT EXISTS ". $table_subscribe_cat ." (                   `subscribe_id` bigint(20) NOT NULL,                   `cat_id` bigint(20) NOT NULL,                   KEY(subscribe_id),                   KEY(cat_id)       ) CHARACTER SET utf8 COLLATE utf8_general_ci";       dbDelta($sql);    }   Which seems like a rather dirty way to work around the problem (no cascading deletes/updates). Therefore:   Do I really have to live with that ? Is it true that dbDelta only works with foreign key in a 3 year old MySQL version?
120999
I'm wanting to decrement a variable in a MySQL table by one everytime an UPDATE query is ran.  What I have is this, which isn't working:  UPDATE forum SET replys = reply--  WHERE fid = '$id'  Is this possible in any way, or am I going to have to run a SELECT and get the value first, decrement it, and then insert the new value into the UPDATE query?
81661
I have to collect statisctics by days, weeks, months and years of user activity for a site. I am the DB design stage and I wanted to do this stage properly since it will make my coding life easier.  What I have to do is just simply increment the values in the fields by 1 in the DB each time an activity happens. So then I can pull up the date by each day, each week, each month and year. How should my DB be structured? Apologies if this is a simple question for most. It would also be great if this structure could be extendable so that it can be broken down by other categories.  The bit am having trouble with is each month is made up of more days and these days change each calender year.  Thanks all for any help or direction.  Other info: Linux Machine, making use of PHP and MySQL
99266
I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id (Primary key) and for Slno. The output will be  Prod_Id -->>>  PROD0001, PROD0002 and so on. Now if the product name is RACK it should create a id RACK_001 with the start two letters of the product name and auto increment automatically.  For all Product name it should create accordingly,how to do this.  As of now i am using a trigger for auto increment  in varchar data type. Help with the trigger code.  DELIMITER $$   CREATE TRIGGER tg_product_details_INSERT   BEFORE INSERT ON product_details   FOR EACH ROW    BEGIN      INSERT INTO product_details_seq VALUES (NULL);      SET NEW.Created_Date = NOW();      SET NEW.Submitted_Date = NOW();      SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;      SET NEW.Prod_id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0'));    END DELIMITER ;   product name -->> BOOK, SHOE, DRESS, FURNITURE  for all book it should create a autoincrement id BOOK_001, BOOK_002  and for all Shoe it should create a autoincrement id like SHOE_001, SHOE_002  and so on...!!!  Thanks, Acube.
35597
I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys. I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support foreign keys. All id fields are INT(11). When I choose the foo_bar table, click the &quot;relation view&quot; link, and try to set the FK columns to be database.foo.id and database.bar.id, it says &quot;No index defined!&quot; beside each column. What am I missing? Clarification/Update For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin. Also, although I haven't been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this: SELECT *  FROM foo  INNER JOIN foo_bar  ON foo.id = foo_bar.foo_id  INNER JOIN bar ON foo_bar.bar_id = bar.id;  It just makes me uncomfortable not to have the FKs explicitly defined in the database.
59811
Here is the script to create my tables:   CREATE TABLE clients (    client_i INT(11),    PRIMARY KEY (client_id) ); CREATE TABLE projects (    project_id INT(11) UNSIGNED,    client_id INT(11) UNSIGNED,    PRIMARY KEY (project_id) ); CREATE TABLE posts (    post_id INT(11) UNSIGNED,    project_id INT(11) UNSIGNED,    PRIMARY KEY (post_id) );   In my PHP code, when deleting a client, I want to delete all projects posts:  DELETE  FROM posts INNER JOIN projects ON projects.project_id = posts.project_id WHERE projects.client_id = :client_id;   The posts table does not have a foreign key client_id, only project_id. I want to delete the posts in projects that have the passed client_id.  This is not working right now because no posts are deleted.
122069
I have created an Auto Number with the following format: {YYYY}/{000}.  Is it possible for the number increments {000} to reset to 1 for each new year?
105976
I'm trying to create a calculated column that uses the list item's ID field as part of the calculated string value. When I create a new list item, the calculated field seems to always use 0 as the ID value. I know that during creation, the new list items ID is 0 until the content database is updated, which apparently generates the actual ID. Is there a way to get the calculated column to accurately reflect the ID value of the list item?
95710
I have End Date column and I want to add 14 Days on it auto when created, can I do it by workflow? Can I do it via jQuery. If yes how? if no how I can do it?