auto_increment problem

Anon

Hi folks
I have a form that inputs data into a MySQL table. This table has an AUTO_INCREMENT field.

In one of the columns I'd like to add the value of the AUTO_INCREMENT field+1.

Problem is, getting the AUTO_INC value?

Thanks a lot

Nick

kparker

I'm not sure how you might do this, but it seems highly doubtful that you really need to do it in the first place. I mean, if you have two columns whose values always differ by the constant '1', you don't really need both of them.

If you need the retrieved value to be the higher amount, then just say

select id+1 as id_plus_one from table;

Anon

Hi
The reason I need to do this is to link (possibly) a record to the previous record as in a series of articles that may have more than one page per article.(more than one record per article)

ID PREDECESSOR TITLE ...
1 null This is the first article
2 1 This is the second article
3 2 This is the final article
4 null Beginning article of another series

Ta very much

Nick

kparker

Well, you don't dare do it that way unless you have some way of guaranteeing that two users will never be adding articles at the same time.

But still, it's not necessary. If you have N articles of a series, with all the titles stored in an array, and you want to insert them all at once, do this:

$prev_id = 0;
for ($i = 0; $i < N; $i++ )
  {
  $qry = "insert into article ( predecessor, title) values ($prev_id, '$title[$i]' )";
  mysql_query( $qry, ... ) // insert the article
  $prev_id = mysql_insert_id(...);
  }

The first row inserted will have 0 for a predecessor ID, and the rest will have the correct ID from the previous insertion, independent of how many other inserts some other connection may have made into the same table at the same time.

knickerlas

Hi again
Your solution looks great Kirk but how will I know when there is more than one page in an article and then link those pages together?

Thanks again

Nick

kelkar_mandar

hi
i think you just use ur insert query first with a null argument to ur other column -- leme explain

table data --
fields id auto_incr
id1=id+1 u want this always
name vc and bla bla fields

ok so first u use ur insert query saying
$sql=insert into data (id ,name) values(0,'firstarticle');
then
$result=mysql_query($sql); -- followed by
$ins_id=mysql_insert_id($result);

this will give u the id of last insert
now all u need is to increment this id by 1
use update -- ex

$sql1=update data set id1=ins_id+1 where id=ins_id;
mysql_query($sql1);

i think this should work ---- 🙂

please leme know the feedback so i can improve

cheers
mandar@freeos.com

kparker

After you do your main query to get the content for the page (at which time you will know the article ID) just issue another query:

select title, id from articles where predecessor = $current_article_id;

Now can display a link that shows the title of the next article, and you know its ID.

knickerlas

I think that may be a light I see at the end of the tunnel!

knickerlas

Not sure I followed all of that but It certainly provided food for thought!

Nick