primary key increase not orderly

Question

i have a list of card names and a php file inserting card. When i inserted one card and deleted it in the database then inserted again. The primary key increase after the card delete. For example:

Card number 1
Card number 2
Card number 3 --> if i delete this value and inserte again the primary key is 4 not 3 how to fix that problem ?

Here is my code

    <?php
// this file show card name and picture
    include("connect.inc");
    $connect=mysqli_connect($host,$username,$password,$dbname) or die ("can't connect to server");
    $query="SELECT * FROM dragon ";
    $result=mysqli_query($connect,$query) or die("can't execute query");
    echo $_SESSION['count'];
    echo "<hr/>";

    while($row=mysqli_fetch_assoc($result))
    {
        extract($row);
        echo $type."<br/>";
        echo $CardName."/";
        echo $Description;
        echo "<br/>";
        echo "<a href='../dragon/{$picture}' border='0'> <img src='../dragon/{$picture}' border='0' width='300' height='300'/></a>";

        echo "<hr/>";

    }


    ?>

this file shows the insert form

<?php
$labels=array("type"=>"type",
                "CardName"=>"Card Name",
                "Description"=>"Description",
                "atk"=>"Attack",
                "def"=>"Defend",
                "picture"=>"picture");

    echo "<form action='InsertCard.php' method='POST'>";
    echo "<h2>Insert new card </h2>";
    foreach($labels as $keys =>$values)
    {
        echo "$values <input type='text' name='$keys'/><br/>";
    }
    echo "<input type='submit'  value='insert new cards'/>";
    echo "<input type='submit' name='return' value='return'/>";
    echo "</form>";

?>

this file handle the inserted file

<?php
$labels=array("type"=>"type",
                "CardName"=>"Card Name",
                "Description"=>"Description",
                "atk"=>"Attack",
                "def"=>"Defend",
                "picture"=>"picture");

if(@isset($_POST['return']))
{
    header("Location:ShowCatalog.php");
}
include("connect.inc");
$connect=mysqli_connect($host,$username,$password,$dbname) or die("can't connect to server");

foreach($_POST as $keys =>$values)
{
    if(empty($values))
    {
        if($keys=='type' or $keys=='CardName' or $keys=='Description' or $keys=='picture')
        {
            $empty_values[]=$keys;
        }
    }
    else
    {
        if($keys=='type')
        {
            if(!preg_match("/^[A-Za-z -]{4,15}$/",$values))
            {
                $invalid_data[]=$keys;
            }
        }
        elseif($keys=='CardName')
        {
            if(!preg_match("/^[A-Za-z -]{4,30}$/",$values))
            {
                $invalid_data[]=$keys;
            }
        }
        elseif($keys=='Description')
        {
            if(!preg_match("/^[A-Za-z., -]{4,255}$/",$values))
            {
                $invalid_data[]=$keys;
            }
        }
        elseif($keys=="atk" or $keys=="def")
        {
            if(!preg_match("/^[0-9]{3,5}$/",$values))
            {
                $invalid_data[]=$keys;
            }
        }
        elseif($keys=='picture')
        {
            if(!preg_match("/^[A-Za-z -]{4,30}(.jpg)$/",$values))
            {
                $invalid_data[]=$keys;
            }
        }
        /*else
        {
            $clean_data[$keys]=trim(strip_tags($values));
        }*/
    }
}

if(@sizeof($empty_values)>0 or @sizeof($invalid_data)>0)
{
    if(@sizeof($empty_values)>0)
    {
    $join=join(", ",$empty_values);
    $msg="You forgot to input: $join<br/>";
    echo $msg;
    }
    if(@sizeof($invalid_data)>0)
    {
    $join=join(", ",$invalid_data);
    $msg="Invalid data: $join";
    echo $msg;
    }
    echo "<form action='$_SERVER[PHP_SELF]' method='POST'>";
    echo "<h2>Insert new card </h2>";
    foreach($labels as $keys =>$values)
    {
        echo "$values <input type='text' name='$keys'/><br/>";
    }
    echo "<input type='submit'  value='insert new cards'/>";
    echo "<input type='submit' name='return' value='return'/>";
    echo "</form>";
    exit();
}

