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

I am not sure how your ms_get_did_detail function is working. To me it looks like it will get the first row returned from the SELECT and put that into the first position of the $row2 array then return $row2 (with just that single element, irrespective of how many other rows the select would bring back). With your other function, what is the structure of your table? Is the id field an auto increment field? — Kickstart, Nov 13 '14 at 09:00
Then try to remove the id (and its value) from the insert, or assign it a value of NULL. — Kickstart, Nov 13 '14 at 09:05

score 1 · Answer 1 · answered Nov 13 '14 at 09:00

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')";

So, simply change the `INSERT` statement and let us know if it works. — Oscar Pérez, Nov 13 '14 at 09:14

score 0 · Answer 2 · answered Nov 13 '14 at 09:07

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

Thank you, how it can be implemented with loop? – Farzan Najipour Nov 13 '14 at 09:09 — Farzan Najipour, Nov 13 '14 at 09:09

Kickstart · Answer 3 · 2014-11-17T17:07:03.603

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();
    }

}

score 0 · Answer 4 · edited May 23 '17 at 11:59

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.

Thank you , but what is $a ? – Farzan Najipour Nov 17 '14 at 14:36 — Farzan Najipour, Nov 17 '14 at 14:36

insert array element into database

4 Answers4