Re: [PHP] auto-increment not working?? MySQL
* Thus wrote Jay Blanchard: [snip] i've got a strange problem with a MySQL table. although my 'id' column is set to 'auto_increment', each new record i insert has the value 1. (instead of 1, 2, 3, etc.) i checked my sql statement and i'm not assigning the id value by mistake. here is my create statement (showing only a few columns from the table for brevity): CREATE TABLE `customers` ( `id` int(10) unsigned NOT NULL auto_increment, `fname` varchar(20) NOT NULL default '', `lname` varchar(20) NOT NULL default '', `is_active` tinyint(4) NOT NULL default '0', PRIMARY KEY (`fname`,`lname`,`email`,`id`), UNIQUE KEY `email` (`email`) ) TYPE=MyISAM; anyone know what's going on?? [/snip] The guys on the MySQL list do. Have you read this-- http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html more like 'db 101' and 'Intro to Mailing lists', would be a more appropriate thing to do. Curt -- First, let me assure you that this is not one of those shady pyramid schemes you've been hearing about. No, sir. Our model is the trapezoid! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] auto-increment not working?? MySQL
[snip] i've got a strange problem with a MySQL table. although my 'id' column is set to 'auto_increment', each new record i insert has the value 1. (instead of 1, 2, 3, etc.) i checked my sql statement and i'm not assigning the id value by mistake. here is my create statement (showing only a few columns from the table for brevity): CREATE TABLE `customers` ( `id` int(10) unsigned NOT NULL auto_increment, `fname` varchar(20) NOT NULL default '', `lname` varchar(20) NOT NULL default '', `is_active` tinyint(4) NOT NULL default '0', PRIMARY KEY (`fname`,`lname`,`email`,`id`), UNIQUE KEY `email` (`email`) ) TYPE=MyISAM; anyone know what's going on?? [/snip] The guys on the MySQL list do. Have you read this-- http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] auto-increment not working?? MySQL
Your primary key should be only (`id`). On Mon, 12 Jul 2004 13:04:19 -0700, barophobia [EMAIL PROTECTED] wrote: hello. i've got a strange problem with a MySQL table. although my 'id' column is set to 'auto_increment', each new record i insert has the value 1. (instead of 1, 2, 3, etc.) i checked my sql statement and i'm not assigning the id value by mistake. here is my create statement (showing only a few columns from the table for brevity): CREATE TABLE `customers` ( `id` int(10) unsigned NOT NULL auto_increment, `fname` varchar(20) NOT NULL default '', `lname` varchar(20) NOT NULL default '', `is_active` tinyint(4) NOT NULL default '0', PRIMARY KEY (`fname`,`lname`,`email`,`id`), UNIQUE KEY `email` (`email`) ) TYPE=MyISAM; anyone know what's going on?? chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php !DSPAM:40f2f0f6322131004619089! -- DB_DataObject_FormBuilder - The database at your fingertips http://pear.php.net/package/DB_DataObject_FormBuilder paperCrane --Justin Patrin-- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] auto-increment not working?? MySQL
On Mon, 12 Jul 2004 15:36:20 -0500, Jay Blanchard [EMAIL PROTECTED] wrote: The guys on the MySQL list do. Have you read this-- http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html not for this issue no. the problem for those that read this later is not that i am using multiple primary keys but rather the order in which they are defined. 'id' should be first in the list. once i moved 'id' to the first spot the table began incrementing as per expected behavior. thanks jay. chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] auto increment
--- marc serra [EMAIL PROTECTED] wrote: i use PEAR DB with a mysql database and i want to know if there is a solution to get last inserted id in a table. I see that I can use sequences but it doesn't work with mysql. There is this: http://www.php.net/mysql_insert_id Hope that helps. Chris = Chris Shiflett - http://shiflett.org/ PHP Security - O'Reilly Coming mid-2004 HTTP Developer's Handbook - Sams http://httphandbook.org/ PHP Community Site http://phpcommunity.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] auto increment
execute the following query: SELECT LAST_INSERT_ID(); Peter -Original Message- From: marc serra [mailto:[EMAIL PROTECTED] Sent: dinsdag 17 februari 2004 20:17 To: [EMAIL PROTECTED] Subject: [PHP] auto increment Hi, i use PEAR DB with a mysql database and i want to know if there is a solution to get last inserted id in a table. I see that I can use sequences but it doesn't work with mysql. Thanks, Marc. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] auto increment
marc serra wrote: Hi, i use PEAR DB with a mysql database and i want to know if there is a solution to get last inserted id in a table. If the id is auto increment, can't you use max(id) to get the last inserted one? Best Bao I see that I can use sequences but it doesn't work with mysql. Thanks, Marc. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] auto increment
BAO RuiXian mailto:[EMAIL PROTECTED] on Tuesday, February 17, 2004 11:32 AM said: If the id is auto increment, can't you use max(id) to get the last inserted one? only if you are the absolute only person that will ever be connected to that db at one time. otherwise the answer is no. what if in between your INSERT INTO statement and your SELECT statement someone else writes a record? for a high traffic website it's very possible that someone will insert another record before you can turn around and read the table. does that help? chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] auto increment
--- Chris W. Parker [EMAIL PROTECTED] wrote: If the id is auto increment, can't you use max(id) to get the last inserted one? only if you are the absolute only person that will ever be connected to that db at one time. otherwise the answer is no. what if in between your INSERT INTO statement and your SELECT statement someone else writes a record? for a high traffic website it's very possible that someone will insert another record before you can turn around and read the table. This is a very good explanation. Also, before you start thinking that these types of synchronization problems don't apply to you, because you're on a dedicated box, consider that Apache (or whatever Web server you are using) can potentially have many child processes serving all of your users. Thus, there may be many connections to the database, and all of them may be inserting into this table. The function to retrieve the last ID is for a single connection, therefore these types of synchronization issues aren't a problem. Hope that helps. Chris = Chris Shiflett - http://shiflett.org/ PHP Security - O'Reilly Coming mid-2004 HTTP Developer's Handbook - Sams http://httphandbook.org/ PHP Community Site http://phpcommunity.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Hi there. You can use the function mysql_insert_id( [link id] ). regards Scott Faisal Abdullah wrote: Hi people, I have a table with a column called ID (auto-increment). Is it possible to know the value of ID, right after inserting a row? I'm using postgresql. Thanks. Sincerely, Faisal __ -- //// // Scott Houseman // // Jam Warehouse http://www.jamwarehouse.com/ // // Smart Business Innovation // // +27 21 4477440 / +27 82 4918021// //// -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Scott Houseman wrote: Hi there. You can use the function mysql_insert_id( [link id] ). I'm using postgresql. Not if you're using PostGreSQL Grtz Erwin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()* Scott Houseman wrote: Hi there. You can use the function mysql_insert_id( [link id] ). regards Scott Faisal Abdullah wrote: Hi people, I have a table with a column called ID (auto-increment). Is it possible to know the value of ID, right after inserting a row? I'm using postgresql. Thanks. Sincerely, Faisal __ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
not on postgreSQL :) Justin on 18/09/02 7:24 PM, Scott Houseman ([EMAIL PROTECTED]) wrote: Hi there. You can use the function mysql_insert_id( [link id] ). regards Scott Faisal Abdullah wrote: Hi people, I have a table with a column called ID (auto-increment). Is it possible to know the value of ID, right after inserting a row? I'm using postgresql. Thanks. Sincerely, Faisal __ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = success;
echo oid : .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 5:42 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()*
Scott Houseman wrote:
Hi there.
You can use the function mysql_insert_id( [link id] ).
regards
Scott
Faisal Abdullah wrote:
Hi people,
I have a table with a column called ID (auto-increment).
Is it possible to know the value of ID, right after inserting a row?
I'm using postgresql.
Thanks.
Sincerely,
Faisal
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
Would a select last_value from sequence_name do any good?
Is there a possibility that I get a value from the outcome of
a nextval by another session?
Meaning, I do an insert 1, 2, 3, 4
A 'select last_value from sequence_name' should give me '4'.
But what if another user/session does another insert/nextval(),
before my select statement finishes processing? Would I get
a 5?
Sincerely,
Faisal
-Original Message-
From: Faisal Abdullah [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 6:37 PM
To: PHP
Subject: RE: [PHP] Auto-increment value
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = success;
echo oid : .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 5:42 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()*
Scott Houseman wrote:
Hi there.
You can use the function mysql_insert_id( [link id] ).
regards
Scott
Faisal Abdullah wrote:
Hi people,
I have a table with a column called ID (auto-increment).
Is it possible to know the value of ID, right after inserting a row?
I'm using postgresql.
Thanks.
Sincerely,
Faisal
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
MySQL has |LAST_INSERT_ID() function, try it (SELECT ||LAST_INSERT_ID())|
Faisal Abdullah wrote:
Would a select last_value from sequence_name do any good?
Is there a possibility that I get a value from the outcome of
a nextval by another session?
Meaning, I do an insert 1, 2, 3, 4
A 'select last_value from sequence_name' should give me '4'.
But what if another user/session does another insert/nextval(),
before my select statement finishes processing? Would I get
a 5?
Sincerely,
Faisal
-Original Message-
From: Faisal Abdullah [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 6:37 PM
To: PHP
Subject: RE: [PHP] Auto-increment value
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = success;
echo oid : .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 5:42 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()*
Scott Houseman wrote:
Hi there.
You can use the function mysql_insert_id( [link id] ).
regards
Scott
Faisal Abdullah wrote:
Hi people,
I have a table with a column called ID (auto-increment).
Is it possible to know the value of ID, right after inserting a row?
I'm using postgresql.
Thanks.
Sincerely,
Faisal
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
Thanks for your reply,
but I'm using postgresql.
Mysql is irrelevant for me.
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 7:41 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
MySQL has |LAST_INSERT_ID() function, try it (SELECT ||LAST_INSERT_ID())|
Faisal Abdullah wrote:
Would a select last_value from sequence_name do any good?
Is there a possibility that I get a value from the outcome of
a nextval by another session?
Meaning, I do an insert 1, 2, 3, 4
A 'select last_value from sequence_name' should give me '4'.
But what if another user/session does another insert/nextval(),
before my select statement finishes processing? Would I get
a 5?
Sincerely,
Faisal
-Original Message-
From: Faisal Abdullah [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 6:37 PM
To: PHP
Subject: RE: [PHP] Auto-increment value
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = success;
echo oid : .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 5:42 PM
To: PHP
Subject: Re: [PHP] Auto-increment value
As he uses postgresql, he should use *pg_last_oid()*
Scott Houseman wrote:
Hi there.
You can use the function mysql_insert_id( [link id] ).
regards
Scott
Faisal Abdullah wrote:
Hi people,
I have a table with a column called ID (auto-increment).
Is it possible to know the value of ID, right after inserting a row?
I'm using postgresql.
Thanks.
Sincerely,
Faisal
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Dear Faisal Abdullah, Once you wrote about RE: [PHP] Auto-increment value: Thanks for your reply, but I'm using postgresql. Mysql is irrelevant for me. There is a similar one for postgresql as well: pg_last_oid() . -Original Message- From: Marek Kilimajer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 7:41 PM To: PHP Subject: Re: [PHP] Auto-increment value MySQL has |LAST_INSERT_ID() function, try it (SELECT ||LAST_INSERT_ID())| -- Best regards, Leonid Mamtchenkov, RHCE System Administrator Francoudi Stephanou Ltd. BOFH: HTTPD Error 666 : BOFH was here -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
There is a similar one for postgresql as well: pg_last_oid() .
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = success;
echo oid : .pg_last_oid($result);
}
-- snip snip --
Sincerely,
Faisal
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
SELECT idno ORDER BY idno DESC LIMIT 1; assuming idno is what gets auto incremented bob -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
You encounter a race condition bob parker wrote: SELECT idno ORDER BY idno DESC LIMIT 1; assuming idno is what gets auto incremented bob -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
Hi Bob, SELECT idno ORDER BY idno DESC LIMIT 1; assuming idno is what gets auto incremented That's not the best idea - what happens if two users are inserting records into the table at nearly-but-not-quite the same time? 1. Insert A goes through 2. Insert B goes through 3. LastID A returns the ID of B 4. LastID B returns the ID of B Whoever did insert A would receive the wrong ID. Cheers Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
If there is a possibilty of that then BEGIN INSERT ... whatever SELECT idno ORDER BY (idno) DESC LIMIT 1; END; It works, I just tried it in postgresql, the last idno is visible to the user making the transaction, and will be unavailable to others. From [EMAIL PROTECTED] Wed Sep 18 22:34:13 2002 Envelope-to: bob@localhost Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm Precedence: bulk list-help: mailto:[EMAIL PROTECTED] list-unsubscribe: mailto:[EMAIL PROTECTED] list-post: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] From: Jon Haworth [EMAIL PROTECTED] To: 'bob parker' [EMAIL PROTECTED], [EMAIL PROTECTED] Date: Wed, 18 Sep 2002 13:27:56 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset=ISO-8859-1 Subject: RE: [PHP] Auto-increment value X-UIDL: *ab!\-5!!-$7!!nk~!! Hi Bob, SELECT idno ORDER BY idno DESC LIMIT 1; assuming idno is what gets auto incremented That's not the best idea - what happens if two users are inserting records into the table at nearly-but-not-quite the same time? 1. Insert A goes through 2. Insert B goes through 3. LastID A returns the ID of B 4. LastID B returns the ID of B Whoever did insert A would receive the wrong ID. Cheers Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php bob -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
I tend to (if I can't use mysql_last_id) insert a unique key into the row, and pull the row out that matches that key, in order to get the ID... ensures that it was the right row... then you can delete the unique key. Justin on 18/09/02 10:27 PM, Jon Haworth ([EMAIL PROTECTED]) wrote: Hi Bob, SELECT idno ORDER BY idno DESC LIMIT 1; assuming idno is what gets auto incremented That's not the best idea - what happens if two users are inserting records into the table at nearly-but-not-quite the same time? 1. Insert A goes through 2. Insert B goes through 3. LastID A returns the ID of B 4. LastID B returns the ID of B Whoever did insert A would receive the wrong ID. Cheers Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
From: Marek Kilimajer [EMAIL PROTECTED] To: PHP [EMAIL PROTECTED] Subject: Re: [PHP] Auto-increment value You encounter a race condition bob parker wrote: SELECT idno ORDER BY idno DESC LIMIT 1; assuming idno is what gets auto incremented bob To avoid the race it is necessary to do the whole thing in a transaction, for postgresql this works BEGIN; INSERT ... whatever SELECT ... the last idno END; The last idno is available to the user inside the transaction. I beleive the terminating ';' are not needed in php generated sql statements but they don't hurt either. bob -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Auto-increment value
Dear Faisal Abdullah,
Once you wrote about RE: [PHP] Auto-increment value:
There is a similar one for postgresql as well: pg_last_oid() .
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
I'd bet that 24807 is your oid. Check the difference between
SELECT * FROM table; and SELECT oid,* FROM table;.
HTH.
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = success;
echo oid : .pg_last_oid($result);
}
-- snip snip --
--
Best regards,
Leonid Mamtchenkov, RHCE
System Administrator
Francoudi Stephanou Ltd.
BOFH: somebody was calculating pi on the server
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto-increment value
Hello people,
Thanks for all your help. I guess I'll go with
$oid = pg_last_oid($result);
select id from table where oid = $oid;
If i'm not mistaken, there will no race issue here:
pg_last_oid($result) has already returned me the row
that 'I' have just inserted, based on $result, regardless whether there is
another
user doing an insert. I already have a reference to a row i just inserted.
On the other hand,
BEGIN
INSERT ... whatever
SELECT idno ORDER BY (idno) DESC LIMIT 1;
END;
will have some 'time' between the 'insert' and the 'select'.
There is no reference to a row based on $result.
There would still probably be a race issue here (just my opinion,
not a fact). But if there's anyone could explain the internals,
I'd be glad.
Thanks for all who helped.
Sincerely,
Faisal
-Original Message-
From: Leonid Mamtchenkov [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 5:35 AM
To: Faisal Abdullah
Cc: Marek Kilimajer; PHP
Subject: Re: [PHP] Auto-increment value
Dear Faisal Abdullah,
Once you wrote about RE: [PHP] Auto-increment value:
There is a similar one for postgresql as well: pg_last_oid() .
I tried that. It gives me 24807, instead of 5.
Or is oid is a reference to something else, which would
lead me to the '5' i'm looking for?
I'd bet that 24807 is your oid. Check the difference between
SELECT * FROM table; and SELECT oid,* FROM table;.
HTH.
-- snip snip --
if($result = pg_exec($db, $sql))
{
$query = success;
echo oid : .pg_last_oid($result);
}
-- snip snip --
--
Best regards,
Leonid Mamtchenkov, RHCE
System Administrator
Francoudi Stephanou Ltd.
BOFH: somebody was calculating pi on the server
__
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Auto Increment Problems....
-Original Message- From: Georgie Casey [mailto:[EMAIL PROTECTED]] Apart from this looking ugly, it poses another problem. In my PHP script where I can add new rows, I query the table, checking how many rows in the table altogether and set the new id as the next number, but this doesnt work if theres 'holes' in the id field, as the new record tries to overwrite another id. The entire purpose of auto_increment is that you do NOT have to 'set' id when you add new records to the field. Don't reference your 'id' field in the insert statement at all. So I've 2 questions 1) Can the next auto_increment value be 'set' by a SQL query No. The next auto_increment value is the same as max(id) + 1. However, just because a column is auto_increment doesn't mean you can't specify an id when you add a new record, if you like. 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID column?? Not with SQL by itself. You'd need to SELECT id FROM table, then use PHP to figure out how to fill in the hole. However, none of this should be a problem; simply allow those holes to exist and find some other way of numbering your records. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] auto increment
On Vie 07 Sep 2001 19:01, [EMAIL PROTECTED] wrote: you know how, if you make a field a primary key and have it auto increment... then if you delete an entry and add a new one it makes the field 2 instead of 1... i know thats explained bad but do you know what i mean? is there a way to make it go back to 1 as if there were never any fields entered? Man, this is a PHP list, not an SQL list. Any way, you never said what Database server you are using, and the auto incrementals work differently on Oracle, PostGreSQL, MySQL or Informix. Saludos... :-) -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - -- PHP General 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]
