insert array element into database

Question

I have this function : it's work correctly,

function ms_get_did_detail($id) {
    global $link;
    $q2="select Dest,Priority from destpr where Id='$id'";

    if($res2=mssql_query($q2)) {
        while($row2[]=mssql_fetch_array($res2,MSSQL_ASSOC)) {
            return $row2;
        }

        return 0;
    }

    return 0;
}

I want insert every element (every Dest & Priority) into MYSQL

if($info=ms_get_did_detail($value)) {
    print_r($info);
    $destination = $info['Dest'];
    $priority = $info['Priority'];
    my_did_destination ($priority , $dest , $active , $did_voip , $cc_id);                 
}     

It returns array like this :

[0]=> Array (
    [Dest] => 100
    [Priority] => 1
)
[1]=> Array (
    [Dest] => 200 
    [Priority] => 3 
) 
[2] => (
)

also , I have this function to insert value in database :

function my_did_destination($priority="",$destination="") {
    global $link_voip;
    $sql="INSERT INTO cc_did_destination (destination,priority) 
          VALUES ('$destination','$priority')";

    $retval = mysql_query( $sql , $link_voip);

    if(! $retval ) {
        die('Could not enter data: ' . mysql_error());
    }
}

but It's insert empty value within

Answer 1

There are a couple of issues here.

Firstly your first function returns an array of arrays. Ie, it returns an array with subscript 0 for the first row (it only ever returns one rows details), which is an array containing that rows details.

You assign this to the $info variable, so it contains:-

[0]=> Array (
[Dest] => 100
[Priority] => 1
)

You then assign $info['Dest'] to $destination and $info['Priority'] to $priority. However neither of these exist. You would need $info[0]['Dest'] and $info[0]['Priority'].

2nd issue is that you are trying to assign a specific value to the auto increment id field. Just leave it out of the insert, or give it a value of null.

Quick rewrite and I would suggest you need something like this:-

<?php

if($info=ms_get_did_detail($value))
{
    print_r($info);
    foreach($info AS $info_row)
    {
        $destination = $info_row['Dest'];
        $priority = $info_row['Priority'];
        my_did_destination ($priority , $dest , $active , $did_voip , $cc_id);                 
    }
}     

function ms_get_did_detail($id)
{
    global $link;
    $q2="select Dest,Priority from destpr where Id='$id'";
    if($res2=mssql_query($q2))
    {
        if ($row2[]=mssql_fetch_array($res2,MSSQL_ASSOC))
        {
            while ($row2[]=mssql_fetch_array($res2,MSSQL_ASSOC))
            {
            }
            return $row2;
        }
        else
        {
            return 0;
        }
    }
    return 0;
}

function my_did_destination($priority="",$destination="")
{
    global $link_voip;
    $priority = mysql_real_escape_string($priority);
    $destination = mysql_real_escape_string($destination);
    $sql="INSERT INTO cc_did_destination (id,destination,priority) VALUES (NULL,'$destination','$priority')";

    $retval = mysql_query( $sql , $link_voip);
    if(! $retval )
    {
        die('Could not enter data: ' . mysql_error());
    }
}

EDIT

If you want to avoid multiple inserts unnecessarily then it might be easier to use an object. This way you can do the inserts easily when there are enough batched up (I normally do 255 at a time).

Something like this, although you probably should use mysqli_*

<?php

if($info=ms_get_did_detail($value))
{
    print_r($info);
    $insert_object = new insert_details($link_voip);
    foreach($info AS $info_row)
    {
        $insert_object->set_row($info_row['Priority'], $info_row['Dest']);
    }
    unset($insert_object);
}     

function ms_get_did_detail($id)
{
    global $link;
    $q2="select Dest,Priority from destpr where Id='$id'";
    if($res2=mssql_query($q2))
    {
        if ($row2[]=mssql_fetch_array($res2,MSSQL_ASSOC))
        {
            while ($row2[]=mssql_fetch_array($res2,MSSQL_ASSOC))
            {
            }
            return $row2;
        }
        else
        {
            return 0;
        }
    }
    return 0;
}

