Building MySQL insert statement from multidimensional array

Question

I need to build INSERT statements from an array but I am stuck this is what I have so far but it obviously isn't right:

INSERT INTO company (, Name, Address, City, State, Zip, Country, Website, NOL
INSERT INTO additionallocations (, CompanyID, Address, City, State, Zip, Country

This is my array and how I am building the statement:

$fieldsArray = array(
                    'company' => array(
                            'Name' => $Name,
                            'Address' => $Address,
                            'City' => $City,
                            'State' => $State,
                            'Zip' => $Zip,
                            'Country' => $Country,
                            'Website' => $Website,
                            'NOL' => $NOL,
                        ),
                    'additionallocations' => array(
                            'CompanyID' => $CompanyID,
                            'Address' => $Address,
                            'City' => $City,
                            'State' => $State,
                            'Zip' => $Zip,
                            'Country' => $Country,
                        )
                    );




        foreach($fieldsArray as $table => $rows) {
            $sql = "INSERT INTO " . $table . " (";
            foreach($rows as $column => $value){
                $sql .= ", " .$column;
            }
        }

        echo $sql;

Here is what I would like to end up with:

INSERT INTO company (Name, Address, City, State, Zip, Country, Website, NOL) VALUES
                             ('$Name', '$Address', '$City', '$State', '$Zip', '$Country', '$Website', '$NOL');

I need to find a way to keep building this out with the VALUES and get rid of the first comma in after the opening parentheses. Anyone have any suggestions?

You said you needed this with the values, how will your values be passed? — AyB
– AyB, Commented Feb 9, 2014 at 4:35
What do you mean? I would like to use the variable names in the VALUES section like this INSERT INTO company (Name, Address, City, State, Zip, Country, Website, NOL) VALUES ('$Name', '$Address', '$City', '$State', '$Zip', '$Country', '$Website', '$NOL'); — Yamaha32088
– Yamaha32088, Commented Feb 9, 2014 at 4:37

Jain · Accepted Answer · 2014-02-09 05:17:08Z

2

foreach($fieldsArray as $table => $rows) 
{
    $sql = "INSERT INTO " . $table . "(";
    foreach($rows as $column => $value)
    {    
         $sql .= $column.", ";
    }
    $sql = rtrim($sql, ", ");
    $sql .= ") VALUES (";

    foreach($rows as $column => $value)
    {    
         $sql .= "'".$value."', ";
    }
    $sql = rtrim($sql, ", ");
    $sql .= ")";
    echo $sql; 
}

edited Feb 9, 2014 at 5:17

answered Feb 9, 2014 at 4:42

Jain

1,24910 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AyB Over a year ago

rtrim($sql, ", "); should be $sql = rtrim($sql, ", ");

Yamaha32088 · Accepted Answer · 2014-02-09 04:52:12Z

0

I got it working with this:

foreach($fieldsArray as $key => $table){            
    $keys = array_keys($table);
        $values = null;
        $x = 1;

        foreach($keys as $value) {
            $values .= "?";
            if($x < count($keys)) {
                $values .= ', ';
            }
            $x++;
        }

    echo $sql = "INSERT INTO {$key} (`" . implode('`, `', $keys) . "`) VALUES ({$values})" . "<br/>";

}

answered Feb 9, 2014 at 4:52

Yamaha32088

4,24310 gold badges54 silver badges105 bronze badges

Collectives™ on Stack Overflow

Building MySQL insert statement from multidimensional array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related