Tables & IDs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Subfusion
Forum Newbie
Posts: 18
Joined: Mon Aug 15, 2005 6:53 am
Location: Spain

Tables & IDs

Post by Subfusion »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I've got a table which has an ID field and it automatically increments. When incrementing it incriments in the following order: 1, 2, 3, 4, 5; I would like my IDs to start in this order: 0001, 0002, 0003, 0004, 0005. 

How can I achieve this?

This is my table structure:
[syntax="sql"]
CREATE TABLE `orders` (
  `id` int(5) NOT NULL auto_increment,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `website` text NOT NULL,
  `notes` longtext NOT NULL,
  `subpages` int(11) NOT NULL default '0',
  `integration` text NOT NULL,
  `template` text NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `status` int(11) NOT NULL default '0',
  `key` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Re: Tables & IDs

Post by hrubos »

Subfusion wrote:I've got a table which has an ID field and it automatically increments. When incrementing it incriments in the following order: 1, 2, 3, 4, 5; I would like my IDs to start in this order: 0001, 0002, 0003, 0004, 0005.

How can I achieve this?

This is my table structure:

Code: Select all

CREATE TABLE `orders` (
  `id` int(5) NOT NULL auto_increment,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `website` text NOT NULL,
  `notes` longtext NOT NULL,
  `subpages` int(11) NOT NULL default '0',
  `integration` text NOT NULL,
  `template` text NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `status` int(11) NOT NULL default '0',
  `key` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
I haven't try this way but I thing it can be. so you insert the first vaule is 0001, and too choose autoincrement. Try it
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Set the field to ZERO FILL.
User avatar
neel_basu
Forum Contributor
Posts: 454
Joined: Wed Dec 06, 2006 9:33 am
Location: Picnic Garden, Kolkata, India

Post by neel_basu »

ya zero Field is What You Want
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

feyd wrote:Set the field to ZERO FILL.
How can we achieve this ZERO FILL? Will you please explain more about this ?
Thank you.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It's a field attribute similar to "NOT NULL"
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

and it's an mysql specific extension to the sql standard and
http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html wrote:If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
It's not a bad thing, just to remember.
Post Reply