CODEWITHSUNDEEP
phpmysql
Jordan
Jordan

Reputation: 39

get last auto increment php

I'm having an issue with my program where it calls questions column(questions) one at a time from my database table starting with id=1, id=2, id=3 and so on. Id is Auto_increment. When I delete a id from the database, my program messes up.

Database

    id     question
    1     Php?
    2     HTML?
    3     CSS?
    4     Java?

Webpage should display as such

#1 Php?
#2 HTML?
#3 CSS?
#4 Java?

The problem happens when I delete or add a question with an id out of order or missing

        id     question
        1     Php?
        2     HTML? <--- 3 is deleted and webpage messes up.
        4     Java?
        5     Oracle
        6     C++

How would I fix this problem where id will always be id=1,2,3,4,5,6...n and make sure when I add a new question at the bottom, the id will be last_id+1 using php and mysql?

I don't think I explained my problem correctly.

I'll see if I can fix one thing at a time.

If I have an id column in my database with id=1,2,3,4,5,6, how would I call the last id which last id=6 in the database if I've already deleted id=7,8? When I insert a new question the question id will be id=9 because of auto increment. I want the new question's id to be id=7. Maybe I can get help to fix this issue first.

Upvotes: 0

Views: 535

Answers (3)

Parag Tyagi
Parag Tyagi

Reputation: 8960

Refer the following example in your case. Here you can declare a variable and increment it in the loop.

$questions = array('PHP?', 'HTML?', 'CSS?', 'JAVA?');

$i = 1;
foreach($questions as $question)
{
    echo "#" . $i . " " . $question. "<br/>";
    $i++;
}

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65264

It is important to differentate between the id as the primary key, and the sequence number of the row.

Try this out:

SELECT
  id, question,
  @seq:=@seq+1 AS seq
FROM
  questions,
  (SELECT @seq:=0) AS init
;

You can now use seq to show the number, always in correct sequence, but make sure, that you associate your actions with the id, not the sequence, so you can reference the rwo by PK.

Upvotes: 1

sgrutman978
sgrutman978

Reputation: 112

You could add a new element at id=3. Or you could just leave the id's alone and have it print out the value and have an integer value that increments every time a value is printed instead of printing out the id.

Upvotes: 0

Related Questions