How to add a multidimensional array

[Hugilore111]

how to add a multidimensional array to the database one insert

Array
(
    [0] => Array
        (
            [...] =>...
            [...] =>...
            [...] =>...
        )

    [1] => Array
        (
            [...] =>...
            [...] =>...
            [...] =>...
        )
)

 

[Snog]

$db->query("
   INSERT INTO tablename (field list)
   VALUES(value list),
   (value list),
   (value list)
");

 

[Hugilore111]

$db->query("
   INSERT INTO tablename (field list)
   VALUES(value list),
   (value list),
   (value list)
");

hello Snog thanks for the help
but the dynamic can be a large array
in the loop a lot of queries to the database

   foreach ($var as $key=>$val)
        {
                $this->_getDb()->query('
                INSERT INTO xf_...
                    ('...','...','...','...',)
                VALUES
                (?,?,?,?)
                ON DUPLICATE KEY UPDATE
                    '...', = VALUES('...',),
                    '...', = VALUES('...',),
                    '...', = VALUES('...',),
                    '...', = VALUES('...',),
                  
                  
            ,$val);
        }

 

[Snog]

I think the ON DUPLICATE KEY UPDATE is what is causing the problem. If you're going to do that, there's nothing I can think of that will stop it. Perhaps someone else knows of a better way.

 

[MaGeFH]

Don't query the database in a foreach loop. This is very bad for performance.

Instead build a query string by looping through your array and adding value assignments to the string as you go. Then, when the loop is done (or the chunk, if your data may cause a single query string to grow too large), fire the query once.

 

[Hugilore111]

Don't query the database in a foreach loop. This is very bad for performance.

Instead build a query string by looping through your array and adding value assignments to the string as you go. Then, when the loop is done (or the chunk, if your data may cause a single query string to grow too large), fire the query once.

Hi MaGeFH
can set an example?

 

[Snog]

Wow was I asleep at the wheel yesterday. I didn't even notice you had the query in a foreach loop.

This is a VERY SIMPLISTIC example of what @MaGeFH is talking about and sort of what I thought you would do with your data...

$fieldlist = "user_id,username,visible";
$values = "";

foreach($visitors as $visitor)
{
   if($values) $values .= "," . "\n";
   $values .= "(" . $visitor['user_id'] . ",'" . $visitor['username'] . "',1)";
}

$db->query("
  INSERT INTO tablename ($fieldlist)
  VALUES " . $values
);

The \n is not needed but it makes the data easier to read in the event you need to manually look at $values.

 

[Hugilore111]

Snog,MaGeFH
Many thanks
everything is working. But how to use
ON DUPLICATE KEY UPDATE
so the query works but repeated insertion into the database

$db->query("
  INSERT INTO tablename ($fieldlist)
  VALUES " . $values ."
ON DUPLICATE KEY UPDATE
                    user_id = VALUES(user_id),
                    username = VALUES(username)"

);

 

[Snog]

I could be wrong, but I think it should work exactly the way you have it written.