[PHP-DB] auto_increment
I have the auto_increment on one of my variables. During the past few days I have been doing testing on a "live" database and created several test records which I now have deleted from my table. Is there any way of setting the auto_increment value to match the last "correct" number? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] auto_increment
How do I change the auto_increment / auto_index value? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] auto_increment
How do I reset the auto_increment value to 1 in table abc? Ron
[PHP-DB] auto_increment
Does anyone see anything wrong with the $query syntax? Ron mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="ALTER TABLE sessions auto_increment = '1'"; mysql_query($query); mysql_close();
[PHP-DB] auto_increment
I am wondering if something like the following will work in mySQL: ALTER TABLE `stats` ADD `visits` INT( 25 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ORDER BY `initial_access` ASC This particular syntax won't work though. initial_access is a column that contains a unix timestamp. I am trying to get the auto_increment value to be added in order of sequence of when the visits occurred. Thank you. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] AUTO_INCREMENT
hi,
i have a problem
i have a database table created with the statement:
CREATE TABLE chatmessages (id tinyint(6) DEFAULT '0' NOT NULL AUTO_INCREMENT, message
text, username varchar(100), UNIQUE id (id);
I insert records with:
INSERT INTO chatmessages (message, username) VALUES ('$message', '$username')
this works fine (for a while), i can insert messages and retreive them with php.
But after some time i get the error:
ERROR 1062: Duplicate entry '127' for key 1
but how can this be because i've set it to AUTO_INCREMENT.
can someone help me?
thanks in advance
Joris Kluivers
[PHP-DB] Auto_Increment
What is the best field type to use for an Auto_Increment field? i am using int at the moment, is there a better one? -- Ben Cairns - Head Of Technical Operations intasept.COM Tel: 01332 365333 Fax: 01332 346010 E-Mail: [EMAIL PROTECTED] Web: http://www.intasept.com "MAKING sense of the INFORMATION TECHNOLOGY age @ WORK.." -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] AUTO_INCREMENT problemos
Hi Wondering if anybody can help with this: My table has an INT column named 'member_id' which is set to AUTO_INCREMENT. It works very well but I have inserted and deleted some test rows during the development stage and now my incremented numbers have jumped ahead. The deleted rows (their member_id values) have not been forgotten it seems and the DB is using them still to evaluate the next number. Any ideas? Simon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
I have the auto_increment on one of my variables. During the past few days I have been doing testing on a "live" database and created several test records which I now have deleted from my table. Is there any way of setting the auto_increment value to match the last "correct" number? Ron You don't say what database application you're using, but if it's MySQL, you can change the auto increment value using: ALTER TABLE tablename AUTO_INCREMENT = 1 That being said, you actually don't have to do this (and often shouldn't, really). Having gaps in your auto increment sequence shouldn't be a problem. Larry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
Thanks Larry. It was mySQL ... Ron On Sun, 26 Dec 2004 20:47:37 -0500, Larry E. Ullman wrote > > I have the auto_increment on one of my variables. During the past few > > days > > I have been doing testing on a "live" database and created several test > > records which I now have deleted from my table. Is there any way of > > setting > > the auto_increment value to match the last "correct" number? Ron > > You don't say what database application you're using, but if it's > MySQL, you can change the auto increment value using: > ALTER TABLE tablename AUTO_INCREMENT = 1 > > That being said, you actually don't have to do this (and often > shouldn't, really). Having gaps in your auto increment sequence > shouldn't be a problem. > > Larry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
Ron Piggott (PHP) wrote: How do I change the auto_increment / auto_index value? Ron Do a query that does what you want. Without knowing the type of DB you're using (and since this is a "PHP and Database" list not just specific DB list) or any other info, there really isn't much you can get from here. You should likely ask on a list specific to your question. This will help in your search for info: http://www.catb.org/~esr/faqs/smart-questions.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
I am using mySQL On Mon, 2006-05-01 at 13:21 +, replies-lists- [EMAIL PROTECTED] wrote: > > Original Message > > Date: Monday, May 01, 2006 09:14:36 AM -0400 > > From: "Ron Piggott (PHP)" <[EMAIL PROTECTED]> > > Subject: [PHP-DB] auto_increment > > > > How do I change the auto_increment / auto_index value? Ron > > -- End Original Message -- > > that depends on the database you're using -- and probably best asked on > the appropriate db-specific mailing list. > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
On 5/1/06, Ron Piggott (PHP) <[EMAIL PROTECTED]> wrote: I am using mySQL .. a google search for "mysql reset auto_increment" reveals the answer! -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
Ron Piggott (PHP) wrote: I am using mySQL In that case, mysql.com woudl have MySQL specific resources since it has nothgin to do with PHP (aside form that is apparently what you're using to interact with MySQL) Please read http://www.catb.org/~esr/faqs/smart-questions.html#forum the entire URL is excellent but that specifric section will apply to this thread. Also *please* do not top post :) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] auto_increment
are you dumping the data or you just want to reset? issuing the 'empty table' command in phpmyadmin will do that or you can do ALTER TABLE tbl AUTO_INCREMENT = 100; bastien From: Ron Piggott <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: PHP DB Subject: [PHP-DB] auto_increment Date: Sun, 11 Mar 2007 20:01:30 -0400 How do I reset the auto_increment value to 1 in table abc? Ron _ This March Break, Have An Outdoor Fun-For-All! http://local.live.com/?mkt=en-ca/?v=2&cid=A6D6BDB4586E357F!147 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] auto_increment command
I am not sure if that last e-mail went through or not. I am wondering how to re-set the auto_increment field back to 1 in one of my tables. Ron
Re: [PHP-DB] auto_increment
Hey friend! I can't see another way to fix your table without a processing (like a stored procedure, script, etcetera). But I believe there is a workaround, which involves a creation of another table. See this: mysql> CREATE TABLE stats2 LIKE stats; mysql> ALTER TABLE stats2 ADD COLUMN id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id); mysql> INSERT INTO stats2 SELECT *, 0 FROM stats ORDER BY initial_access; Don't forget to see the correct type of INT which should be used. Cheers, Adriano! > > > > > > > On Tue, 7 Sep 2010 05:29:07 -0400 "Ron Piggott" wrote: I am wondering if something like the following will work in mySQL: ALTER TABLE `stats` ADD `visits` INT( 25 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ORDER BY `initial_access` ASC This particular syntax won't work though. initial_access is a column that contains a unix timestamp. I am trying to get the auto_increment value to be added in order of sequence of when the visits occurred. Thank you. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
I am receiving the following error Adriano: SQL query: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats` ; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `stats2` LIKE `stats`' at line 2 The complete commands were: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats`; ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST; INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY `initial_access`; I don't understand the error, your way of creating a table is new to me. Did something small get missed? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
You are missing a semicolon after the first alter statement. Kapu On 7. 9. 2010 18:05, Ron Piggott wrote: I am receiving the following error Adriano: SQL query: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats` ; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `stats2` LIKE `stats`' at line 2 The complete commands were: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats`; ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST; INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY `initial_access`; I don't understand the error, your way of creating a table is new to me. Did something small get missed? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
Hello! I didn't use any DROP statements. Do exactly as following: mysql> CREATE TABLE stats2 LIKE stats; mysql> ALTER TABLE stats2 ADD COLUMN id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id); mysql> INSERT INTO stats2 SELECT *, 0 FROM stats ORDER BY The first line copies the structure of table stats to a new table called stats2; The second one, create a new field called id; The third inserts all records from stats to stats2, in correct order. After all, you need drop the table stats and rename the table stats2 to stats: mysql> DROP TABLE stats; mysql> RENAME TABLE stats2 TO stats; Be careful, I don't know your environment. []'s Adriano! > > > > > > > > On Tue, 07 Sep 2010 18:10:34 +0200 Kapu wrote: You are missing a semicolon after the first alter statement. Kapu On 7. 9. 2010 18:05, Ron Piggott wrote: I am receiving the following error Adriano: SQL query: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats` ; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `stats2` LIKE `stats`' at line 2 The complete commands were: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats`; ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST; INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY `initial_access`; I don't understand the error, your way of creating a table is new to me. Did something small get missed? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AUTO_INCREMENT
At 12:58 PM 3/4/01 +0100, Joris Kluivers wrote:
>hi,
>
>i have a problem
>i have a database table created with the statement:
>CREATE TABLE chatmessages (id tinyint(6) DEFAULT '0' NOT NULL
AUTO_INCREMENT, message text, username varchar(100), UNIQUE id (id);
>
>I insert records with:
>INSERT INTO chatmessages (message, username) VALUES ('$message', '$username')
>
>this works fine (for a while), i can insert messages and retreive them
with php.
>But after some time i get the error:
>ERROR 1062: Duplicate entry '127' for key 1
>but how can this be because i've set it to AUTO_INCREMENT.
>
>can someone help me?
>thanks in advance
Don't use tinyint. It only allows 128 values (0 to 127). Any values
larger than 127 will go in as 127.
Try smallint or int.Sounds like phpadmin, it defaults variables as
tinyint
=
"To dwell within Samsara, however, is to
be subject to the works of those mighty
among dreamers."
- Mahasamatman, in Zelazny's "Lord of Light"
Andrew Apold
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] AUTO_INCREMENT
on 3/4/01 3:58 AM, Joris Kluivers at [EMAIL PROTECTED] wrote: > CREATE TABLE chatmessages (id tinyint(6) DEFAULT '0' NOT NULL > AUTO_INCREMENT, message text, username varchar(100), UNIQUE id (id); > with php. > But after some time i get the error: > ERROR 1062: Duplicate entry '127' for key 1 > but how can this be because i've set it to AUTO_INCREMENT. That's the limit of the TINYINT type. ALTER TABLE and change the type to INT. Paul -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
SV: [PHP-DB] AUTO_INCREMENT
change
CREATE TABLE chatmessages (id tinyint(6) DEFAULT '0' NOT NULL
AUTO_INCREMENT, message text, username varchar(100), UNIQUE id (id);
to
CREATE TABLE chatmessages (id int(11) DEFAULT '0' NOT NULL
AUTO_INCREMENT, message text, username varchar(100), UNIQUE id (id);
mvh
Knut
> -Opprinnelig melding-
> Fra: Joris Kluivers [mailto:[EMAIL PROTECTED]]
> Sendt: 4. mars 2001 12:59
> Til: [EMAIL PROTECTED]
> Emne: [PHP-DB] AUTO_INCREMENT
>
>
> hi,
>
> i have a problem
> i have a database table created with the statement:
> CREATE TABLE chatmessages (id tinyint(6) DEFAULT '0' NOT NULL
> AUTO_INCREMENT, message text, username varchar(100), UNIQUE id (id);
>
> I insert records with:
> INSERT INTO chatmessages (message, username) VALUES
> ('$message', '$username')
>
> this works fine (for a while), i can insert messages and
> retreive them with php.
> But after some time i get the error:
> ERROR 1062: Duplicate entry '127' for key 1
> but how can this be because i've set it to AUTO_INCREMENT.
>
> can someone help me?
> thanks in advance
>
> Joris Kluivers
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Auto_Increment
The correct question is if it's even possible to use a non integer field with auto_increment with MySQL (assuming it's MySQL) and the answer is : No. you must use an int type. >From the MySQL manual : "An integer column may have the additional attribute AUTO_INCREMENT. ". Sincerely berber Visit http://www.weberdev.com Today!!! To see where PHP might take you tomorrow. -Original Message- From: Ben Cairns [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 29, 2001 12:06 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Auto_Increment What is the best field type to use for an Auto_Increment field? i am using int at the moment, is there a better one? -- Ben Cairns - Head Of Technical Operations intasept.COM Tel: 01332 365333 Fax: 01332 346010 E-Mail: [EMAIL PROTECTED] Web: http://www.intasept.com "MAKING sense of the INFORMATION TECHNOLOGY age @ WORK.." -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Auto_Increment
You can use the Serial Datatype if using Postgres. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Auto_Increment
Hi, well usually yuto_increment is used on ID fields. then depending on how many rows your table has tinyint (up to 255; 1k), smallint (up to 65535; 2k) mediumint (up to 16777215; 3k) int (up to 4294967295, 4k). Johannes "Ben Cairns" <[EMAIL PROTECTED]> schrieb im Newsbeitrag 3AC2FA65@tech-01">news:3AC2FA65@tech-01... > What is the best field type to use for an Auto_Increment field? > > i am using int at the moment, is there a better one? > > -- Ben Cairns - Head Of Technical Operations > intasept.COM > Tel: 01332 365333 > Fax: 01332 346010 > E-Mail: [EMAIL PROTECTED] > Web: http://www.intasept.com > > "MAKING sense of > the INFORMATION > TECHNOLOGY age > @ WORK.." > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] auto_increment problem???
Miles: Thanks for your input. I'm running MySQL 3.23.49-nt-log on php4.2.1. and the table in question is of type: MyISAM. I noticed that, upon returning to work Monday (today) I deleted * from the table and the auto_increment column reverted back to starting from one again. However, today I'm deleting * from the same table and the auto_increment numbers start where the last [now deleted] entry left off. I assume then that this table type has some relation or other to a database connection. By this I mean if the DB connection times out or otherwise quits, the auto_increment column assumes to re-use keys and start again from one?? So is there then a PHP-MySQL function that can be used right after a Query that explicitly tells MySQL to close such a connection so that columns of type: INT - auto_increment will *always* start from 1 if the table contains no records (table is empty)?? Thanks a lot for your help. :-) -Original Message- From: Miles Thompson [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 24, 2002 12:12 AM To: Russ; [EMAIL PROTECTED] Cc: Ross Gerring Subject: Re: [PHP-DB] auto_increment problem??? Russ, Check your docs to confirm this, as you are probably running a later version than I'm familiar with. If the table is defined as type "MyISAM" the autoincrement numbers do not get reused, they steadily increase. You can rely on them. The default table type is ISAM, and the autoincrement numbers do get reused. How did I discover this? Same way you did, disappearing keys and very strange results on selects. Fortunately I discovered the difference before I had too much data, but it had me going for a while. Cheers - Miles Thompson At 05:29 PM 8/23/2002 +0800, Russ wrote: >G'day folks: > >The way I have my MySQL table set up should work as follows: > >1). If table IS NOT empty, get MySQL to insert auto_increment value into >primary key column as normal, then get value of previous record's >second, none primary ID column. (latter column used for lookup purposes >elsewhere) > >(This is do-able as there is already an existing record from which to >gather a second none-primary ID.) > >2). If table IS empty, insert auto_increment value as normal, into >primary key column and *the same value* into the second, none-primary ID >column. > >The thing is, as I'm using auto_increment as my primary key definition, >and occassionally this table is emptied (deleted from), how can I get >the second none primary ID column to reflect the new primary key >auto_increment value of a new record, if no previous record exists?? > >I hope you understand what I mean! ;-) >Cheers. > >Russ > >Mr Russ Michell >Web Applications Developer > >Itomic.com >Email: [EMAIL PROTECTED] >Tel: +61 (0)8 9321 3844 >Fax: +61 (0)8 6210 1364 >Post: PO Box 228, Innaloo, WA 6918, Australia >Street: Suite 24, 158 William St, Perth, WA 6000, Australia > >"No proof of existence is not proof of non-existence." >(Physicist: Stanton T. Friedman on Debunking Ufology) > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] auto_increment problem???
Russ, I'd pursue this on the mysql list as this behaviour appears erratic. I'd have to check docs, but would assume that autoincrement data is kept in the parent (mysql) table. If these keys are not sequential it doesn't matter. (That's one of the great things about Visual FoxPro's sys(2015) funtion - it generates a unique key, one simply has to call it and insert the returned value.) The alternative is maintaining a key table where the value of the last used key (plus one) is stored for each table requiring a unique key. Then you're off down a completely different track, with record locking issues, etc. Much better to verify the way MySQL does it. Miles At 11:26 AM 8/26/2002 +0800, Russ wrote: >Miles: > >Thanks for your input. > >I'm running MySQL 3.23.49-nt-log on php4.2.1. and the table in question >is of type: MyISAM. >I noticed that, upon returning to work Monday (today) I deleted * from >the table and the auto_increment column reverted back to starting from >one again. > >However, today I'm deleting * from the same table and the auto_increment >numbers start where the last [now deleted] entry left off. > >I assume then that this table type has some relation or other to a >database connection. By this I mean if the DB connection times out or >otherwise quits, the auto_increment column assumes to re-use keys and >start again from one?? > >So is there then a PHP-MySQL function that can be used right after a >Query that explicitly tells MySQL to close such a connection so that >columns of type: INT - auto_increment will *always* start from 1 if the >table contains no records (table is empty)?? > >Thanks a lot for your help. :-) > >-Original Message- >From: Miles Thompson [mailto:[EMAIL PROTECTED]] >Sent: Saturday, August 24, 2002 12:12 AM >To: Russ; [EMAIL PROTECTED] >Cc: Ross Gerring >Subject: Re: [PHP-DB] auto_increment problem??? > > >Russ, > >Check your docs to confirm this, as you are probably running a later >version than I'm familiar with. If the table is defined as type "MyISAM" > >the autoincrement numbers do not get reused, they steadily increase. You > >can rely on them. The default table type is ISAM, and the autoincrement >numbers do get reused. > >How did I discover this? Same way you did, disappearing keys and very >strange results on selects. Fortunately I discovered the difference >before >I had too much data, but it had me going for a while. > >Cheers - Miles Thompson > >At 05:29 PM 8/23/2002 +0800, Russ wrote: > >G'day folks: > > > >The way I have my MySQL table set up should work as follows: > > > >1). If table IS NOT empty, get MySQL to insert auto_increment value >into > >primary key column as normal, then get value of previous record's > >second, none primary ID column. (latter column used for lookup purposes > >elsewhere) > > > >(This is do-able as there is already an existing record from which to > >gather a second none-primary ID.) > > > >2). If table IS empty, insert auto_increment value as normal, into > >primary key column and *the same value* into the second, none-primary >ID > >column. > > > >The thing is, as I'm using auto_increment as my primary key definition, > >and occassionally this table is emptied (deleted from), how can I get > >the second none primary ID column to reflect the new primary key > >auto_increment value of a new record, if no previous record exists?? > > > >I hope you understand what I mean! ;-) > >Cheers. > > > >Russ > > > >Mr Russ Michell > >Web Applications Developer > > > >Itomic.com > >Email: [EMAIL PROTECTED] > >Tel: +61 (0)8 9321 3844 > >Fax: +61 (0)8 6210 1364 > >Post: PO Box 228, Innaloo, WA 6918, Australia > >Street: Suite 24, 158 William St, Perth, WA 6000, Australia > > > >"No proof of existence is not proof of non-existence." > >(Physicist: Stanton T. Friedman on Debunking Ufology) > > > > > >-- > >PHP Database Mailing List (http://www.php.net/) > >To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AUTO_INCREMENT problemos
Thats how its supposed to work. It won't reuse old numbers even after you delete the entire row. Adam On Tue, 27 Aug 2002, simon wrote: > Hi > > Wondering if anybody can help with this: > > My table has an INT column named 'member_id' which is set to AUTO_INCREMENT. > It works very well but I have inserted and deleted some test rows during the > development stage and now my incremented numbers have jumped ahead. > The deleted rows (their member_id values) have not been forgotten it seems > and the DB is using them still to evaluate the next number. > > Any ideas? > > Simon > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AUTO_INCREMENT problemos
Hi simon > My table has an INT column named 'member_id' which is set to AUTO_INCREMENT. > It works very well but I have inserted and deleted some test rows during the > development stage and now my incremented numbers have jumped ahead. > The deleted rows (their member_id values) have not been forgotten it seems > and the DB is using them still to evaluate the next number. Assuming we're talking about MySQL, ALTER TABLE will do the biz: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] AUTO_INCREMENT problemos
Checl your MySQL docs and redefine the table if necessary: table type of ISAM - autoincrement numbers get reused table type of MyISAM - autoincrement numbers are not reused. Many people prefer the latter and use the values as primary and foreign keys. Miles Thompson At 05:21 PM 8/27/2002 +0200, simon wrote: >Hi > >Wondering if anybody can help with this: > >My table has an INT column named 'member_id' which is set to AUTO_INCREMENT. >It works very well but I have inserted and deleted some test rows during the >development stage and now my incremented numbers have jumped ahead. >The deleted rows (their member_id values) have not been forgotten it seems >and the DB is using them still to evaluate the next number. > >Any ideas? > >Simon > > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment command
u can't - Original Message - From: "Ron Piggott" <[EMAIL PROTECTED]> To: "PHP DB" Sent: Monday, March 12, 2007 7:52 AM Subject: [PHP-DB] auto_increment command > I am not sure if that last e-mail went through or not. I am wondering > how to re-set the auto_increment field back to 1 in one of my tables. > Ron there no such thing reset auto_increment if you need the reset.. there were way to do that. rename the table.. then take query to build it create new table and tralalla.. u have reset it. fyi. if inside the table already have a data were id was 100.. the next id must be 101.. btw.. if you want to make your table more clean (i said about.. how random the num).. there is a way. what reason u wanna do that?? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment command
Actually you can. As Bastien pointed out: ALTER TABLE tbl AUTO_INCREMENT = 1; This may screw with your indexes though, if you have a primary indexed, or unique indexed row, and you set this to 1, mysql >>MAY<< try and insert conflicting values. I've never done this so I have no idea how this is handled by the server, but I suspect you'll just get an error. -Micah On 03/11/2007 06:55 PM, bedul wrote: u can't - Original Message - From: "Ron Piggott" <[EMAIL PROTECTED]> To: "PHP DB" Sent: Monday, March 12, 2007 7:52 AM Subject: [PHP-DB] auto_increment command I am not sure if that last e-mail went through or not. I am wondering how to re-set the auto_increment field back to 1 in one of my tables. Ron there no such thing reset auto_increment if you need the reset.. there were way to do that. rename the table.. then take query to build it create new table and tralalla.. u have reset it. fyi. if inside the table already have a data were id was 100.. the next id must be 101.. btw.. if you want to make your table more clean (i said about.. how random the num).. there is a way. what reason u wanna do that??
RE: [PHP-DB] AUTO_INCREMENT value
I figured it out, the permissions weren't set on the user to allow the command to work. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] auto_increment in mysql
In mysql, the auto_increment start at 1 default, But, how can I make the auto_increment start at 0? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] auto_increment and INSERT INTO
I have various tables where a column is set to auto_increment in my
table structure. I have been using the following INSERT query format:
INSERT INTO table VALUES ('$auto_increment_variable','$variable_1',
'$variable_2')
and then in applications where I have needed to know the value of the
$auto_increment_variable I have immediately queried the table for
$variable_1 and $variable_2 and used
$reference = mysql_result($result,$i,"reference");
to determine what numeric value was assigned.
Is there a way I may find out what value was assigned to the
$auto_increment_variable when the INSERT INTO query is issued?
Thanks.
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment in mysql
I may be semi-new to this MySQl lark but I'm sure auto_increment starts at 0. Russ #---# "Believe nothing - consider everything" Russ Michell Anglia Polytechnic University Webteam http://gertrude.sipu.anglia.ac.uk/webteam [EMAIL PROTECTED] +44 (0)1223 363271 ext 2331 www.theruss.com #---# -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] auto_increment in mysql
The version you use may be lower than 3.23 because auto_increment sequence number begin at 1 for MySQL version up to 3.23. "Russ Michell" <[EMAIL PROTECTED]> ? news:[EMAIL PROTECTED]... > I may be semi-new to this MySQl lark but I'm sure auto_increment starts > at 0. > > Russ > > #---# > > "Believe nothing - consider everything" > > Russ Michell > Anglia Polytechnic University Webteam > http://gertrude.sipu.anglia.ac.uk/webteam > [EMAIL PROTECTED] > +44 (0)1223 363271 ext 2331 > > www.theruss.com > > #---# > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] auto_increment in mysql
I use MySQL 3.22.23 but I just had a hunt about on the web and I found a posting that confirmed your findings - I.E: auto_increment starts at 1!! I stand corrected! Russ #---# "Believe nothing - consider everything" Russ Michell Anglia Polytechnic University Webteam http://gertrude.sipu.anglia.ac.uk/webteam [EMAIL PROTECTED] +44 (0)1223 363271 ext 2331 www.theruss.com #---# -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] auto_increment in mysql
> I use MySQL 3.22.23 but I just had a hunt about on the web and I found > a posting that confirmed your findings - I.E: auto_increment starts at > 1!! > > I stand corrected! The reasoning behind this a colleague of mine recently informed me of.. When an insert occurs, the new record is given the value of '0'. The auto-increment occurs once the insert is complete. If you use a 0 value in the auto_increment'd field, and you do a table modification (done this once or twice), you'll find you 0'd field jumped up to the next auto_increment value. bkx --MySQL Documentation extract-- An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See section 23.4.30 mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM table but not with a MyISAM table. If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for both table types. NOTE: There can be only one AUTO_INCREMENT column per table, and it must be indexed. MySQL Version 3.23 will also only work properly if the auto_increment column only has positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers 'wrap' over from positive to negative and also to ensure that one doesn't accidently get an auto_increment column that contains 0. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query: SELECT * from tbl_name WHERE auto_col IS NULL --End Documentation--: manual_Reference.html#IDX839 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] auto_increment and INSERT INTO
On Sun, 22 Jan 2006 15:08, Ron Piggott (PHP) wrote:
> I have various tables where a column is set to auto_increment in my
> table structure. I have been using the following INSERT query format:
>
> INSERT INTO table VALUES ('$auto_increment_variable','$variable_1',
> '$variable_2')
>
> and then in applications where I have needed to know the value of the
> $auto_increment_variable I have immediately queried the table for
> $variable_1 and $variable_2 and used
>
> $reference = mysql_result($result,$i,"reference");
>
> to determine what numeric value was assigned.
>
> Is there a way I may find out what value was assigned to the
> $auto_increment_variable when the INSERT INTO query is issued?
Yes. Again, it's in the PHP docs if you look in http://www.php.net/mysql
for "Get the ID generated from the previous INSERT operation"
Really, you should try the docs first instead of falling back on the soft
option of asking the list.
Also, just as a side note, you don't need to provide a value for the
auto-increment field; that's why it is called auto as it does the
incrementing automatically. If your response to that is "I need the
values in a specific order" then you have the wrong idea of what the
auto-increment field is for.
Cheers
--
David Robley
Set laser printers to "stun".
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment and INSERT INTO
That is the function I was looking for. Thanks. Ron
On Sat, 2006-01-21 at 23:47 -0500, [EMAIL PROTECTED] wrote:
> Try:
>
>mysql_query("INSERT INTO table VALUES ('$variable_1', '$variable_2');");
> $auto_increment_variable = mysql_insert_id();
> ?>
>
>
> -TG
>
>
>
>
> = = = Original message = = =
>
> I have various tables where a column is set to auto_increment in my
> table structure. I have been using the following INSERT query format:
>
> INSERT INTO table VALUES ('$auto_increment_variable','$variable_1',
> '$variable_2')
>
> and then in applications where I have needed to know the value of the
> $auto_increment_variable I have immediately queried the table for
> $variable_1 and $variable_2 and used
>
> $reference = mysql_result($result,$i,"reference");
>
> to determine what numeric value was assigned.
>
> Is there a way I may find out what value was assigned to the
> $auto_increment_variable when the INSERT INTO query is issued?
>
> Thanks.
>
> Ron
>
>
> ___
> Sent by ePrompter, the premier email notification software.
> Free download at http://www.ePrompter.com.
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Auto_increment field size (was: Howto make a double LEFT JOIN)
- Original Message - From: "Bas Jobsen" <[EMAIL PROTECTED]> To: "Sheridan Saint-Michel" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, October 05, 2001 9:50 AM Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > > ACK! Don't do this. You do know that an unsigned int (using a signed int > > in an auto_increment field is pointless anyway) has a max value of > > 4294967295, right? To put this into perspective... if you had one million > > customers, they would have more than 4294 transactions EACH before sid > > filled up. If you are still worried about running out of numbers in your > > sid field make it an unsigned bigint. The max value for an unsigned > bigint > > is 18446744073709551615... so that should give you lots of space to play > in, > > Well, oke. I'm builing some pageviewcounter. Maybe count more then > 1 million pageview a day in future :) You're right it will take many days > before i have counted 18446744073709551615 views :). But in some way it feel > like wasting. The first day using the first milion ids, the second day the > next milion and never reuse that numbers. (Yes i realize 8bytes a row is > much better then 32 (or 33 if you do stupid things :))) > > Maybe there is a better implemention? I dont i the first one solving this? This is usually viewed as a non-issue. The reason is you are using 8 bytes whether sid=1 or (2^64-1) (which is the really big number above). In addition (2^64-1) is a REALLY big number. If you had a Billion hits a day, you would not run out of possible sid's for approximately 50.5 million years. Finally, since you are deleting rows after a certain amount of time you can always reset the auto_increment value (I think that is as simlple as "set insert_id=1;" but you may want to double check as this is off the top of my head). > > > and sid will only be 8 bytes per row... not 33 (Also incidentally, if you > do > > want to save an md5 value to a row in the future... use char not varchar > as > > you know it will be 32 bytes and are adding a wasted 33rd byte). > > Oke, i see. > > Best regards, > > Bas Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Auto_increment field size (was: Howto make a double LEFT JOIN)
Hello, > you can always reset the auto_increment value (I think that is as simlple as > "set insert_id=1;" but you may want to double check as this is off the top > of my head). I don't think i can do so cause i never delete all rows. so will get something like: 1 <- new insert her 1000 1001 etc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
