insert values between rows

Question

I don't know if it can be done with just a sql query or it needs a php code when a cid is missing

There exist many missing values which I can't handle manually

For example, here I don't have cid=1 and cid=6. I want to insert a row:

cid=1 tcp_sport='undefined' tcp_dport='undefined'

and

cid=6 tcp_sport='undefined' tcp_dport='undefined'

It seems to me I should create a procedure and insert between lines

another solution that I thaught was that I will create a table with cid and undifined values with the respective order and then join this one with that one and this join should have for example ifnull(tcp_sport,'')

would you please help me?

Answer 1

First, use MAX for get the largest ID.

SELECT MAX(cid) as max FROM table

Then, create a for loop for checking if the individual IDs exist:

for ($i = 0; $i < $max; $i++) {
    // $query = ... SELECT 1 FROM table WHERE cid = $i ...
    // check if the number of rows for $query is greater than 0
    // if not, INSERT INTO table VALUES ($i, DEFAULT, DEFAULT)
}

Answer 2

The whole idea of an auto increment ID is to have a value that only refers to one thing ever. By "inserting between the lines" you may be opening yourself up to a lot of unforeseen problems. Image you have another table that has some values that link to the CID of this table. What if that table already has an entry for CID=1, When you insert a new item with CID=1 it will then join to that supporting record. So Data that really belongs to the original item with CID=1 will show for the new item which it probably has nothing to do with.

You aren't going to run out of ID values (if you are approaching the limit of integer, switch it to bigInt), don't re-use IDs if you can avoid it.