1

I have strange situation where I would have to insert ip address in certain range , Would there be any simple way to do it instead of inserting 100s of ip address manually

example

ip address range - 33.44.0.1 to 33.44.0.254

table stucture

id, IP,        timestamp,  timestamp,  assigned, not_assigned
1   33.44.0.1  2016-07-04  2016-07-04    0            1

Would there be any way to do it?

Uppi
  • 702
  • 2
  • 13
  • 37
  • 2
    You should write a procedure to do this, either in your favorite application language or as a stored procedure in MySQL. – Gordon Linoff Jul 05 '16 at 02:47
  • 1
    I wrote up an answer [here](http://stackoverflow.com/a/32020220) in 3 formats with the help of peers. The formats were string, string with CIDR, and int. – Drew Jul 05 '16 at 03:04
  • It depends what you mean by "manually" - do you mean that literally or mean some other way than one db record per unique IP address? If it's the latter, you have a few choices depending on the consistency of the start and endpoints of the range (i.e., do they always fit into a full netmask range) and can you use a netmask or similar start/end range as the condition to test against later? – ldg Jul 05 '16 at 03:41
  • Another factor is if the IPs in the range needs to be "linked" for later update/delete functions. – ldg Jul 05 '16 at 03:49

2 Answers2

1

Try this (assuming you are using PHP).
The concept is to use a kind of loop to generate numbers between 1 and 254 and append it to the last section of the IP address, then insert the generated IP into database each time.

<?php
   for($n = 1; n < 255; n++) {
     $ip = '33.44.0.'.$n;
     //code to insert into database here
   }
?>
Chuka Charles Okoye
  • 41
  • 1
  • 5
  • This assumes exactly a full netmask(/24) range, in which case I would wonder if simply 33.44.0.1/24 would suffice -- depending on the app's ability to use it. (Obviously trivial to extend for more complex cases but I assume the OP wasn't asking how to loop from 1 to 254 in PHP. Nor would I suggest he create 254(+) insert statements -- if I was to take your example literally.) – ldg Jul 05 '16 at 03:47
1

You can create a procedure to do this:

CREATE PROCEDURE `insert_ips`()
BEGIN

    DECLARE suffix INT DEFAULT 1;

    WHILE suffix < 255 DO
        INSERT INTO table1 VALUES (suffix, CONCAT('33.44.0.', suffix), NOW(), NOW(), 0, 1);
        SET suffix = suffix + 1;
    END WHILE;

END

And the run this procedure by CALL insert_ips().

Blank
  • 12,308
  • 1
  • 14
  • 32