foreach($_POST as $keys =>$values)
{
    $queried_data[$keys]=mysqli_real_escape_string($connect,trim(strip_tags($values)));
}
$check_existence="SELECT CardName FROM dragon WHERE CardName=";
foreach($queried_data as $keys =>$values)
{
    if($keys=="CardName")
    {
        $check_existence.="'".$values."'";
    }
}
$checking_result=mysqli_query($connect,$check_existence)or die("can't execute query ".mysqli_error($connect));
    if(mysqli_affected_rows($connect)>0)
    {
        echo "card is already existed !";
        include("ShowForm.php");
        exit();
    }
    else
    {
        $query="INSERT INTO dragon(";
        foreach($queried_data as $keys =>$values)
        {
            $query.=$keys.",";
        }
        $query.=")";
        $query=preg_replace("/\,\)/",")",$query);
        $query.="VALUES(";
        foreach($queried_data as $keys =>$values)
        {
            if($keys=="type" or $keys=="CardName" or $keys=="Description")
            {
                $values=ucfirst($values);
            }
            if($keys=="atk" or $keys=="def")
            {
                if(empty($values))
                {
                    $values='n/a';
                }
            }
            $query.="'".$values."',";   
        }
        $query.=")";
        $query=preg_replace("/\,\)/",")",$query);
        $result=mysqli_query($connect,$query);
        echo "card is inserted !";
    }
?>

That's how autoincrement keys work... you could disable autoincrement for that field and add keys manually. — Hardy, Dec 29 '13 at 13:21
That is the expected behavior, what is the problem if the next key is 4, not 3? — Lefsler, Dec 29 '13 at 13:22
This shouldn't be something to worry about.... autoincremental keys should be unique, not necessarily consecutive: if your code is dependent on incremental numbering, then you should handle that yourself, and independently of the PK — Mark Baker, Dec 29 '13 at 13:22
Yes. In general, if you're trying to "fix" this "problem" it may be a sign that your design is wrong. We might need to know *why* this is causing problems to know what to suggest you do instead. — Matt Gibson, Dec 29 '13 at 13:24
If you need them to be consecutive, then you should not be using auto-increment. There's a clue in the name. The other side of the coin is why do they need to be consecutive, it adds a huge amount of complexity to the design, so the reason needs to be valid, really valid. — Tony Hopkinson, Dec 29 '13 at 13:28
You should set it to auto increment and increment by 1 in your database — Awlad Liton, Dec 29 '13 at 13:37

score 2 · Answer 1 · answered Dec 29 '13 at 13:28

2

That is expected behaviour, in other words, that's now AI works. Instead of counting on the ID's being sequential, you should keep track of this by yourself in case you need it. When you have done an insert with mysqli, you can fetch the "insert id" after the query has been done, if you need it for reference later.

For more information on getting insert id see: http://www.php.net/manual/en/mysqli.insert-id.php

answered Dec 29 '13 at 13:28

MadsBjaerge

116
5

As I said above, you need to really think before you do something like this, personally I'd go a long way to avoid it. – Tony Hopkinson Dec 29 '13 at 13:29
Well, it seems he needs to be able to correctly point to a inserted row, and how you would do that without using the insert id, and some sort of relation is beyond me. – MadsBjaerge Dec 29 '13 at 13:32
Good, but again, if you need to keep track of the id of the card inserted, i would juse $insert_id.. – MadsBjaerge Dec 29 '13 at 13:36
Correctly point to the inserted row and they aren't consecutive are two different animals, though. Expectation seems to be sorted now. Looked like an exposing a surrogate key problem initially and they always point to a badly flawed design. – Tony Hopkinson Dec 29 '13 at 14:24
Agreed :) I merely pointed out to him that using $insert_id was a possibility as i understood the question as he needed the sequeltial AI for reference. Why i did that, i dont know. – MadsBjaerge Dec 29 '13 at 14:34

score 2 · Answer 2 · answered Dec 29 '13 at 13:36

This is not a problem. When using autoincrement primary keys, the key assigned to your input is the lowest positive value that has not been assigned yet. This is useful in many ways. For example, you have another table with a foreign key. If your design is bad, you may delete a row from this table and remain with an orphan foreign key. This is a easy detectable error, but if when you insert another row, it gets the key of the deleted row, then yor foreign key will point to other data. This error is very hard to detect. So, the problem is not how keys are assigned, but your expectations. You might consider asking what are you trying to achieve, in order to get an useful answer for your work.

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

I see you didn't post any code relating to the delete query, so if you are using a delete in sql manually use the following code.

ALTER TABLE dragon AUTO_INCREMENT = 1

this will reset auto incrementing, and sql will insert the next highest integer.

see https://stackoverflow.com/a/8923132/2401804

edited May 23 '17 at 10:32

Community

1
1

answered Dec 29 '13 at 13:40

r3wt

4,642
2
33
55

1

I would never ever use this method, as it creates a serious performance issues. Everytime you do this, the table will be deleted and recreated again. Im not saying it's not possible, im just saying its a very non-efficient way as there no logical reason for doing this. – MadsBjaerge Dec 29 '13 at 14:37

primary key increase not orderly

3 Answers3