class insert_details()
{
    private $db;
    private $insert_row = array();
    public function __CONSTRUCT($db)
    {
        $this->db = $db;
    }
    public function __DESTRUCT()
    {
        $this->do_insert();
    }
    public function set_row($priority="",$destination="")
    {
        $priority = mysql_real_escape_string($priority, $this->db);
        $destination = mysql_real_escape_string($destination, $this->db);
        $this->insert_row[] = "(NULL,'$destination','$priority')";
        if (count($this->insert_row) > 255)
        {
            $this->do_insert();
        }
    }
    private function do_insert()
    {
        $sql="INSERT INTO cc_did_destination (id,destination,priority) VALUES ".implode(',', $this->insert_row);

        $retval = mysql_query($sql, $this->db);
        if(! $retval )
        {
            die('Could not enter data: ' . mysql_error());
        }
        $this->insert_row = array();
    }

}

Quick rough mysqli_* equivalent, assuming that $link_voip is a mysqli connection. Note that prepared statements with bound parameters are an option (and it makes it harder to forget to escape variables), but it can become a bit messy when you are doing multiple inserts like this.

<?php

if($info=ms_get_did_detail($value))
{
    print_r($info);
    $insert_object = new insert_details($link_voip);
    foreach($info AS $info_row)
    {
        $insert_object->set_row($info_row['Priority'], $info_row['Dest']);
    }
    unset($insert_object);
}     

function ms_get_did_detail($id)
{
    global $link;
    $q2="select Dest,Priority from destpr where Id='$id'";
    if($res2=mssql_query($q2))
    {
        if ($row2[]=mssql_fetch_array($res2, MSSQL_ASSOC))
        {
            while ($row2[]=mssql_fetch_array($res2, MSSQL_ASSOC))
            {
            }
            return $row2;
        }
        else
        {
            return 0;
        }
    }
    return 0;
}

class insert_details()
{
    private $db;
    private $insert_row = array();
    public function __CONSTRUCT($db)
    {
        $this->db = $db;
    }
    public function __DESTRUCT()
    {
        $this->do_insert();
    }
    public function set_row($priority="",$destination="")
    {
        $priority = mysqli_real_escape_string($this->db, $priority);
        $destination = mysqli_real_escape_string($this->db, $destination);
        $this->insert_row[] = "(NULL,'$destination','$priority')";
        if (count($this->insert_row) > 255)
        {
            $this->do_insert();
        }
    }
    private function do_insert()
    {
        $sql="INSERT INTO cc_did_destination (id,destination,priority) VALUES ".implode(',', $this->insert_row);

        $retval = mysqli_query($this->db, $sql);
        if(! $retval )
        {
            die('Could not enter data: ' . mysqli_sqlstate($this->db));
        }
        $this->insert_row = array();
    }

}

Answer 2

your array is:

[0]=> Array (
    [Dest] => 100
    [Priority] => 1
)
[1]=> Array (
    [Dest] => 200 
    [Priority] => 3 
) 
[2] => (
)

so it is a multidimensional array. if you need to insert all those entries, you shouldn't run multiple queries for the same thing. just use mysql batch insert syntax. (e.g. INSERT INTO tbl (col1,col2,col3) VALUES(a,b,c),(d,e,f),(g,h,i))

build the query string for insert.

foreach($a as $i => $v)
{
   $b[] = '("'.$v['Dest'].'","'.$v['Priority'].'")';
}

$c = implode(',', $b);

$sql = "INSERT INTO cc_did_destination (destination,priority) 
          VALUES ".$c;

then run the query

N.B.

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Answer 3

Your $info is array of rows, it has numeric keys, not 'Dest'.
You should add index, like $dest = $info[0]['Dest'].

         if($info=ms_get_did_detail($value))
         {
            print_r($info);
            $dest = $info[0]['Dest'];
            $priority = $info[0]['Priority'];
            my_did_destination ($priority , $dest , $active , $did_voip , $cc_id);                 
         }     

Or you can iterate through $info with a loop:

         if($info=ms_get_did_detail($value))
         {
            foreach($info as $row) {
              $dest = $row['Dest'];
              $priority = $row['Priority'];
              my_did_destination ($priority , $dest);                 
            }
         }     

also, remove id from your insert statement

Answer 4

You are inserting all rows with an ID of 0, so, if a row with id=0 already exists, it will fail and will not be inserted.

Maybe the easiest solution would be to make yout ID column autoincrement with an SQL statement like:

    ALTER TABLE cc_did_destination 
  MODIFY COLUMN id INT auto_increment;

And then change your INSERT statement for:

  $sql="INSERT INTO cc_did_destination (destination,priority) 
             VALUES ('$destination','$priority')";