Mysql configure/settings

Question

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

Please add table structure, example data, expected output and where exactly you are stuck. — juergen d, Mar 10 '13 at 11:59
Try with ROW_NUMBER() function instead of id please refer stack:http://stackoverflow.com/questions/1895110/row-number-in-mysql — shola, Mar 10 '13 at 12:34
"Select ROW_NUMBER(),other_column from table order by id " In DB it will still store in 1,3,5 however fetch in order will be 1,2,3 — shola, Mar 10 '13 at 12:49

Hazzit · Answer 1 · 2013-03-10T13:12:35.133

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');
    }

    }
}

$select2 = mysql_query("SELECT new_id from naujiena ORDER BY new_id DESC LIMIT 1"); if (mysql_num_rows($select2) > 0) { $max_public_id = mysql_fetch_row($select2); $query2 = mysql_query("INSERT INTO naujiena (new_id,new_pav) VALUES ('$max_public_id[0]'+1,'$pav2')"); } My code is. — user2149578, Mar 10 '13 at 12:19
@user2149578 That code is more complex than it needs to be. Just change the new_id column to `AUTO_INCREMENT`, and then just insert a `0` - no prior select needed. The database will then automatically pick a new id. — Hazzit, Mar 10 '13 at 12:23
please edit your original question, and add more detail describing your problem. — Hazzit, Mar 10 '13 at 12:32
Hazzit, your idea is good, but actually i need to check if some values exist already in database. If not i assign the id = 1 otherwise i check the last id numbet and add one more (+1). — user2149578, Mar 10 '13 at 13:21
@user2149578 No you don't. The database will do this for you. — Hazzit, Mar 10 '13 at 13:22

Mysql configure/settings

1 Answers1