Change an array into an sql query PHP

Question

I have an array $contact that looks like this :

Array
(
    [0] => Array
        (
            [type] => Textbox
            [label] => Company
            [name] => company
            [properties] => a:2:{s:11:"class_label";s:8:"col-md-4";s:5:"class";s:8:"col-md-8";}
            [language] => EN
            [weight] => 1
            [nid] => 5
        )

    [1] => Array
        (
            [type] => Textbox
            [label] => First name
            [name] => first_name
            [properties] => a:3:{s:8:"required";b:1;s:11:"class_label";s:8:"col-md-4";s:5:"class";s:8:"col-md-8";}
            [language] => EN
            [weight] => 2
            [nid] => 5
        )

    [2] => Array
        (
            [type] => Textbox
            [label] => Last Name
            [name] => last_name
            [properties] => a:3:{s:8:"required";b:1;s:11:"class_label";s:8:"col-md-4";s:5:"class";s:8:"col-md-8";}
            [language] => EN
            [weight] => 3
            [nid] => 5
        )

And I'd like to change it to :

INSERT INTO `form` (`nid`, `language`, `label`, `name`, `type`, `properties`, `options`, `weight`) VALUES
( 5, 'EN', 'Société', 'societe', 'Textbox', 'a:2:{s:11:"class_label";s:8:"col-md-4";s:5:"class";s:8:"col-md-8";}', NULL, 1),
( 5, 'EN', 'First name', 'first_name', 'Textbox', 'a:3:{s:8:"required";b:1;s:11:"class_label";s:8:"col-md-4";s:5:"class";s:8:"col-md-8";}', NULL, 2),
( 5, 'EN', 'Last Name', 'last_name', 'Textbox', 'a:3:{s:8:"required";b:1;s:11:"class_label";s:8:"col-md-4";s:5:"class";s:8:"col-md-8";}', NULL, 3);

So I tried this :

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

}

And it worked for the only the first element of the array,but when I do :

    foreach ($var as $key => $value) {
        var_dump(insert_contact( $var[$key]));
     }

It displays me this error : Notice: Undefined variable: columns
Is there a way to avoid this error and generate a clean sql query? Thanks

What is the array you pass into insert_contact(). Ensure it contains the key fields. — Jason McCreary
– Jason McCreary, Commented Apr 24, 2014 at 15:52
Thanks, I forgot about it since I used the function for other purposes ! — user3350731
– user3350731, Commented Apr 24, 2014 at 15:57

Jim · Accepted Answer · 2014-04-24 15:52:28Z

2

You don't have a fields in your array. The reason the code is working for the first element is because:

if ($key == 'fields'){

will be true if $key is 0 (due to type coercion).

Try:

function insert_contact($array){
    $values = array();
    foreach($array as $key => $value){       
        $columns = array_keys($value);
        $values[] = "('" . implode("', '", array_values($value))."')";
    }
    $sql = "INSERT INTO `".'form' . "` (`".implode("`, `", $columns)."`)";
    $sql .= " VALUES ".implode(',', $values) .";";
    return $sql;

}

Please note if it's at all possible that the $array or it's contents can come from user input you don't want to use the values in a query without first escaping them.

answered Apr 24, 2014 at 15:52

Jim

22.7k6 gold badges55 silver badges81 bronze badges

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

1 Comment

Jim Over a year ago

@JasonMcCreary Although I should add that if $key was a string this wont be the case, numeric keys are always stored as an integer however.

Szandor · Accepted Answer · 2014-04-24 16:09:14Z

0

Jim wrote a good answer, but he never explained the actual error:

On line 8 of your function you call the variable $columns. However, since $key == 'fields' evaluates to false it never gets set. That's why the error says there's an undefined variabe named 'columns' in the script.

You can only call variables that have been previously defined.

answered Apr 24, 2014 at 16:09

Szandor

4032 silver badges12 bronze badges

Collectives™ on Stack Overflow

Change an array into an sql query PHP

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