1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to add a multidimensional array

Discussion in 'XenForo Development Discussions' started by Hugilore111, May 17, 2016.

  1. Hugilore111

    Hugilore111 Member

    how to add a multidimensional array to the database one insert
    Code:
    Array
    (
        [0] => Array
            (
                [...] =>...
                [...] =>...
                [...] =>...
            )
    
        [1] => Array
            (
                [...] =>...
                [...] =>...
                [...] =>...
            )
    )
     
  2. Snog

    Snog Well-Known Member

    Code:
    $db->query("
       INSERT INTO tablename (field list)
       VALUES(value list),
       (value list),
       (value list)
    ");
    
     
    Hugilore111 likes this.
  3. Hugilore111

    Hugilore111 Member

    hello Snog thanks for the help
    but the dynamic can be a large array
    in the loop a lot of queries to the database
    Code:
       foreach ($var as $key=>$val)
            {
                    $this->_getDb()->query('
                    INSERT INTO xf_...
                        ('...','...','...','...',)
                    VALUES
                    (?,?,?,?)
                    ON DUPLICATE KEY UPDATE
                        '...', = VALUES('...',),
                        '...', = VALUES('...',),
                        '...', = VALUES('...',),
                        '...', = VALUES('...',),
                      
                      
                ,$val);
            }
     
  4. Snog

    Snog Well-Known Member

    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.
     
    Hugilore111 likes this.
  5. MaGeFH

    MaGeFH Member

    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 and Snog like this.
  6. Hugilore111

    Hugilore111 Member

    Hi MaGeFH
    can set an example?
     
  7. Snog

    Snog Well-Known Member

    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...
    Code:
    $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.
     
    Last edited: May 18, 2016
    Hugilore111 likes this.
  8. Hugilore111

    Hugilore111 Member

    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
    Code:
    $db->query("
      INSERT INTO tablename ($fieldlist)
      VALUES " . $values ."
    ON DUPLICATE KEY UPDATE
                        user_id = VALUES(user_id),
                        username = VALUES(username)"
    
    );

     
  9. Snog

    Snog Well-Known Member

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

Share This Page