1 
public function add_employee($input)
{
    $key_array = null;
    $value_array = null;
    $bind_array = null;
    foreach ($input as $column => $value) {
        if ($value) {
            #$bind_array => ?, ?, ?;
            #$value_array => [$value1, $value2, $value3];
            #$key_array => column1, column2, column3;
        }
    }
    $sql = "INSERT INTO ol_employee ($key_array) VALUES ($bind_array)";
    $this->db->query($sql, $value_array);
}

Refer to comment in the function, how to achieve that output? the idea is, from the input POST i get which over 27 fields, i just want to fill in into the $sql query i prepared as you can see. I don't think writing each table column manually is a good way.

im using Codeigniter 4 php framework + postgresql.

Improve this question

3 Answers 3

Reset to default
1

According to CodeIgniter 4 documentation, you can do this inside your loop for each employee:

$data = [
    'title' => $title,
    'name'  => $name,
    'date'  => $date
];

$db->table('mytable')->insert($data);
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

it's not the question. i'm not into writing those column name 1 by 1, its over 27 fields. Looping the is the only way. but i dont hv idea how to append those things. thats all. please see the desire outcome im looking for in the function comment.
If you want an array with all the keys and another array with all the values you can take a look to array_keys and array_values PHP functions.
array_combine is the function
0

You can use array and implode function first make array of key_array, value_array and bind_array then use implode() and use in sql like following 

public function add_employee($input)
{
    $key_array = array();
    $value_array = array();
    $bind_array = array();
    foreach ($input as $column => $value) {
        if ($value) {
            $bind_array[] = '?';
            $value_array[] = $value;
            $key_array[] = $column;
        }
    }

    $binds = implode(",",$bind_array);
    $keys = implode(",",$key_array);    
    $values = implode(",",$value_array);
    $sql = "INSERT INTO ol_employee ($keys) VALUES ($binds)";
    $this->db->query($sql,$values);
}
Improve this answer

3 Comments

looks like what im looking for.. will try first
Query failed: ERROR: syntax error at or near "," LINE 1: ...title, password, privilege, mr_id, salt) VALUES (?, ?, ?, ?,... ^ im also already fix your code on $value_array[] = $value; also $this->db->query($sql,[$values]);.. still having issue
can you send me sql query ? so i can check that what is issue
0

by the insight of Alex Granados, this is the query i use:

public function add_employee($input)
{
    foreach ($input as $column => $value) {
        if ($value) {
            $data[$column] = $value;
        }
    }

    $this->db->table('ol_employee')->insert($data);
}

this will eliminate null field and regardless how many field, still working good. as long the input name field from form is same as db column. Else, need to do some changes on that.

Thanks guys.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.