Joomla 3 loop through multidimensional array for db insert

Question

this is my first question here so please be nice :)

I'm trying to insert multiple records, but I keep getting a mysql error, #1136 Column count doesn't match value count at row 1. I've followed the guide here: http://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase

Part of my issue is that the columns won't necessarily always be the same in the array. For example, 'address1' is required, but 'address2' and 'address3' are not. All available fields exist in the db table though, so I just need to specify the columns as keys for each value set.

I feel like I'm missing something quite obvious, but having spent far too many hours at this, I hope someone will be able to easily point it out.

Here's my error:
1136 Column count doesn't match value count at row 1 SQL=INSERT INTO jos_supersite_contact

Below is the code I'm using....

        foreach ($data[0]['result'] as $results) {
            $db = JFactory::getDbo();
            $db->getQuery(true);
            foreach ($results as $key => $value) {
                $columns[] = $db->quoteName(str_replace('.', '_', $key));
                $rows[] = $db->quote($value);
            }
            $query
                    ->columns($columns)
                    ->values(implode(',',$rows))
                    ->insert($db->quoteName('#__supersite_contact'));
            $db->setQuery($query);
            if (!$db->execute()) {
                throw new Exception($db->getErrorMsg());
            }
        }

score 0 · Accepted Answer · edited May 23 '17 at 12:05

You error message itself explain the issue.

The column filed and values fields are not matching in number.

you already mentioned that address1 is required and address2,3 not so in your query the column have 3 fields every time but value have only address1 when 2 and 3 empty. So make sure all time you have these three fields even those have null value

Also I noticed the Query you are using inside the loop it give more load to the DB. Instead of using like that use like below.

       $db = JFactory::getDbo();
       $columns = "column1,column2,column3";
       //This is what you need to loop.
       $vals    = "('$db->quote($val1)','$db->quote($val2)','$db->quote($val3)')
                  ,('$db->quote($val1)','$db->quote($val2)','$db->quote($val3)')
                  ,('$db->quote($val1)','$db->quote($val2)','$db->quote($val3)')"; 
       $query
               ->columns($columns)
               ->values($vals)
               ->insert($db->quoteName('#__supersite_contact'));
        $db->setQuery($query);
        if (!$db->execute()) {
            throw new Exception($db->getErrorMsg());
        }

This is much effective in multiple row insert situations.

Hope it make sense..

I'm getting a 500 server error trying it this way, so I'll need to figure out what's going on with that... will post back shortly. — James, Jul 10 '14 at 21:08

Joomla 3 loop through multidimensional array for db insert

1 Answers1