CODEWITHSUNDEEP
mysqlkeysettingsconfigure
user2149578
user2149578

Reputation: 79

Mysql configure/settings

What settings i need to choose to maintain an orderly queue when delete some value. I create the table with id with Auto increment and primary key. When i insert values first time it is okay. Like 1,2,3,4,5... when delete some value the order changes like 1,5,3.... And script doesn't work well.

Some code:

if(isset($submit)){

    if($pav2 == ""){
        header('Location: index.php');  
    }

    else {

    $select = mysql_query("SELECT new_id from naujiena ORDER by new_id");
    $zym = mysql_num_rows($select);

    if($zym == 0) {
    $query = mysql_query("INSERT INTO naujiena (new_id,new_pav) VALUES ('1','$pav2')");
    header('Location: index.php');
    }

    else {
         $select2 = mysql_query("SELECT new_id from naujiena ORDER BY new_id DESC LIMIT 1");
         $max_public_id = mysql_fetch_row($select2);
         $query2 = mysql_query("INSERT INTO naujiena (new_id,new_pav) VALUES ('$max_public_id[0]'+1,'$pav2')");

         header('Location: index.php');
    }

    }
}

When new_pav and new_id have just added: Example:

new_id new_pav
5      some_value
4      some_value
3      some_value
2      some_value
1      some_value

When i delete something it becomes: For example:

new_id new_pav
4      some_value
2      some_value
3      some_value
1      some_value

Upvotes: 0

Views: 80

Answers (1)

Hazzit
Hazzit

Reputation: 6882

What you're concerned with here is a common misconception about what a table in a database is.

When you start off with a blank table, and add a few rows, the primary key will give you a sequence of ascending unique values. Doing a simple SELECT * FROM MyTable will give you this:

MyTable
ID  other columns
1   row1
2   row2
3   row3
4   row4
5   row5

Nice and pretty, huh? Now when you then delete rows from that table, e.g. row 2 and 4, you would expect the same select to give you:

MyTable
ID  other columns
1   row1
3   row3
5   row5

Unfortunately, that is not how it works, you might be getting this instead:

MyTable
ID  other columns
1   row1
5   row5
3   row3

Not so pretty. The reason is simple: When you run a SELECT * FROM MyTable query, the order in which the rows are given back by the query is undefined. The database may give tham in any order it wants, unless you specify a certain order.

If you actually want the rows to be in a certain consistent order, you need say so.

SELECT * FROM MyTable ORDER BY id will give you what you're looking for.

Now why did it work in the first place? Why were the rows in perfect order before you deleted things? It just happens to be the most efficient order the database could retrieve that data from a fresh table - nothing more, nothing less. If the database had any more efficient way to do this, it would have started off with a different order in the first place.

Edit: As it turns out since your edit, you're problem differs from what I initialally thought. You seem to be concerned that when you delete the row with the highest id, a new row will then get the same id. That is because you made the whole thing more complex than it needed to be.

Step 1: Change your database

Set your new_id column to be INT AUTO_INCREMENT PRIMARY KEY as shown in this SQL Fiddle

Step 2: change your code to (also fixes the SQL injection issue with your original code)

if(isset($submit)){

    if($pav2 == ""){
        header('Location: index.php');  
    }
    else {
         $query2 = mysql_query("INSERT INTO naujiena (new_id,new_pav) VALUES (0,'". mysql_real_escape_string($pav2)."')");

         header('Location: index.php');
    }

    }
}

Upvotes: 1

Related Questions