Change an array into a query PHP

Question

Basically I have this array that I want to change into a database query

Array
(
    [table] => menu
    [fields] => Array
        (
            [mid] => menu_admin
            [language] => EN
            [name] => Admin
        )

)

Array
(
    [table] => user
    [fields] => Array
        (
            [uid] => 1
            [rid] => admin
            [first_name] => Admin
            [last_name] => Admin
            [email] => [email protected]
            [password] => xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            [date_register] => Zend_Db_Expr Object
                (
                    [_expression:protected] => NOW()
                )

            [last_connexion] => NULL
            [enable] => 1
        )

)

and so on...And I'd like to have it like this:

INSERT INTO `menu` (`mid`, `language`, `name`) VALUES
('menu_admin', 'EN', 'Admin');
INSERT INTO `user` (`uid`, `rid`, `first_name`, `last_name`, `email`, `password`, `date_register`, `last_connexion`, `enable`) VALUES
(1, 'admin', 'Admin', 'Admin', '[email protected]', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '2014-02-27 15:33:45', '0000-00-00 00:00:00', 1);

Here's what I tried :

foreach ($query as $sql) { 
$keys = rtrim(explode(", ", array_keys($sql['fields']), ",")); //<--Here
$fields = rtrim(explode(", ", $sql['fields']), ",");  //<--And here
$sql = sprintf("INSERT INTO %s (%s) VALUES (%s)", $sql['table'], $fields, $keys); 
}
var_dump($sql);

Here's the Error : explode() expects parameter 2 to be string, array given But I just can't quite work out how I achieve it. Any help with this? Thanks.

Just use a foreach loop on your [fields] array and build 2 strings : one string for fields names and another for fields values. Then just concat the two strings* — YannPl
– YannPl, Commented Apr 11, 2014 at 14:05

larsAnders · Accepted Answer · 2014-04-11 14:06:38Z

2

Here's a function that will build a SQL statement from your source array:

function buildInsertSQL($array){
    foreach($array as $key => $value){
        if ($key == 'table'){
            $table = $value;    
        }
        if ($key == 'fields'){
            $columns = array_keys($value);
            $values = array_values($value);
        }
    }
    $sql = "INSERT INTO `". $table . "` (".implode(", ", $columns).")";
    $sql .= " VALUES ('".implode("', '", $values)."') ";
    return $sql;

}

$test = array(
    'table' => 'menu',
    'fields' => array(
            'mid' => 'menu_admin',
            'language' => 'EN',
            'name' => 'Admin'
        )

);

$sql = buildInsertSQL($test);
echo $sql;

answered Apr 11, 2014 at 14:06

larsAnders

3,8031 gold badge17 silver badges19 bronze badges

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

Comments

dikesh · Accepted Answer · 2014-04-11 14:10:58Z

1

// example code

foreach ($query as $sql)
{
    $cols = array();
    $vals = array();

    foreach($sql['fields'] as $key => $val)
    {
        array_push($cols, "`". $key."`");
        array_push($vals, "'". $val."'");
    }

    echo "INSERT INTO `".$sql['table']."` (".implode(',', $cols).") VALUES (".implode(',', $vals).")";
}

answered Apr 11, 2014 at 14:10

dikesh

3,1252 gold badges20 silver badges29 bronze badges

Collectives™ on Stack Overflow

Change an array into a query PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related