PHP how to parse Array data result into mysql insert table-CodePudding

i am developing a small project on php this product uses the freeradius mysql database. i am strugling to input a result data array into a mysql insert table specific..
this table as the following structure radippool table , it has the following rows in it. pool_name, framedipaddress i can input the data manually with the mysql insert function

    $ipblocks = (getEachIpInRange ( $cidr));
    $sql = "INSERT INTO radippool (pool_name, framedipaddress, calledstationid, callingstationid, username, pool_key) VALUES ('pool-teste', '$ipblocks', '', '', '', '0')";

basically the function calculates a block of IP based on the CIDR for example 192.168.0.1/30 and generates the following ips.. and i get the array result in this format below

    Array ( [0] => 100.64.0.1 [1] => 100.64.0.2 [2] => 100.64.0.3 [3] => 100.64.0.4 [4] => 100.64.0.5 [5] => 100.64.0.6 ) 1

i am trying to copy each single IP into a different row on the radippool table.. row 1 id1 100.64.0.1 row 2 id2 100.64.0.2 row 3 id3 100.64.0.3 row 4 id4 100.64.0.4

but using the following code he tries to input in the framedipaddress row, all the 4 ips at once.. and throws an error out..

i found a way of inputing it on different rows.. but its not the most praticall way which was

    $sql = "INSERT INTO radippool (pool_name, framedipaddress, calledstationid, callingstationid, username, pool_key) VALUES ('pool-teste', '$ipblocks[0]', '', '', '', '0'), ('pool-teste', '$ipblocks[1]', '', '', '', '0'), ('pool-teste', '$ipblocks[2]', '', '', '', '0'), ('pool-teste', '$ipblocks[3]', '', '', '', '0')";

but if i need to calculate a 192.168.0.1/22 which throws out 1024 ips.. it will be a nightmare of sql insert to input 1024 , i am shure there must be a way of doing this properly ..

basically i just need the end result ips.. to be input each single ip generated from the CIDR in a different row with each individual IP adress stored..

any helps will be appreciated.

CodePudding user response：

You can to achieve this using prepared statement and loop through array:

<?php
// source array
$ips = ['100.64.0.1', '100.64.0.2', '100.64.0.3', '100.64.0.4', '100.64.0.5', '100.64.0.6'];

// SQL query
$sql = "INSERT INTO radippool (
        pool_name, framedipaddress, calledstationid, callingstationid, username, pool_key
    ) VALUES (
        'pool-teste', ?, '', '', '', '0'
    )";

// prepared statement from query
$stmt = $pdo->prepare($sql);

// use loop for execute prepared statement for each element of array
foreach($ips as $ip) {
    $stmt->execute([$ip]);
}

Here online test PHP prepared statement

CodePudding user response：

Hi guys i have solved it in the end, with a foreach, here is full testing code, sure will be better off with a prepared statement PDO which i will do next for testing purposes, also, but basically it generates a block os ips based on the CIDR input, and it will generate all the ips, and insert it to the freeradius db on radippool table "framedipaddress" row.

function getIpRange(  $cidr) {
list($ip, $mask) = explode('/', $cidr);

$maskBinStr =str_repeat("1", $mask ) . str_repeat("0", 32-$mask );      //net mask string binaria
$inverseMaskBinStr = str_repeat("0", $mask ) . str_repeat("1",  32-$mask ); //mascara invertida

$ipLong = ip2long( $ip );
$ipMaskLong = bindec( $maskBinStr );
$inverseIpMaskLong = bindec( $inverseMaskBinStr );
$netWork = $ipLong & $ipMaskLong; 

$start = $netWork 1;//ignore network id (eg: 192.168.1.0)

$end = ($netWork | $inverseIpMaskLong) -1 ; //Ignore IP broadcast example (eg: 192.168.1.255)
return array('firstIP' => $start, 'lastIP' => $end );
}    
function getEachIpInRange ( $cidr) {
$ips = array();
$range = getIpRange($cidr);
for ($ip = $range['firstIP']; $ip <= $range['lastIP']; $ip  ) {
    $ips[] = long2ip($ip);
}
return $ips;
}

 $cidr = '100.64.0.0/22'; 

 $ipblocks = (getEachIpInRange ( $cidr));

 foreach ($ipblocks as $id => $ips) {

 // attempt to insert ips in framedipaddress table
 $sql = "INSERT INTO radippool (pool_name, framedipaddress, calledstationid, 
  callingstationid, username, pool_key) VALUES ('pool-teste', '$ips', '', '', '', '0')";
   

 if(mysqli_query($mysqli, $sql)){

    $redirectUrl = '../list_pool.php';

      echo '<script type="application/javascript">alert("Pool registered with success!!!!!"); window.location.href = "'.$redirectUrl.'";</script>';
header( "refresh:5; url=../checkpool.php" ); 

                            } else{
                                    echo "ERRO: Not possible to execute $sql. " . mysqli_error($mysqli);
                                  }
                              }

// Close connection
mysqli_close($mysqli);